'List of list' vs 'list of text' error

[Kirk_Masden]

Hi!

A student of mine was using a copy of an app I made and encountered an error:

I don’t fully understand the problem but I’m guessing that the SELECT() expression is construed by the AppSheet platform as a “List of ‘List’” expression. Indeed, [Tags to list] is a list.

However, in the app that the student copied, this doesn’t cause an error:

Here too, [Tags to list] is a list. So, what might be happening?

The student was using the app without any problems but I think that his editing of the spreadsheet directly in the Google Spreadsheet interface may have caused this issue to occur. He has shared the spreadsheet with me and I had to fix some misaligned data. Or, perhaps something he typed into the “tags” column triggered the problem – but the tags in his sheet seem normal.

I’ve found a workaround to get his app going again so there’s not short-term problem. I would, however, like to understand what caused this issue, if possible.

I suspect that I may need to contact AppSheet support about this but I thought I’d address the inquiry to the community first. Thanks in advance!

[Koichi_Tsuji]

I have no idea what is the possible root cause, but only suspect version of appsheet might bring a difference. Better to submit support request to appsheet support desk as usual.

I’m curious what is going to happen to original app which retuning error to be on new version ie if you edit something and save change to subsequesnt version. Still returning an error?

[Steve]

@natalie?

[nkadonaga]

Thanks @Steve for the heads up!

@Kirk_Masden Right, the SELECT() expression produces a List of List.

We are introducing a new change where List arithmetic (e.g., SELECT(...) - LIST("") ) will now require that the element type of the second List is convertible to the element type of the first. (EDIT: Ignore the next 2 sentences-- see my next comment.) However, I think there is an error on our end, since Text should be convertible to List. So, I think you should open a support ticket so I can fix this.

The reason why your student’s app and your app have inconsistent results is that when we roll out a change, we start with a small percentage of users, then slowly ramp up to 100% of users. So, the app that is creating an error is in rollout, and the app that doesn’t create the error is not in the rollout. Hope that clears some confusion!

[Kirk_Masden]

Thanks for the explanation. I understand the basic idea, I think, but in order to be sure that the way I currently have my app configured will be OK is to test it. May we request that an app be included in rollout ahead of time, so that we can test it and figure out how to fix it before our users are affected?

[nkadonaga]

Kirk_Masden:

May we request that an app be included in rollout ahead of time, so that we can test it and figure out how to fix it before our users are affected?

The rollout system is currently on a by-app-owner-ID basis, not a by-app basis. So, if your test app had a different owner ID than the app with active users, we could arrange what you request if you send me which account ID should be in the rollout and which account ID should not be in the rollout.

[Kirk_Masden]

Thanks @natalie! I will write to “support” to request that my main account be put in the rollout.

[nkadonaga]

Yes, just to make absolutely sure we don’t mix things up: To put your account in the rollout means that the apps where you are Owner will start showing the error. All accounts will start showing the error eventually, unless they are part of the “exclude list.” If you need an app to not show the error, that app’s account owner ID must be excluded from the rollout.

When you create the support ticket, you can tell them to assign the ticket to me.

[Kirk_Masden]

Thanks! In my case, students only copy apps from me – that don’t use apps in my account. So, I think putting my main account in the rollout and fixing everything there is probably the way to go.

[nkadonaga]

Ah nevermind, I was mistaken-- apologies. It turns out it was our design that Text should not be convertible to List. Text is convertible to any type except for List. So, as a workaround, you’ll need to convert the output of SELECT into a List of a different element type (e.g., Text). You can convert the output of SELECT into a List of Text by using TEXT() and SPLIT() functions.

[Lynn]

@LeventK
Could the error in your demo app be related to this?

[LeventK]

Possibly yes @Lynn, following it

[Kirk_Masden]

natalie:

You can convert the output of SELECT into a List of Text by using TEXT() and SPLIT() functions.

Since it’s difficult for me to test at this point I’dl like to make sure I’ve understood properly. Would this produce a normal text list?

SPLIT(TEXT([Column of text lists produced with SELECT]))

I’ve tended to use CONCATENATE, as follows. Is that the same in this context?

SPLIT(CONCATENATE([Column of text lists produced with SELECT]))

By the way, you wrote as follows:

We are introducing a new change where List arithmetic (e.g., SELECT(...) - LIST("") ) will now require that the element type of the second List is convertible to the element type of the first.

But in my app, the original column that produces a list via SELECT is text. So, should I understand that List text will also be affected by the change?

[Steve]

Kirk_Masden:

I’ve tended to use CONCATENATE, as follows. Is that the same in this context?

I would recommend CONCATENATE(), as TEXT() could reformat your values for display (as is its function), which may not be your intention.

[Kirk_Masden]

Thanks @Steve!

Here’s what I’ve done to fix the problem in the student’s app:

split(concatenate(
	SELECT(Kankaku[Tags to list],ISNOTBLANK([Tags]),true)
	))
	-list(",")-list("")-list(" ") 

Originally, I had only been subtracting list("") but when I used the CONCATENATE + SPLIT technique to create the new list, I found that the commas that separated items were now being made into list items:

So, I also subtracted lists of commas and of spaces for good measure. That seems to have fixed the problem.

[Steve]

Try:

(
  SPLIT(
    SELECT(
      Kankaku[Tags to list],
      ISNOTBLANK([Tags]),
      TRUE
    ),
    " , "
  )
  - LIST("")
)

[Kirk_Masden]

Thank @Steve! That did the trick!

[LeventK]

@Lynn
It might be also related with this as well

Changes to SUM(), AVERAGE(), ABS(), and List arithmetic Announcements

Hello everyone! In the coming weeks, we will make these changes: SUM(): If given a List of Refs, returns a value with the referenced type, instead of a Ref AVERAGE(): If given a List of Refs with a numeric referenced type, returns a Decimal, instead of a Ref ABS(): If given a Ref with a numeric referenced type, returns a value with the referenced type, instead of a Ref List arithmetic (e.g., LIST(1, 2, 3) + LIST(4, 5)) will now require that the element type of the second List is conver…