Report stopped working, now generates an error?

[Patrick_Paul]

I have an emailed report that is scheduled.  I last modified the template on 12/2 at 1:19pm and successfully ran a test subsequently at 1:23pm.  I have the emailed report.  This week it did not run as scheduled.  Upon attempting to manually execute it, it failed.  The report now generates an error when tested as follows:

Task 'Send Mentor Plastic Report' Body template. Expression 'orderby(Select(IcarData [I-CAR ID],any([tdp mentor profile][mentor status])=”Active Approved”), any([tdp mentor profile][area tech trainer]))' is invalid due to: Expression had error..

Which needless to say is not very specific or helpful.  Nothing has been altered in the template or app.  However, I did open the app and it did a "consistency update" on its own in between.  Any ideas what could be going on?  I don't see any obvious problems.

[simonccccc]

did you solve?

[AleksiAlkio]

Don't know what your column types are, but it sounds the ORDERBY() doesn't know what column it should use to sort as you are using ANY() for that purpose 

[Patrick_Paul]

I thought that might be the problem as well (despite it running successfully with the "any()" in place last week), so I removed that as the sort parameter and replaced it with a "normal" column instead.  Still get the same error.

[AleksiAlkio]

So.. what is your new formula and what is the error message?

[Patrick_Paul]

Task 'Send Mentor Plastic Report' Body template. Expression 'orderby(Select(IcarData [I-CAR ID],any([tdp mentor profile][mentor status])=”Active Approved”), [location])' is invalid due to: Expression had error..

[Steve]

Your ORDERBY() expression is missing at least one argument.

[Patrick_Paul]

Steve, can you elaborate?  I'm not seeing what you believe I'm missing?  Orderby needs a list of keys and a parameter to sort by separated by a comma.  My select() function provides the list of keys separated by a comma, then the any() function provides the sort parameter.  What might I be missing? 

Again, the strange thing is that this template ran successfully last week and no changes have occurred since then to the app or the template other than an automatic "consistency" update applied by the editor when I opened it to glance at something since then.

[Steve]

---

@Patrick_Paul wrote:

Orderby needs a list of keys and a parameter to sort by separated by a comma.  My select() function provides the list of keys separated by a comma, then the any() function provides the sort parameter.  What might I be missing?

---

Read the doc. You are absolutely missing something.

[Patrick_Paul]

I guess I need a hint!  I've read the doc and I've done dozens of templates just like this, and I'm either blind to what I should be seeing or just flat out missing it.... 🤔

Here's a very similar expression used in another template that executes perfectly fine, I don't see any difference in syntax:

orderby(Select(Trainees [RandomID],[current status]=”Active Approved”), [area tech trainer])

[Patrick_Paul]

Guys, I'm finding now that across the 13 apps I maintain, I'm receiving this same error on various *previously working* reports.  Some have been running on a routine basis for over a year and have not been modified.  I believe Appsheet has made a back-end change that is producing this problem.

[Patrick_Paul]

Update:  I found across 3 apps 4 reports not working, some of which have been running for over a year with no problems.  All involve the use of this sort of dereference in the template code:  [Some reference][Some column].  I don't use this frequently so it was easy to spot as a common thread.  I am working with support now, I assume Appsheet has introduced a bug.

[Patrick_Paul]

Update:  Support is still working on it.  The issue seems to center around select or filter functions that make use of [Some ref][Some column] to generate a list of keys producing errors now when they did not in the past.

[PCV_Tech]

I had the same issue, 3 of my apps stopped working on December 5th, i got around the issue temporarily by moving the targeted data in the START expression into slices. Just this morning i realized by testing with another Webhook that it only had issues with dereference. 

I have dealt with support in the past and for the most part they have helped, but getting passed that first line of support just didn't seem very intriguing at the time when i had a lot of other stuff on my plate. I appreciate you getting after it.

[Steve]

Developers have been making a lot of changes to Expression Assistant in the app editor. Many of those changes have to also be implemented in the app and on the servers. To the best of my understanding, the app editor, the app itself, and the servers all have different software, so reproducing changes in one to the others can be a challenge. I'd guess your problems are rooted here.

[Patrick_Paul]

Wonderful.  Needle in a haystack then.  You would think I wouldn't be the only one adversely affected.

[Steve]

I imagine you aren't. That construct, dereferencing a column of list of Ref, is not widely used--it's a more advanced tactic. And it's possible you're using it in an unusual way. Myself, I'm willing to chalk this up to the kind of thing that happens given the implementation complexity rather than some deficiency with the development or support teams.

[Patrick_Paul]

