Search multiple columns and multiple tables with different criteria

[anonymous]

I’ve used the sample given by GreenFlux (Search multiple columns with different criteria for the first search.)

Context:
I have Events, and for each Event I invite Persons.
3 tables are needed (Person, Event and Invitation),

Attributes:
a Person has a name and a country (USA,France, …)
an Event has a name and a class (Football, Handball, …)
an Invitation has a type of invitation (VIP, Normal, …)

Relations
One Person is invited to many Event
One Event has many Person invited
for each row Invit there is 1 person and 1 event

Sample of data

The user of the application need to search for persons:

A: Search only on the attributes of the person 1. Example: the person that are in USA.
the person that have Pierre in their name and are in USA, etc…
This search is called PRSONLY 2 (it is exactly the same has the sample of GreenFlux)

B : Search with the attributes of Event and Invitation: Example : Person in USA that have been invited to ‘Tournois of Paris 2020’, or Person that have been invited at least one time has a VIP
This search is called WITHEVT B

C: Search on non-existing invitations. Find the person that have never been invited to Football, that have never been invited has VIP for Handball event, etc…
This search is called WITHOUT C

Sample for other App
CRM App: Search for all the person that have never been contacted by phone
Shop App : Search for all the person that have never ordered this type of product (the 3 tables are Person, Item, Order)

The search on person table is:

Prs_List :
SELECT(T_Person[ID],
AND(
OR( ISBLANK([_THISROW].[Src_Prs_Nom]) , CONTAINS([Prs_Nom] ,[_THISROW].[Src_Prs_Nom]) ),
OR( ISBLANK([_THISROW].[Src_Prs_Pays]) , [Prs_Pays] = [_THISROW].[Src_Prs_Pays] )
)
,true)

(this is the same as the GreenFlux sample)

Search for Event and Invit. I search for all invitation and Event that match the criteria.

Evt_List:
SELECT(T_Invit[Inv_Prs_ID],AND(
OR( ISBLANK([_THISROW].[Src_Inv_TYPE]) , [Inv_Type] = [_THISROW].[Src_Inv_TYPE] ),
IN([INV_Evt_ID],
SELECT(T_Event[ID],AND(
OR( ISBLANK([_THISROW].[Src_Evt_Nom]) , CONTAINS([Evt_Nom] ,[_THISROW].[Src_Evt_Nom]) ),
OR( ISBLANK([_THISROW].[Src_Evt_class]) , [Evt_class] = [_THISROW].[Src_Evt_class] )
),true)
)),true)

PRSONLY A : It is the same as Prs_List

WITHEVT B : The final list is the person that are in the both list ( Prs_List and Evt_List ), it is an INTERSECT(Prs_List, Evt_List).

WITHOUT C: The final list is the person that are in Prs_List and not in Evt_List. so it is (Prs_List - Evt_List)

In my Search table I’ve got a virtual field Results for displaying the results

in the App formula of this, I’ve tried to do something like this

SWITCH([_THISROW].[Src_Type],
‘PRSONLY’, Prs_List,
‘WITHEVT’, INTERSECT(Prs_List, Evt_List),
‘WITHOUT’, Prs_List - Evt_List)

and also to use IFS() but I’m not able to do it. I’m a beginner with AppSheet.

so I wrote this type of formula.

(if ‘PRSONLY’ then Prs_List) + (if ‘WITHEVT’ then INTERSECT(Prs_List, Evt_List),) + (if ‘WITHOUT’ then Prs_list – Evt_List)

and it works .

you can see the App in my portfolio

Next Step :

Her are the improvements I would like to do. But for the present time my goal is to be able to do with AppSheet all the touchy thing I’ve developed on AppMaker in JavaScript Server.
if you have ideas to resolve these Quest, welcome.

Quest1: The reading of this formula
((if ‘PRSONLY’ then Prs_List) + (if ‘WITHEVT’ then INTERSECT(Prs_List, Evt_List),) + (if ‘WITHOUT’ then Prs_list – Evt_List))
is complicated to understand, I prefer to have something like this:

