Drop-down menu

ux
(francesco cannone) #1

HELLO
in a table called service I have a drop-down menu that takes data from another table ref. (TEST)
how can I not see the services I have already added?
THANK YOU

1 Like
(Aleksi Alkio) #2

Do you have a Valid_If formula that filter the list… or do you have slice or security filter?

(francesco cannone) #3

Sorry, but I did not understand
The drop-down menu takes the values from another table with the REF function

(Levent Kulacoglu) #4

@francesco_cannone
What @Aleksi wants to say is, there are a couple of ways to filter a REF dropdown content.

1.) Using a Slice: You can create a slice and slightly change your REF_ROWS expression in the [Related …] column to read the values from this slice,

2.) Using a Security Filter: You can set a Security Filter for you ref table which will filter away the rows that you don’t want to display

3.) Using a Valid_if Expression: You can use a Valid_if expression in your REF column, to filter away ref rows i.e. SELECT(ParentTableName[Key],{Y/N condition})

Hope it’s clear now.

(francesco cannone) #5

I think the number 3 option is the best
I would like to do something like this in the picture

(Levent Kulacoglu) #6

@francesco_cannone
Is your ref table a READ_ONLY table? Because there is no +New link on top of your dropdown to add a new ref record. Not a bid deal but just asking.

(francesco cannone) #7

yes exactly is read only!

(Steve Coile) #8

I’m going to guess what you mean is that you want the drop-down menu to only display entries from the referenced table that haven’t already been referenced in the same column of other rows of this table.

In the Valid_If for the column:

FILTER("ThatTable", TRUE)
- SELECT(
 ThisTable[ThisColumn],
 ([KeyColumn] <> [_THISROW].[KeyColumn])
)

where ThatTable is the name of the referenced table, ThisTable and ThisColumn are the names of the table and column you want the drop-down menu in, and KeyColumn is the name of the key column for ThisTable

  1. FILTER(...) gets a list of all key column values from ThatTable.

  2. SELECT(...) gets a list of existing values in the ThisColumn column of ThisTable that…

  3. ([KeyColumn] <> [_THISROW].[KeyColumn]) excludes the current row’s reference to ThatTable. If we don’t do this, the column value will be considered invalid if you edit the row again in the future.

  4. FILTER(...) - SELECT(...) removes all of the references that occur in ThisTable (the “used” references) from the list of all possible references, leaving only those that are unused.

See also: FILTER(), SELECT()

(francesco cannone) #9

Thank you
It works perfectly !!!:confetti_ball::tada:

1 Like
(Levent Kulacoglu) #10

@francesco_cannone
SELECT(ParentTable[Key],NOT(IN([ThisTableRefColumn],ParentTable[Key])))

1 Like