How to use a column as a dynamic dataset name for MINROW() or SELECT() functions?

[anderoo]

Hey there,

I am trying to use values in the "Part Number" column (in a dataset called "CAM") to find the minimum value listed in the "Build Number" column across a number of datasets with names that correspond to values in the "Part Number" column.

The formula I am currently using:

Part Number << TEXT([_THIS].[Part Number])

MINROW(Part Number,"Build Number")

However it returns the following error:

"Column _THIS in expression '[_THIS].[Part Number]' does not contain a reference"

What is the correct way to use a column as a dynamic input for the MINROW() or SELECT() functions?

Any help would be much appreciated.

[Aurelien]

Hi @anderoo 

You may want to try with [_THISROW] instead of [_THIS] 🙂

[_THIS] applies to the value of a specific colum and specific row, for example the one you are currently editing.

[_THISROW].[yourColumn] is of better use with some cases.

Hence:

TEXT([_THIS].[Part Number])

 should be instead: 

TEXT([_THIS])

or

TEXT([_THISROW].[Part Number])

EDIT:

Moreover:

---

@anderoo wrote:

to find the minimum value listed in the "Build Number" column across a number of datasets

---

I would suggest using the MIN() expression instead.

Something like this for example:

MIN(CAM[Build Number])

 For reference:

MIN() - AppSheet Help

You could benefit from a combination of MIN and SELECT.

For reference:

SELECT() - AppSheet Help

EDIT2

---

@anderoo wrote:

across a number of datasets with names that correspond to values in the "Part Number" column.

---

You can also combiene various lists using a list addition.

Hence, your final expression could be:

MIN(
  SELECT(dataset1[Build Number], condition1)
  +
  SELECT(dataset2[Build Number], condition2)
)

For reference:

Combine lists - AppSheet Help

[anderoo]

Hey Aurelien,

thanks for the reply, however none of these approaches quite work for what I am after. I will try explaining my use case better with a simplified example.

I have one dataset called 'CAM' with the following information:

Part Number	Name
PRT-1002443	Mantis
PRT-1002734	Pistol

and other datasets called  "PRT-1002443" and "PRT-1002734" with the following information:

Part Number	Name	Build Number
PRT-1001409	XXXXXXX	1
PRT-1002781	YYYYYYY	4
PRT-1002780	ZZZZZZZZZ	1

I would like to be able to use a variable dataset name to find the minimum value in each corresponding datasets "Build Number" column to produce a new virtual column in "CAM" dataset called "QTY", as shown below:

Part Number	Name	QTY
PRT-1002443	Mantis	1
PRT-1002734	Pistol	2

The code that I thought should work:

MINROW([Part Number][Build Number])

produces the following error:

Column 'Part Number' is used in a SELECT or list dereference expression and should be a List/EnumList of Refs


I would like to scale this solution so that 30x datasets can be used.

Any further help would be greatly appreciated.

[anderoo]

Apologies, the code I was actually using is:

MIN([Part Number][Build Number])

[Aurelien]

Hi @anderoo 

If my understanding is correct, you have the dataset CAM that lists the other dataset names.

You can't use an expression that will calculate over calculated dataset names.

Basically, SELECT() and FILTER() expressions are underlying MINROW() and associated expressions.

When looking at the documentation, we have:

SELECT() - AppSheet Help

REF_ROWS() - AppSheet Help

MINROW() - AppSheet Help

So, if you know how many datasets you may have, I suggest you build a expression that will sum all of these.

Something like:

MIN(
  [Related Dataset1][Build Number]
  +[Related Dataset2][Build Number]
  +[Related ....][Build Number]
)

[anderoo]

Hey Aurelien,

Unfortunately I was unable to implement these approaches to my desired effect. I have instead gone with rearranging my datasets to sidestep this issue. 

Thanks for your support.

Andrew