IFS using Reverse Ref

[Ben_Rix]

Hey Everyone,

Im stuck with this.

Im trying to use this function.

IFS(
AND([Related Case Sessions][Attendance]=“Scheduled”),[Related Case Sessions][Date]<TODAY()),
“SCHEDULED”, TRUE,

I am getting the cannot compare list with text. Is this because I Should be using the IN Function? If so, how do I go about it because I’m struggling to get my head around it.

Regards
Ben

[Suvrutt_Gurjar]

The expression is giving error because it is comparing list with a single value.

[Related Case Sessions][Attendance] and [Related Case Sessions][Date] will both give a list of “Attendance” columns and a list of “Date” columns from the related child table records.

So expression wants to know, out of these list values which specific value(s) you wish to compare with “Scheduled” for “Attendance” and <TODAY() for “Date” list. Depending on whether you wish any value or all values or specific values in those lists to compare with “Scheduled” and < TODAY(), the expression needs modification.

IN() will work if any value can be “Scheduled” in “Attendance” list for example.

[Ben_Rix]

So the expression criteria would both need to both contain attendance column being Scheduled and date column being before today.

The main reason for this is a clinician may forget to change the clients attendance and I want this rule to be added to our client status rule which highlights certain outstanding issues.

If the expression needs modification, do you know how i could modify it to make it work?

Regards
Ben

[Suvrutt_Gurjar]

Ben_Rix:

If the expression needs modification, do you know how i could modify it to make it work?

You can make it work… However , as mentioned modifications will depend on how the data looks/ is strucured,

Ben_Rix:

So the expression criteria would both need to both contain attendance column being Scheduled and date column being before today.

Will these related records have multiple records, then these columns will produce lists.
For example [Related Case Sessions][Attendance] will typically produce a list like {“Scheduled”, “Planned”, “Canceled”, “Scheduled”}

[Related Case Sessions][Date] will typically produce a list such as {“04/01/2021”, “05/01/2021”, “04/25/2021” }

How would you like the expression to behave for such a list? Some values in that list may match criteria and some will not, Or you may share more details on how your table structure looks for those columns. Will there be multiple child records for the parent table or only one?

[Ben_Rix]

It doesnt matter if more than one session matches the criteria.

Parent Table is the Clients Case and the Child table are the sessions. Parent table is where the case status is set upon and looks into the sessions to see if a case has any outstanding issues such as the attendance of a session marked as scheduled and the date being before today.

Maybe the list isn’t what I should be doing maybe I should be using a Select function.

[Suvrutt_Gurjar]

Hi @Ben_Rix ,

I am certainly willing to help to the best of my ability. However I am afraid, I am not yet clear on the exact scenario that the app has.

Your entire expression that you have shared is not clear and probably has syntactical errors.

In general, based on your description so far and my understanding, your expression could be something like

IFS( AND(IN(“Scheduled”, [Related Case Sessions][Attendance] ), COUNT(SELECT([Related Case Sessions][Date], [Date]<TODAY()))>0, “Scheduled”…

This expression will select “Scheduled” if any of the child records’ [Attendance] column has “Scheduled” status and any of the child records’ [Date] column has a date lesser than today.

It is not clear how you have constructed the IFS() expression, so my shared expression mainly takes care of IFS() condition.

[Ben_Rix]

Hey Suvrutt,

Im really struggling to gunderstand this. Its because of the reverse reference which i cant get my head around. If I am to use the in() function, I need to add a FILTER, which filters out the scheduled and before today criteria. If that will work, Do I then combine this then with a COUNT so that, if the list has more than 0 matching criteria it will trigger.

Just an addition, i have now made a slice that displays a list of all sessions that have an “scheduled” in the attendance column and date<today()