Filtering ref column by unique label?

[Binkodyl]

I have a column called [DataID] that references a table called “Data”. I have the label of data set to a column called [String] which has duplicate values in multiple rows, but mean different things. How can I filter the key values showing labels to be unqiue? I only want to display unique values from that column, but other rows have the same [String] value. So instead of that ref column showing five “1” values, I’d like it to only show one. How can I do this?

[Steve]

Where would you like this list displayed? In a dropdown? In a table view?

[Binkodyl]

It displays in a dropdown. What I’m looking for is an expression.

DataID is my key, and String is the label. I have a ref to DataID in another table. Is there a valid if constraint that only displays values in the dropdown with the condition that the string column value is unique? So that I only get one 1, and two 2’s?

[Steve]

When the user makes a selection from the dropdown, what value will you want saved in the column? The Ref value, or the label value?

[Binkodyl]

I want to limit the key values represented by the label to be filtered by the label so that Ids will be shown in the dropdown only for unique label values. Will what you suggested accomplish this?

[Steve]

What value will you want stored?

[Binkodyl]

The Key is fine. I have it setup so that when a key is selected, the string field is populated. The only issue is how many key values are visible with duplicate string values.

[Steve]

Binkodyl:

The Key is fine.

When there are multiple keys with the same string value which key do you want saved?

[Binkodyl]

Preferably the First key with a given string value.

[Steve · Accepted solution]

Try:

FILTER(
  "Data",
  ISBLANK(
    FILTER(
      "Data",
      AND(
        ([_ROWNUMBER] < [_THISROW-1].[_ROWNUMBER]),
        ([String] = [_THISROW-1].[String])
      )
    )
  )
)

See also:

help.appsheet.com

AND()

Are all true?

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

ISBLANK()

Is value absent?

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

Back-references within nested queries ([_THISROW-n]) Consider a complex App formula with “nested” SELECT() expressions (SELECT() expressions within SELECT() expressions): SELECT( table[column1], ... SELECT( table[column2], ... SELECT( table[column3], ... ) ... ) ... ) Let’s call the row for which this entire expression is evaluated as its App formula the Origin Row. Within this entire expression, we can refer to column values of the Origin Row by dere…

[Willyan_Hendra]

Dear steve,
I have the same case and will save the Ref Value, is it the same expression?

[Steve]

UNIQUE(Data[String]) will give a list of distinct values in the String column of the Data table, as will SELECT(Data[String], TRUE, TRUE) (note two TRUE values there). The SELECT() approach could also be used to further filter the possible values.

See also:

help.appsheet.com

SELECT()

Gather column values from row in table or slice

help.appsheet.com

UNIQUE()

Unique items in list

[Binkodyl]

That worked perfectly! Thank you so much Steve!