Hi Appsheet community! Got a quick question...

AppSheet AppSheet Q&A
1

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…

2

@Jon_Simmons

SELECT(Books[BOOK_UID],NOT([BNP_UID]=[_THISROW].[BNP_UID]))

3

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.

4

@Levent_KULACOGLU here’s the video https://drive.google.com/file/d/19muheUK2OkM5HSRFy2tSXBhPKrhx5MOZ/view

5

@Jon_Simmons

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?

6

@Levent_KULACOGLU Done

7

@Jon_Simmons Done

8

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!

9

@Jon_Simmons you’re welcome. My pleasure to help.

10

@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?

11

@Levent_KULACOGLU Logically I would use it within the Books column

12

@Jon_Simmons

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?

13

@Levent_KULACOGLU yes - 100% correct

14

@Jon_Simmons

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.

15

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.

16

@Jon_Simmons

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?

17

@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