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