Slice based on enum list

As my titles indicates, I’m trying to make a slice based on an enum list that is located in another table. Here’s an image showing the expression and what I get when I test the expression:

Note that both B1 and B2 are evaluated as TRUE. This is as it should be because at the time of this test the contents of the cell of the LOOKUP expression are “B1 , B2”. So far, so good.

When I look at the results of the slice in my app, however, I only get B1. I wonder if the following note about a “randomly chosen value” has anything to do with my problem:

When I look at the actual results of my slice (not the Y / N in my expression tester) it would seem that the slice is base on the first available item in my enum list, not on the whole list.

If this is not an error, I think it should be. It’s very confusing to have test results that are different from the slice results.

Solved Solved
0 22 1,233
1 ACCEPTED SOLUTION

The issue does, indeed, seem to be fixed in my account. The expressions are working properly now.

View solution in original post

22 REPLIES 22

Steve
Participant V

LOOKUP(...) should be SPLIT(LOOKUP(...), " , ").

Thanks for the quick response. Actually, I’ve tried that:


3X_0_1_01a1dcfc0644e4b20c3a37a3f3ca9eebb030f1c3.png

In this case, the results seem to be the same in every way.

I can’t figure out what this “one randomly chosen value from this list” is about. The app behaves as if it’s not checking every item in the list when I makes the slice.

Is a column involved in this expression marked for PII?

I’ll check. I’m not familiar with “PII” but I’ll try to look for it.

3X_f_8_f8215d3c452c7cc47b2f344fc27475c83e4132d5.png

3X_0_9_096d0c7ac450aa56fa410bd816231d29e69fce0c.png

The reason I ask is because I’ve noticed the Test feature in Expression Assistant seems to produce unreliable results when a column is marked as Sensitive data. Consider disabling Sensitive data for those columns, or simply Save the changes and test with the live app.

Thanks! Yes indeed. I had some columns marked PII. I just removed those designations. But the problem remains: both the text results and the actual results are the same as before.

If it were only a matter of the test behaving unexpectedly but that app working properly, I wouldn’t have much of a problem. The real problem is that I can’t get the app to do what I want (include records if for which the [CEFR] value is one of the values in the enum list. Shouldn’t the IN() expression work to select both B1 and B2 records for me if I have an enum list of “B1 , B2”?

I’ll have to get back to you later, Kirk. Got an outage to help with!

Sure. No hurry. It’s bedtime in Japan. I’ll come back to this tomorrow.

Steve
Participant V

This:

LOOKUP(lookup-value, table, lookup-column, result-column)

is equivalent to:

ANY(SELECT(table[result-column], ([lookup-column] = lookup-value)))

Historically, ANY() has been said to return “one randomly-chosen value” from the list. In practice, it returns the first value of the list. SELECT(), however, isn’t guaranteed to produce a list in a particular order.

Thanks for the explanation. I’ve found a workaround, but I’m still confused about why the workaround is necessary.

First, the workaround.

The enum list column I’m using to determine what is included in the slice is CEFR, in another table. I made a virtual column to turn that into text with a marker that I can use to split it later. Here’s that expression:

substitute(concatenate([CEFR])," , ","+")

This is named “CEFR_text”.

Then I was able to use this in the following expression in the slice successfully:

in([CEFR],split(LOOKUP("1", "Settings", "Key", "CEFR_text"),"+"))

So, my problem is basically solved but I wonder why I had to figure out a workaround and why the test results didn’t conform to the actual behavior. Do you think this is an error or issue that support@appsheet.com should know about?

P.S. I wound up using the SUBSTITUTE() expression because the following doesn’t work:

concatenate([CEFR])

and

in([CEFR],split(LOOKUP("1", "Settings", "Key", "CEFR_text")," , "))

yes, please register your case formally to support desk by sending mail to support@appsheet.com
We have similar issues which have been reported to support desk in recent days we are waiting for the advise back from them.

We basically made yes/no type of virtual column which is returning Y or N, and use this slice, for example, just slice the records with Y values.
Expression assistant and test is indicating the result correctly.
Then we made a table view out of this slice, but the table view is just showing few rows only, while we are missing other records with Y value.

Sounds like our issue is standing on the same root cause.

@Takuya_Miyai

OK. I’ll report it. By the way, my workaround is faster (immediate results, sync not required) in my case than making a virtual column in the table to be sliced (I also tried that). The table I need to slice has 8000 records so I want to be efficient.

Until this gets fixed, my workaround might help.

Thanks.

Virtual expression is always needing a wait until the full sync is finished to be recalculated.
The expression pushed into the slice conditions is different. No need to wait for sync to be complete, as it could be client side job, so you see immidate changes upon data change made on the client.

@Takuya_Miyai is attending to the case i mentioned. Once it is cleared through the support desk, we will let you know.

Right. But my “settings” table is tiny – just one row. So, the virtual expression I put in that table to change enum list into text that I can split later is virtually costless. It’s much better than making a virtual column in the table with 8000 rows of data.

I’ve written to support@appsheet.com about this.

Thank you for posting this workaround!  🙂

I figured out 3 different ways which, per the Appsheet documentation, were supposed to work but did not because of this error ;(

You rock!

There is a known issue with slice filters that involve EnumList producing incorrect results. We’re about to begin rolling out a fix for this and I’ll be adding Kirk’s account to receive the fix early. For anyone else currently experiencing this kind of issue, if you’d also like to receive the fix early please open a support ticket (or reply on an already opened one) and mention my name, this thread, and your account ID number.

The issue does, indeed, seem to be fixed in my account. The expressions are working properly now.

What did you do? Im trying to do the same but i have the problem you had. 

I'm sorry that I failed to notice your question.  I hope your problem has been resolved.

@Adam-google this problem still exists, at least currently. Test shows correct Y/N, but slice only showing first corresponding option. 

Looks like this is not solved for the current version of Appsheet. Help!

@Kirk_Masden 

The problem (filter conditions for slice with IN expression yeild wrong result) has been perstiting for long years.  Recently, we identify similar bug with Intersect expression for slice filter and reported to the support desk who spoke to Eng team and recently and seemingly release a fix for this bug.

Not sure if it was intended or not, but it appears the same fix is solving issue of IN expression with slice. Under my account, I tested with IN() inside slice filter condition, the result shows the correct sub set of the rows.

This is good one, and solving the long-runninng headache with us.

 

Top Labels in this Space