SWITCH([_THISROW].[Src_Type],
‘PRSONLY’, xxx,
‘WITHEVT’, xxx
‘WITHOUT’, xxx)
is it possible to do that in the formula?

Quest2: Duplicated lines is bad, in reality I have much more than 2 attributes on the 3 tables, so the formula will be very long , in another language I would have created a function or a procedure or an include.

I’ve added 2 virtual columns and wanted to use [_THISROW].[Prs_List] and [_THISROW].[Evt_List]

like that there is no duplication of this part of code.

but it didn’t worked, I don’t know how to do that

Prs_List
SELECT(T_Person[ID],
AND(
OR( ISBLANK([_THISROW].[Src_Prs_Nom]) , CONTAINS([Prs_Nom] ,[_THISROW].[Src_Prs_Nom]) ),
OR( ISBLANK([_THISROW].[Src_Prs_Pays]) , [Prs_Pays] = [_THISROW].[Src_Prs_Pays] )
)
,true)

Evt_List
SELECT(T_Invit[Inv_Prs_ID],AND(
OR( ISBLANK([_THISROW].[Src_Inv_TYPE]) , [Inv_Type] = [_THISROW].[Src_Inv_TYPE] ),
IN([INV_Evt_ID],
SELECT(T_Event[ID],AND(
OR( ISBLANK([_THISROW].[Src_Evt_Nom]) , CONTAINS([Evt_Nom] ,[_THISROW].[Src_Evt_Nom]) ),
OR( ISBLANK([_THISROW].[Src_Evt_class]) , [Evt_class] = [_THISROW].[Src_Evt_class] )
),true)
)),true)

Quest3 : for my knowledge of AppSheet: if I’ve got a virtual column using the result of an other virtual column, I need to understand in which order they are evaluated. from top to down or down to top ?

in the formula of A can I use [_THISROW].[B] and [_THISROW].[C]

or

in the formula of C can I use [_THISROW].[A] and [_THISROW].[B]

Quest4 : optimization evaluate in a OR() or AND(). left to right or right to left

in my sample I’ve written AND (evaluationA, evaluationB)

I suppose AppSheet evaluate first A then B, as A is very simple to evaluate I put it first

is it the good way?

Quest5 : is it possible to add comments in the formula?

Quest6 : if you have a better idea to resolve all this, welcome.

I enjoy very much this tool, and it is really easy to use, and very fast to have applications developed.

Congratulations

Stéphane

[GreenFlux]

Welcome to the community, @Stephane_Liema! I’m glad you found the sample helpful.

Stephane_Liema:

Quest5 : is it possible to add comments in the formula?

No, unfortunately not. But you can vote for it here:
https://community.appsheet.com/t/feature-request-comments-inside-application-formula-expression/20519

Stephane_Liema:

Quest4 : optimization evaluate in a OR() or AND(). left to right or right to left

I’m not sure, on this, but there is some odd behavior with LEFT vs RIGHT side of = when a value is blank. I think @steve can explain better. (I think I recall learning it from one of his posts)

I’m on a few quests of my own right now but I’ll be glad to help out on the others later.

[Steve]

GreenFlux:

I’m not sure, on this, but there is some odd behavior with LEFT vs RIGHT side of = when a value is blank. I think @steve can explain better. (I think I recall learning it from one of his posts)

Testing right now appears to show this behavior has been corrected! Hooray!

[Steve]

Stephane_Liema:

SELECT(T_Person[ID],> AND(> OR( ISBLANK([_THISROW].[Src_Prs_Nom]) , CONTAINS([Prs_Nom] ,[_THISROW].[Src_Prs_Nom]) ),> OR( ISBLANK([_THISROW].[Src_Prs_Pays]) , [Prs_Pays] = [_THISROW].[Src_Prs_Pays] )> )> ,true)

If ID is the key column is the key column of the T_Person table, the third argument to SELECT() of true is unnecessary, as keys are by definition unique. It doesn’t hurt, but it also has no practical effect except to (arguably) clutter the expression.

Stephane_Liema:

Quest1

SWITCH() requires a default, which is missing from your expression.

I’d like to spend more time looking at your expression but can’t right now.

