Why does this formula work when I test it, but not in the app?

AppSheet AppSheet Q&A
, ,
1

I have the following code in a virtual column:

intersect(select(Filtered Data[Course Name],and([Course Status]=“COMPLETED”,[_THISROW].[ORG ID]=[Org Id])),split([OE Online I-CAR Required],“,”))

It’s intended to compare a list of courses completed to those required. I have the “split” on the required column because it’s a list of lists so I’m flattening it.

The thing is, when I click the “test” button, the formula produces good results. Once I save the VC, it also shows correct results in the app preview pane. When I click “Save” for the entire app and it re-syncs, they all go blank and nothing shows up in the VC anymore for any rows. What’s going on? Thanks.

1 Like
2

image
image716×336 49.5 KB

Try instead:

intersect(select(Filtered Data[Course Name],and([Course Status]=“COMPLETED”,[_THISROW].[ORG ID]=[Org Id])),split(("" & [OE Online I-CAR Required]),“ , ”))
3

Thanks, Steve. The above suggestion produces some results, but not reliably. It’s worth noting that the lists being compared sometimes contain blank elements. For instance, the required field may contain several preceding blank records, or trailing records such as:

, , , , , , , Ford Aluminum Body Structural Repair, Ford Service Information Navigation for Collision Repair, Ford Battery Electric Vehicle (BEV) Components and Operation, Ford Mach-E and New Model Training Overview

OR:

Post Repair Advanced Driver Assistance Systems (ADAS) Testing, ADAS Diagnostics - When the Calibration Fails , Collision Repair of the 2021 General Motors (GM) Full-Frame Sport Utility Vehicles , General Motors Collision Repair Network (GM‑CRN) BEV Basic Training Package , Collision Repair of the 2020 General Motors (GM) Corvette C8 , , , , , , ,

I am not sure about the nuances of the amount of space around commas that the above documentation snippet seems to be pointing to. My lists have varying spacing around commas, with some elements having zero space, others having a space on one side of the comma or the other, and sometimes on both sides. I have also noticed that some elements in the list contain commas within single entries! Should I wrap entries in quotes to prevent those entries from being problematic?

4

Maybe I can help…

First, if the column [OE Online I-CAR Required] is defined as a List type column, you should not need the SPLIT() function. The only time I have found I needed to use SPLIT() was when I used the ANY() function. It forces a list item into a plain comma-separated text.

Second, I would recommend to filter the lists down to UNIQUE items and remove any blanks since blanks don’t seem to make sense in the logical comparison.

So I would try this…

INTERSECT(
          UNIQUE(select(Filtered Data[Course Name],
                        and([Course Status]=“COMPLETED”,
                            [_THISROW].[ORG ID]=[Org Id]))) 
          - LIST(""),
 
          UNIQUE([OE Online I-CAR Required]) 
          - LIST("")
)

If you still have issues, then I would recommend breaking down the pieces into temporary VC columns to inspect the intermediate results - which sounds like you have already been doing.

I hope this helps!

1 Like
5

UNIQUE() is redundant in that expression: list subtraction will inherently remove duplicates. UNIQUE([List]) - LIST(...) and [List] - LIST(...) produce the same results.

3 Likes
6

ALL duplicates or only the subtracted duplicates?

1 Like
7

All duplicates.

3 Likes
8

Yes, confirmed…and surprising, though I think I now recall Community conversations in the past about this. Thank you!

2 Likes
9

For me,this behaviour usually happens when i create VC in read only tables (more often with delta sync or server caching options enabled).

The solutions in my apps were to make my table not read only, or to make a manual change in the google sheet table to trigger the VC when table is re-syncing in app

2 Likes
10

Yes, happened to me once. I’ve seen me do it.

11

image
image722×196 10.2 KB

12

This is an interesting observation I’ll look into, because it’s very strange to me that the code seems to work until it is fully saved, so it seems like a glitch or other “unintended functionality”.

13

Willow, I like your thoughts here, but Appsheet rejects it because [OE Online I-CAR Required] is a list of lists and must be flattened. I’ll see if I can work on this.

14

Making the table not read-only did not solve the issue.

15

Flattening the list of lists ([OE Online I-CAR required]) appears to be the main issue. Using Split() to flatten it works before saving, but as soon as it gets saved, the results disappear. I figured this out by going to the “source” column and attempting to flatten it there. It just disappears, even though in testing (and before hitting save) it’s fine.

Is there some way to flatten this list of lists other than Split()? I’m going to try to look at it from that standpoint, or perhaps flattening it during the original derivation.

16

Well, as far as I can find, there’s no “good” way to solve this when having to flatten a list of lists, so I went back further into the app and eliminated that by making the requirment column just a plain old list. This further helps me because I can now pipe-delimit the source data so it deals correctly with fields containing commas. Anyhow, the original expression in questions now works as expected due to not having to flatten a list of lists, largely because “split” seems to be far to finnicky a function to expect reliable results in that situation.

2 Likes
17

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.