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.

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

1 Like

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


Screen Shot 2021-06-12 at 01.32.37

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.

image

image

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. :slight_smile:

1 Like

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")," , "))

1 Like

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

2 Likes

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. :slight_smile:

1 Like

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.

3 Likes

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. :slight_smile:

2 Likes