Stephane_Liema:

Quest2 : Duplicated lines is bad

I agree. Again, I’d like to come back to this when I have time.

Stephane_Liema:

Quest3 : for my knowledge of AppSheet: if I’ve got a virtual column using the result of an other virtual column, I need to understand in which order they are evaluated. from top to down or down to top ?

AppSheet constructs a dependency graph and evaluates them in the order needed.

Stephane_Liema:

Quest4 : optimization evaluate in a OR() or AND(). left to right or right to left

Unfortunately, AppSheet evaluates all of them, regardless of order. Logical operations do not short-circuit except in trivial cases.

Stephane_Liema:

Quest5 : is it possible to add comments in the formula?

Nope.

Stephane_Liema:

I enjoy very much this tool, and it is really easy to use, and very fast to have applications developed.

I agree!

[Steve]

Quest2 is the approach I would pursue myself. I notice the column types are Text but they should be List or EnumList. Did you set the column types yourself, or did the app editor choose them?

[anonymous]

Done
i’ve voted

[anonymous]

Steve:

If ID is the key column is the key column of the T_Person table, the third argument to SELECT() of true is unnecessary,

Yes you are right

as the data i fetch is a key, no need to add True for having unique value.

and this is much quicker to execute, because I suppose that when I put true for having unique value, the AppSheetEngine has to do:

for each row , take the value, check if this value has already been selected , if yes go on with the next row, if no add this value to the unique value, and at the end of the read send all the good value

and without true

for each value read and send, no need to test, and to store in memory all the value

very good idea, thanks

[anonymous]

yes i put text to this column, just for debbuging and check if the request is working well

[Steve]

You said Quest2 didn’t work. What did it do?

[anonymous]

i tried to use the result of the 2 other virtual columns that added
instead of writing al l the select,
my goal is to write something like this but i can’t but i’m not yet strong enough
SWITCH([_THISROW].[Src_Type],
‘PRSONLY’, the result is in : [_THISROW].[Prs_List],
‘WITHEVT’, the result is : INTERSECT( [_THISROW].[Prs_List],[_THISROW].[Evt_List])
‘WITHOUT’, ( [_THISROW].[Prs_List] - [_THISROW].[Evt_List]))

Do you know how to do that?

[GreenFlux]

SWITCH() requires a default result as the last argument.

[anonymous]

im getting better with AppSheet, and I’ve founded the answer of my question 2

I’ve got 2 virtuals column Prs_List and Evt_List , that search for the data
and the Result to display according the user Choice

Prs_List:
SELECT(T_Person[ID],
AND(
OR( ISBLANK([_THISROW].[Src_Prs_Nom]) , CONTAINS([Prs_Nom] ,[_THISROW].[Src_Prs_Nom]) ),
OR( ISBLANK([_THISROW].[Src_Prs_Pays]) , [Prs_Pays] = [_THISROW].[Src_Prs_Pays] )
)
,true)

Evt_List:
SELECT(T_Invit[Inv_Prs_ID],AND(
OR( ISBLANK([_THISROW].[Src_Inv_TYPE]) , [Inv_Type] = [_THISROW].[Src_Inv_TYPE] ),
IN([INV_Evt_ID],
SELECT(T_Event[ID],AND(
OR( ISBLANK([_THISROW].[Src_Evt_Nom]) , CONTAINS([Evt_Nom] ,[_THISROW].[Src_Evt_Nom]) ),
OR( ISBLANK([_THISROW].[Src_Evt_class]) , [Evt_class] = [_THISROW].[Src_Evt_class] )
),true)
)),true)

Result:
IFS(
[Src_Type]=‘PRSONLY’, [Prs_List],
[Src_Type]=‘WITHEVT’, INTERSECT(SPLIT([Prs_List], “,”),SPLIT([Evt_List], “,”)),
[Src_Type]=‘WITHOUT’, SPLIT([Prs_List], “,”)-SPLIT([Evt_List], “,”)
)

I’ve published the application
https://www.appsheet.com/portfolio/1252781

Don’t look at the UX, for the present time I’m learning the Expressions, Actions, Workflow