is a bug or what else?

[racingmat]

Last week our company released an internal feature to allow workers to print labels automatically by their device through our Appsheet app. After few days working well, this morning it suddently stopped working.

We use a bot after we have put a new record in a table ETICHETTE.

This is the body of an http POST request we want to sent to printers:

[{
"supplier": "<<ANY(SELECT(PRODOTTI[Fornitore], [_THISROW].[Id Prodotto] = [Id Prodotto]))>>",
"date": "<<[Data]>>",
"printer_id":<<ANY(SELECT(UTENTE[Stampante Etichette], USEREMAIL() = [Id Utente]))>>,
"quantity":<<[Quantita]>>,
"product_name": "<<ANY(SELECT(PRODOTTI[Nome Prodotto], [_THISROW].[Id Prodotto] = [Id Prodotto]))>>",
"qr_code_url": "https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=<<[Id Prodotto]>>"
}]

When resolved, the fields "supplier" and "product_name" should have a value but now we just get an empty string. In an attempt to debug this issue we sent via email the same body and we noticed that it only works in preview mode.

i have try to sent an email with the same value and result is the same but if i test the result with preview email the result is correct. 

Could you help us understand this issue?

[scott192]

Your HTTP POST is missing quotes around a number of expressions.

The format of the object is for each of the identifiers and values to have quotes around.

"identifier1":"<<[Value1]>>",
"identifier2":"<<[Value2]>>" etc

[{
"supplier": "<<ANY(SELECT(PRODOTTI[Fornitore], [_THISROW].[Id Prodotto] = [Id Prodotto]))>>",
"date": "<<[Data]>>",
"printer_id":<<ANY(SELECT(UTENTE[Stampante Etichette], USEREMAIL() = [Id Utente]))>>,
"quantity":<<[Quantita]>>,
"product_name": "<<ANY(SELECT(PRODOTTI[Nome Prodotto], [_THISROW].[Id Prodotto] = [Id Prodotto]))>>",
"qr_code_url": "https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=<<[Id Prodotto]>>"
}]

Those higlighted sections are missing the quotes

[racingmat]

Sorry but you made an error.

this is the only two work correct 
"printer_id":<<ANY(SELECT(UTENTE[Stampante Etichette], USEREMAIL() = [Id Utente]))>>,
"quantity":<<[Quantita]>>,

if is a number and not a text we can use without " " .

so problem is another.

[Marc_Dillon]

Do the expressions in question return the appropriate values when tested with a virtual column in the app? Are there any Security Filters or Slices here? What changes did you make between the time it was working and when it suddenly stopped?

In an attempt to debug this issue we sent via email the same body and we noticed that it only works in preview mode.

i have try to sent an email with the same value and result is the same but if i test the result with preview email the result is correct

---

What do you mean by "preview" mode? And how are you sending emails as tests?

[racingmat]

For me is this bug

[OptimiX_XcrY]

That bug was recently fixed by appsheet team, at least in my apps 

[racingmat]

For now my problem is not solved. Maybe need time the fix for propagate on all server.

[Axel_Pro]

I dare to assume that [_THISROW] inside SELECT (when used in a bot) does not work quite correctly, or columns with the same name [Id Prodotto] and [_THISROW].[Id Prodotto] are somehow incorrectly processed by the bot.

Try:
1. Create a previous step (SPEP0) with action type "Run a data action" ==>> Set_value_row

Insert any column value into the string without changing it, for example in "Id Prodotto" - [_THISROW].[Id Prodotto]

2. Then change your query to:
[{
"supplier": "<<ANY(SELECT(PRODOTTI[Fornitore], [SPEP0].[Id Prodotto] = [Id Prodotto]))>>",
"date": "<<[Data]>>",
"printer_id":<<ANY(SELECT(UTENTE[Stampante Etichette], USEREMAIL() = [Id Utente]))>>,
"quantity":<<[Quantity]>>,
"product_name": "<<ANY(SELECT(PRODOTTI[Nome Prodotto], [SPEP0].[Id Prodotto] = [Id Prodotto]))>>",
"qr_code_url": "https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=<<[Id Prodotto]>>"
}]

Not sure if it will help but you can try. For example, I have a column named [ID] inside the select was replaced in the bot with [InstantID].

As a debugging step, at the first step, you can write expressions like ANY(SELECT(PRODOTTI[Fornitore], [_THISROW].[Id Prodotto] = [Id Prodotto])) into the current table - see what the bot actually returns without sending POST.

P.S.
You can also try to rename the [Id Prodotto] column in one of the tables, for example to [Id Prodotto_]

and change the expression to ANY(SELECT(PRODOTTI[Fornitore], [Id Prodotto_] = [Id Prodotto]))

This can be done without pasting the previous step, but it will change all the formulas in the application, which may not be very convenient.

This is all, of course, "crutches", because. there is a real problem with handling [_THISROW] inside SELECT in bot