Hi Appsheet community!
Got a quick question regarding Valid_If constraints on Ref fields.
In a nutshell i need to filter a Ref field based on the selection of another Ref Field.
What i’ve got:
Tables: 1) “Books” - key column = [BOOK_UID] 2) “Bookstores” - key column = [BNP_UID] 3) “Bookstore_Placement” - key column = [BKSTP_UID].
Bookstore_Placement obviously has [BOOK_UID] and [BNP_UID] as Refs.
The trick is when adding a new entry into “Bookstore_Placement”, and selecting a [BNP_UID] , to get the list of books that are NOT currently placed in that Bookstore ?
In plain English - i don’t want to place the same book twice in a bookstore. Is this possible?
It feels like i need to do something like a SELECT within SELECT … not sure…
Thanks for any suggestions or ideas…
SELECT(Books[BOOK_UID],NOT([BNP_UID]=[_THISROW].[BNP_UID]))
Hi Levent
Thanks for the reply - can you be a bit more clear as to where this select statement needs to be placed?
I’ve tried it in the obvious places ie. Valid_IF of [BOOK_UID] within “Bookstore_Placement” and in the App Formula. As a result the [BOOK_UID] field disappears when you create a new entry.
Is it possible that you can share the app with levent@able3ventures.com email with co-author status and also share the gSheet of the app with edit access?
@Levent_KULACOGLU Done
@Jon_Simmons Done
This great! Wow!
So essentially we’re comparing two lists in a somewhat “mathematical” way. We subtract one list of values from the other list of values and end up with the result.
The final Valid_IF formula that goes against the [BOOK_UID] in Bookstore Placement looks like this:
=SELECT(Books[BOOK_UID],TRUE) - SELECT(Bookstore Placement[BOOK_UID],[BNP_UID]=[_THISROW].[BNP_UID])
Many thanks Levant!
@Jon_Simmons you’re welcome. My pleasure to help.
@Jon_Simmons you have to use this in Valid_if property but I believe I have misunderstood your statement on where you would like to use this expression. You want to use this expression in Bookstore_Placement_Form UX, and in Bookstore or Books column?
@Levent_KULACOGLU Logically I would use it within the Books column
So in the [Books] ref column, you want to list only the Book IDs that are not belong/associated to the selected [Bookstores_UID] key value, am I correct?
@Levent_KULACOGLU yes - 100% correct
If that’s the case, so you can use below expression in Valid_if property of [Books] column:
SELECT(Books[BOOK_UID],NOT([BNP_UID]=[_THISROW].[BNP_UID]))
This expression will populate all the [BOOK_UID] column values from Books table, where [BNP_UID] column value in the same table is not equal to the [BNP_UID] value that you have selected in your Form UX.
Try it and please keep us updated provided you require further help/explanation.
Hey Levent,
But i’ve tried this before and it doesn’t work. Mainly this is because [BNP_UID] doesn’t exist in the Books Table.
Amongst the UID columns that you have mentioned in your post, which of them exist in your Books table so that we can point out as a reference?
@Levent_KULACOGLU when you link the [BOOK_UID] in the Book Placements table, you get the [Related Book Placements] virtual column in the Books table - naturally.
I’m just uploading a video explainer of how the app is set up so you get a better picture
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
16 |