@Steve Any chance you'd like to talk to the support folks?  They don't seem to understand the issue, or that the issue is new, or something.  They have basically advised me that I have re-engineer the whole thing around not using the list dereference and their reasoning behind that was rather nonsensical:  That you cannot reference a table not called out in the event that triggers a bot, so I must use "for each row in table" and have separate emails for each row (which is not going to happen as that doesn't fit my use case at all) and it be triggered by the length of the table over time instead of on a schedule. 

Scheduled events *never* reference tables in the triggering event!  I hate to sound critical, but I don't think they know what they're talking about.  That, or they're trying to get me to use a work-around that will not produce like results.

@PCV_Tech I encourage you to file a ticket if you have not already.

[Steve]

Attn @devingu 

[Steve]

Does that ORDERBY() expression work as an App formula of a virtual column?

Please post screenshots of the following:

• The columns list for the IcarData table that includes the I-ICAR ID and tdp mentor profile columns.
• The Type Details section of the configuration of the tdp mentor profile column.
• The configuration of the Send Mentor Plastic Report task.
• The body template, including at least the entire <<>> tag that includes the problem expression.

[Steve]

@Patrick_Paul, respond, please.

[Patrick_Paul]

Thanks for your help, here goes:

I am not sure what you're asking me to do about the orderby() in a virtual column.  I tried swapping that code out with a simple column rather than the any() expression with the dereference and it made no difference in the error.  Again, this whole thing worked perfectly fine as-is on Dec 2nd.

Not sure how much of the following one you want:

It is worth noting perhaps that in this case (this is 1 of 4 previously working reports that ceased to function that utilize a list dereference in the select or filter function of a Start: statement in the template), every time I use [some ref][some column] it is a 1:1 relationship.  There can only ever be 1 result in the "list".  Appsheet always "assumes" many:many (or perhaps 1:many), but that is not how this is designed from a database perspective.

[AleksiAlkio]

As per your screenshots, the SELECT() and its content should work, but for some reason it doesn't. I would guess the reason is coming from this part ANY([...][...]) because when you tested ORDERBY() with a simple column, it didn't solve it. If you modify it like ORDERBY(SELECT(....,TRUE),[Your column]), does it work then?

[Patrick_Paul]

No effect.  Already tried it.

Task 'Send Mentor Plastic Report' Body template. Expression 'orderby(Select(IcarData[I-CAR ID],any([tdp mentor profile][mentor status])=”Active Approved”,TRUE), [I-CAR ID])' is invalid due to: Expression had error..

[AleksiAlkio]

I meaned Orderby(Select(IcarData[I-CAR ID],TRUE), [I-CAR ID])

[Patrick_Paul]

Well, of course it works then 😁.  We've eliminated the known issue of [some ref][some column] in the select statement doing that.  We know that's the source of the bug.  Whatever changed around Dec 5th is what stopped that working.

Elaborating, I have built literally 100+ similar reports (if my organization was cookie monster, reports would be chocolate chip flavor)... The only ones that started failing after Dec 5th or so were the 4 that use [some ref][some column] in the select/filter.  All the others are fine.

[AleksiAlkio]

Have you also tested what happens with Orderby(Select(IcarData[I-CAR ID],any([tdp mentor profile])="something"),[I-CAR ID]) ?

[AleksiAlkio]

I made a small test and yes, it's a bug. When using ANY([...][...]) it doesn't work with the SELECT() or ORDERBY(). One workaround is to write it as..
ORDERBY(
SELECT(ICarData[I-car ID],
ANY(SELECT(Mentors[mentor status],[I-Car number]=[_THISROW-1].[I-Car ID]))="Active Approved"),
ANY(SELECT(Mentors[area tech trainer],[I-Car number]=[_THISROW-1].[I-Car ID]))
)
Though it doesn't make sense to read the whole"Mentors" table as you could read it through the smaller virtual list.

EDIT: It seems to be related to scheduled Bot only as it works fine with the Data change Bot.

[Patrick_Paul]

Unfortunately, everything is down this morning (I alerted you on a separate thread someone else had already started).   Would you like me to message you my support ticket privately?  Or is this something that's going to take awhile to fix and I need to try your work-around above when I have time and things are back up and running?

[AleksiAlkio]

The system should be live again.

[Patrick_Paul]

I will try this as soon as I have time, unfortunately with the holidays upon us, I'm currently swamped with work that is of an even higher priority.  Any idea if/when the underlying issue can be resolved?  Thanks for all your help!

[Patrick_Paul]

@AleksiAlkio I have just figured out that *ALL* automations across *ALL* of my apps are working intermittently as of now.  This is becoming very difficult to manage as we have critical business processes running off this stuff. 😕

[Steve]

Please contact AppSheet Support for help with this. I know it seems pointless, but that is the official channel and the best way to impress upon Google how widespread a problem is.

Can you describe how else your bots are affected?

[Patrick_Paul]

It was transient in nature, but lasted long enough to cause some chaos.  For about 15-20 mins, bots simply didn't do anything.  If I tried to run a "test", it just said "ERROR".  But it's all back to working now. 😞

[Steve]

Is there actually a trailing space in the table's name? If so, that's asking for trouble. If not, remove the space in the expression so IcarData [I-CAR ID] is instead IcarData[I-CAR ID].

[Patrick_Paul]

LOL, already thought of that awhile ago.  Makes no difference.

[Steve]

---

@Patrick_Paul wrote:

Any chance you'd like to talk to the support folks?

---

Dear God, no. Your only hope is that @AleksiAlkio will intervene.

---

@Patrick_Paul wrote:

I hate to sound critical, but I don't think they know what they're talking about.

---

I'd call that an accurate assessment.

[wabrian · Accepted solution]

This was indeed a breakage that AppSheet introduced. This will be fixed in the first release of the new year. Apologies for the inconvenience and thank you for reporting.

[Patrick_Paul]

@wabrian Thank you!  This will save me a lot of time versus trying work-arounds.  I appreciate your help!

[wabrian]

FYI, still working on getting the fix out, should hopefully be out early this week, apologies for the delay.