Possible to use Select from multiple pages?

[Mathericnl]

Hi, 

Currently I'm using the formula below to get an initial value filled in a form. 
It only extracts from sheet (Storage). I would like to extract from 3 pages: (Storage, Reserved, Sold).

SELECT(Storage[Status], ([ProductNo] = [_THISROW].[ProductNo])),

[WillowMobileSys]

If you mean can you extract and combine from the 3 sources, the answer is YES.   You could do something like the below.

NOTE:  Any time you use SELECT() it returns a LIST of values even a single value is a list of 1.  Lists are treated/represented  differently than single values.  Wrap a SELECT() with ANY() to get just the value.

Example to combine status' from 3 sources into a single LIST:

LIST(
     ANY(SELECT(Storage[Status], [ProductNo] = [_THISROW].[ProductNo])),
     ANY(SELECT(Reserved[Status], [ProductNo] = [_THISROW].[ProductNo])),
     ANY(SELECT(Sold[Status], [ProductNo] = [_THISROW].[ProductNo]))
)

 The column will need to be defined as some list type - EnumList if a datasource column,  List if a Virtual Column

[Mathericnl]

Hi,

thank you for the response.
I tried you're code and it gives me the right values. 

My only problem is that it shows "  ,,  " as initial value. When I receive a value from storage it looks like this:

Receiving value: Banana
Empty initial value: ,,
Initial value after receiving: Banana,,

When it receives from reserved it looks like:

Receiving value: Banana
Empty initial value: ,,
Initial value after receiving: ,Banana,

[WillowMobileSys · Accepted solution]

There is another way to combine these that might be better: using List addition:

SELECT(Storage[Status], [ProductNo] = [_THISROW].[ProductNo]) +
SELECT(Reserved[Status], [ProductNo] = [_THISROW].[ProductNo]) +
SELECT(Sold[Status], [ProductNo] = [_THISROW].[ProductNo]

However, you might want to think this through.  If all 3 sources have the same Status' or even some that are the same you could run into confusion.

For example,  Let's say all 3 sources have the status of "Complete".  If you were to be returned a status from all 3 it would look like this "Complete, Complete, Complete" - and all is good.

BUT if you get a status from ONLY 1 and eliminate the commas, that list will look like this "Complete". 

The problem?  Which source returned the status of Complete?  There is no way to know.

For this reason, if you wish to still use this method of combining Status', I recommend ALWAYS assigning a status value.  Maybe it is "None" or "Unassigned" as the Initial Value.  Doing this will eliminate the comma problem altogether.

[Mathericnl]

Perfect. this works for me, because all the ProductNo are unique and only in 1 sheet to find. 

Thank you very much.

[Mathericnl]

Good to know too:

The given value should be editable in the form.
The status is variable. I want to use it as well for measurements of products.

[WillowMobileSys]

---

@Mathericnl wrote:

Good to know too:

The given value should be editable in the form.
The status is variable. I want to use it as well for measurements of products.

---

The field where you are combining Status' probably should NOT be editable.   Since it represents status' from other sources, It wouldn't make sense for a user to "assign" a value in "this" field  (whichever table we are talking about) and then expect that a value to be propagated to one of those other 3 sources.

I strongly recommend to NOT use columns for multiple purposes such as Status and also Product Measurement.  I would say to keep them in separate data columns.  For display you can always combine them in some way.

Note that these are not hard-fast rules.  Simply suggestions from years of experience in software development.