November 17, 2021

AppSheet Release Notes
1

Deployment Time: 1:57 PM PST

Features & enhancements

None.

Bug fixes

Item Description
Bug Fixed a bug sometimes causing incorrect filter results. This bug affected filter expressions evaluated within the app (such as slice filters or inline views) where the filter condition involves an IN function and the list being searched is a column in the table being filtered. For example, a slice filter condition like IN(UserEmail(), [List of Emails]).

Rollout changes

None.

Preview announcements

The AppSheet Preview program lets app creators try out new app features that are not yet fully supported. Learn how to participate in the AppSheet preview program for app client features.

  • No new preview features were released today.

What’s currently available in the Preview program?

Item Description
Feature Table view

Table View now supports freezing the first column while scrolling horizontally. For details, see Freezing the first Column of a Table View - in Preview Program.
Feature Chart Editor

App Creators can now make use of our new chart editor and the new and improved charts it can create. Learn more.
Feature Detail views

Rich text formatting is now available in Detail views. For details, see this announcement in the community.
3 Likes
2

Liz_Lynch:

Fixed a bug sometimes causing incorrect filter results. This bug affected filter expressions evaluated within the app (such as slice filters or inline views) where the filter condition involves an IN function and the list being searched is a column in the table being filtered. For example, a slice filter condition like IN(UserEmail(), [List of Emails]) .

Does this change makes IN() aware of the List-of-Lists scenario?
Example:

  • USERS is a table
  • [COMPANIES] is EnumList
  • USERS[COMPANIES] is a list of lists

Should IN() take this as a big list? Like if it where a list addition?

3

IN() has never supported lists-of-lists. This is not considered a bug.

2 Likes
4

Yeah, that’s why I’m asking.

Look at this:

Enhance CSV Export to Support User Specified ‘Search Filters’

This problem has been corrected. The server now honors all four forms of row filtering when performing CSV Export.

There was never a problem, Export CSV was not intended to work that way and now does, which is great.

I suspect that this might be the same thing:

