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
Solved! Go to Solution.
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
FILTER(...)
gets a list of all key column values from ThatTable.
SELECT(...)
gets a list of existing values in the ThisColumn column of ThisTable that…
([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.
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.
Do you have a Valid_If formula that filter the list… or do you have slice or security filter?
Sorry, but I did not understand
The drop-down menu takes the values from another table with the REF function
@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.
I think the number 3 option is the best
I would like to do something like this in the picture
@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.
yes exactly is read only!
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
FILTER(...)
gets a list of all key column values from ThatTable.
SELECT(...)
gets a list of existing values in the ThisColumn column of ThisTable that…
([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.
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.
Thank you
It works perfectly !!!
@francesco_cannone
SELECT(ParentTable[Key],NOT(IN([ThisTableRefColumn],ParentTable[Key])))
I am a newbie with no experience and I was wondering what the easiest way to create a drop down within a selection in the app would?
I have done what is is said in the tutorial but I must have missed something.
User | Count |
---|---|
38 | |
35 | |
27 | |
23 | |
18 |