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)
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.
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
Welcome to the community, @Stephane_Liema! I’m glad you found the sample helpful.
No, unfortunately not. But you can vote for it here:
https://community.appsheet.com/t/feature-request-comments-inside-application-formula-expression/2051...
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.
Testing right now appears to show this behavior has been corrected! Hooray!
Done
i’ve voted
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.
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.
I agree. Again, I’d like to come back to this when I have time.
AppSheet constructs a dependency graph and evaluates them in the order needed.
Unfortunately, AppSheet evaluates all of them, regardless of order. Logical operations do not short-circuit except in trivial cases.
Nope.
I agree!
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
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?
yes i put text to this column, just for debbuging and check if the request is working well
You said Quest2 didn’t work. What did it do?
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?
SWITCH() requires a default result as the last argument.
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