[Having trouble with expression for turning a field into a list for using in a IN() expression](https://community.appsheet.com/t/having-trouble-with-expression-for-turning-a-field-into-a-list-for-using-in-a-in-expression/58514/26) Questions

This is new(-ish?). It certainly didn’t used to work this way!

1 Like
5

Tested and

IN(
	[_THISROW],
  SPLIT(
  	DIM_CURRENTUSER[AN_ENUMLIST_BASETYPE_REF],
    " , "
  )
)

Is working the same way as

IN(
  [_THISROW],
  DIM_CURRENTUSER[AN_ENUMLIST_BASETYPE_REF],
)

@MultiTech_Visions Can you troubleshoot with one of your apps?

2 Likes
6

Just FYI @SkrOYC, for your formula:

SkrOYC:

auto> IN(> [_THISROW],> SPLIT(> DIM_CURRENTUSER[AN_ENUMLIST_BASETYPE_REF],> " , "> )> )> >

I would always wrap the “list of a list” pull in concatenate

DIM_CURRENTUSER[AN_ENUMLIST_BASETYPE_REF]> > > Concatenate(DIM_CURRENTUSER[AN_ENUMLIST_BASETYPE_REF])- Turning it into a string - that way SPLIT() can process it correctly. (Because split() is expecting a string value, not a list of list)

IN(
	[_THISROW],
  SPLIT(
  	Concatenate(DIM_CURRENTUSER[AN_ENUMLIST_BASETYPE_REF]),
    " , "
  )
)
1 Like
7

SkrOTC,

IN expressions currently work differently on the client and the server. This is not by design. It is simply an accident.

For example, consider the case where <list1> contains “Apple”, “Cherry” and <list2> contains “Apple”, “Banana”, “Cherry”.

On the client, IN(<list1>,<list2>) checks each value in <list1> against the values in <list2>. In our example, the client would compare “Apple” to each of the values in <list2> and match on “Apple”. It would then compare “Cherry” to each of the values in <list2> and match on “Cherry”. The IN expression would then return ‘true’. This is what you are probably expecting.

On the server, IN(<list1>,<list2>) takes the values in <list1> and constructs a string containing the list values separated by the List Separator character. It then compares this concatenated string value against the values in <list2>. In our example, the server would compare “Apple , Cherry” to each of the values in <list2> and no match would be found. The IN expression would then return ‘false’. This is probably not what you expect. If <list1> happens to contain only a single value, that value would match one of the values in <list2> and the IN expression would return ‘true’.

The problem is that we cannot simply change the server expression system because existing apps may depend on the current behavior, flawed as it may be.

I am planning to fix the problem by adding a new IN expression that fixes this problem. I need to decide what to call the new IN expression, but for the moment let’s call in IN2. We will implement IN2 on both the client and server. It will work in the same way as IN currently works on the client. You can then use IN2 to get the behavior you expect.

We are open to your suggestions, but this is what we hope to do in the absence of a better idea.

6 Likes
8

Phil:

I am planning to fix the problem by adding a new IN expression that fixes this problem. I need to decide what to call the new IN expression, but for the moment let’s call in IN2. We will implement IN2 on both the client and server. It will work in the same way as IN currently works on the client. You can then use IN2 to get the behavior you expect.

Perhaps handle it the same we’re handling the change to =?

1 Like
9

Very interesting to know, also because I was talking about a different thing and now I’m more interested to this than before.

Some time ago, and the reason @MultiTech_Visions is wraping the list (second argument inside IN()) inside Concatenate and Split, IN() had troubles with list-of-lists like this:

IN(
  "A",
  LIST( /*first list*/
    LIST("A", "B", "C"), /*list 1 inside list*/
    LIST("D", "E", "F") /*list 2 inside list*/
  )
)

So, It would return false because neither of the two items under the first list is the strign “A”
So, to fix this, as Matt mentioned, we needed to translate the first list to a string resulting into "A , B , C , D , E , F" and then spliting this string to get a new list that would be equivalent to

LIST("A", "B", "C", "D", "E", "F")

So, now it seems like the original formula above is being interpreted as if there was a List Addition between the lists 1 and 2 so, at the time of writing, that particular IN() expression returns true

Question is:

Was this changed/upgraded lately and now it’s working this way? Just to make sure it’s the right time to remove the Split() and Concatenate() without breaking anything.

Another one:

The change told by @Liz_Lynch is related to the problem you mentioned about client vs server operation, the change I mentioned above, both?

1 Like
10

@Phil
BTW, it’s awesome how usefull IN() is and I understand that any important change to the behaviour that users expect from it would be a problem.
If I get it right, IN() on client side can receive a list as the first argument and then compare all the values inside that list to the second list/argument while server treats the first argument as a value/string.
Basically, this formula:

IN(
  LIST("A", "B"),
  LIST("A", "B", "C", "D", "E")
)

Returns true on client and false on server?

A perfect naming scenario would be INVALUE() and INLIST() or, better yet, VALUEIN() and LISTIN()
I guest the problem is that there are so many expression using current IN() that you can’t just change everything.
It would be great to leave IN() as is and announce VALUEIN() and LISTIN() as a replacement of IN(). Maybe the IN() that IN() should have been.

Also, why don’t add another argument to IN()like this? (I know it may be harder than I think):

IN(
  LIST("A", "B"),
  LIST("A", "B", "C", "D", "E"),
  "List" /*Third argument indicating which type of data is on the first one*/
)


IN(
  "A",
  LIST("A", "B", "C", "D", "E"),
  "Value" /*Third argument indicating which type of data is on the first one*/
)

And if there is no third argument, treat IN() the way is working today, as if it where on auto

1 Like
11

The thing is it’s working anyway and I can’t explain that to myself

1 Like
12

SkrOYC:

auto> IN(> LIST("A", "B"),> LIST("A", "B", "C", "D", "E")> )> >

There is Intersect() already…

  • Kinda seems like what you’re looking for is a shorthand for
    IsNotBlank(Intersect({List1}, {List2}))
1 Like
13

Yeah, IN() seems to be working like this:

LIST("A", "B")
=
INTERSECT(
  LIST("A", "B"),
  LIST("A", "B", "C", "D", "E")
)

Also, this is not something I want to do, I’m just helping to understand @Phil’s post and the way IN() actually works

[November 17, 2021](https://community.appsheet.com/t/november-17-2021/58652/7) Feature Release Notes

SkrOTC, IN expressions currently work differently on the client and the server. This is not by design. It is simply an accident. For example, consider the case where

14

You raise a good question about what the following IN expressions means.

IN ( LIST(“A”, “X”),
LIST(
LIST(“A”, “B”, “C”),
LIST(“X”, “Y”, “Z”)
)
)

I assume this would return ‘false’ because “A” and “X” are not both present in either LIST(“A”, “B”, “C”) or LIST(“X”, “Y”, “Z”).

If you wanted to check the Union of the last two lists, you could use the List Addition function. The IN expression would look like:

IN ( LIST(“A”, “X”),
LIST(“A”, “B”, “C”) + LIST(“X”, “Y”, “Z”)
)

This would return ‘true’ because “A” and “X” are in the output of the List Addition function, namely LIST(“A”, “B”, “C”, “X”, “Y”, “Z”)

Alternative you could use the List Subtraction or List Intersection functions on the last two lists.

1 Like
15

Phil:

IN ( LIST(“A”, “X”),> LIST(“A”, “B”, “C”) + LIST(“X”, “Y”, “Z”)> )

I tested with virtual column, but it returned false instead of true.

@Takuya_Miyai

1 Like
16

Hi Koichi san,

If you are running the expression on the existing server code, that expression will fail.
It fails for the reason I described above, that is:

On the server, IN( <list1> , <list2> ) takes the values in <list1> and constructs a string containing the list values separated by the List Separator character. It then compares this concatenated string value against the values in <list2> . In our example, the server would compare “Apple , Cherry” to each of the values in <list2> and no match would be found. The IN expression would then return ‘false’. This is probably not what you expect. If <list1> happens to contain only a single value, that value would match one of the values in <list2> and the IN expression would return ‘true’.

Specifying a List for the first argument will only start working on the server once I do the work described here:

I am planning to fix the problem by adding a new IN expression that fixes this problem. I need to decide what to call the new IN expression, but for the moment let’s call in IN2. We will implement IN2 on both the client and server. It will work in the same way as IN currently works on the client. You can then use IN2 to get the behavior you expect.

My response regarding an IN expression where the second argument was a List of Lists was my attempt to answer the question OscarYC raised about how that might work in the new IN2 function.

3 Likes
17

Phil san,
Thank you for your clarification and advice.
Me, personally I never know that we are able to pass the list to 1st argument for IN expression before (as it is not said like that on the documentation) so I reckon my existing application wont have such type of expression, but it is good and surprising to know that we are able to pass List as 1st arg, which could extend the capability using this expression in the future, while we have to pay utmost attention to the point the expression will return different value on client and server.

I still examine this interesting chain of posts under this thread to digest.

The new finding (least to me) was the list expression being passed to 2nd urgument will return different results. I will digest what I found on follow up post here.

3 Likes
18

In new chart editor, the fields window is too short. When there are a lot of columns, only way to drag and drop the Colum to the field window is by zomming out. If you have over 60/70.colums, you have to zoom out to almost max.

It will be nice we can assign fields from drop downenu.

2 Likes
19

You can use the Filter to search for column names.

3X_0_c_0c566af44ca5f91d936e47278f58522e78f3b52c.png
3X_0_c_0c566af44ca5f91d936e47278f58522e78f3b52c.png423×361 12.5 KB

The new chart editor still in Preview Program

[New Charts in AppSheet applications - in Preview Program](https://community.appsheet.com/t/new-charts-in-appsheet-applications-in-preview-program/43287) Announcements

The following feature has been added to the Preview Program. Feel free to test the feature and report issues or possible improvements in this thread. App Creators can now make use of our new chart editor and the new and improved charts it can create. There are still features to be added before general availability, and the new charts should not be used in production apps until general availability as they may be unstable in the future. We’ve been trying to create a charting experience that all…

2 Likes