Strange error in Automation when using Text([Image type column]).

AppSheet AppSheet Q&A
1

1.There is one table as follows.
[Text_Image] has the following Formula.

TEXT([Image])

1.png
1.png1799×600 57.7 KB

2.If you upload an image in a new registration and save it, the URL is not entered in [Text_Image],
If you open the relevant record in Edit and SAVE, the URL will be entered in [Text_Image].

2.png
2.png508×893 259 KB

3.png
3.png510×895 242 KB

When saving a new registration, the expression [Text_Image] is executed with no images uploaded yet.
In other words, as the formula is being executed on an empty value, the result of this formula will also be empty and no URL will be generated.
We assume that this is the cause of 2.

Hence, if a record with an image already uploaded is opened in Edit and saved,
Formula in [Text_Image] will generate a URL as a result.

The above is the first part and what we wanted to convey most in this contribution is the behaviour of Automation as described below.

If you have a column with TEXT([Image type column]) in Formula,
Automation has been found to generate strange errors and not work properly.

3.If you create and run the following Bot, an error will occur

4.png
4.png1789×1010 66.1 KB

Error messages include.

Error: ‘Set Column Values’ Data action ‘‘Set Column Values’ Data action ‘Action for set note’’ failed with exception Value ‘Data_Images/0ace11a2.Image.064815.jpg’ in field ‘Text_Image’ cannot be converted to type ‘Url’. .

Why do these strange errors occur?
I believe that this error should not occur.

ps. Just to be sure, I are trying to adopt the following as an alternative to the above.
https://support.google.com/appsheet/answer/10107317?hl=en&sjid=1176134514394052012-NA

2

I got exactly the same behavior when I tested this.

A work-around on Step 3 is to set the expression (TEXT([image col])) in the Initial Value property and set “Reset on Edit” to CONTEXT(“Host”)<>“Server” to prevent the bot from trying to do the conversion.

I found no work-around on Step 2. (You have to open a Form to edit the record to force the conversion…)

2 Likes
3

I think one reason it does not enter the URL in the new form is, the URL is not yet formed till the record is saved.

You may want to try to use an event action on form save to set the value of the column [Text_Image] to TEXT([Image]) . The action can be of “set the values of some columns in this row” type

1 Like
4

Thank you for a great solution!

5

I think one reason it does not enter the URL in the new form is, the URL is not yet formed till the record is saved.

I agree.

You may want to try to use an event action on form save to set the value of the column [Text_Image] to TEXT([Image]) . The action can be of “set the values of some columns in this row” type

I tried the above, but unfortunately the URL was not entered in [Text_Image] when registering a new user.
But thank you for the advice!

1.png
1.png1153×741 51.3 KB

2.png
2.png1505×228 36.8 KB

6

How are you firing the action?

7

Form Saved in Event Actions.

2.png
2.png1505×228 36.8 KB

8

It takes a while for the image to be transferred to the cloud storage and this event action unfortunately fires too soon.

I would set the condition of this action to

AND(ISBLANK([image_text]), ISNOTBLANK([image]))

and display it as Inline and let the user press it manually. This at least saves them a few clicks…

1 Like
9

As I thought, it seems to be the case.
Thank you for your suggestion!

10

Sorry for a late reply. You could implement the action through a bot and then having a wait step of 5 minutes before action is executed through bot. In this case, the URL will update after after 5 minutes or so after the new record is added. The wait steps are not very accurate in terms of time delay ( It could vary slightly -longer than 5 minutes) but if a slight delay is acceptable, bot will populate the URL automatically.

The overall bot looks like below:

Suvrutt_Gurjar_0-1722763681709.png
Suvrutt_Gurjar_0-1722763681709.png980×801 25.5 KB

Event fires on Add row as shown below

Suvrutt_Gurjar_1-1722763781870.png
Suvrutt_Gurjar_1-1722763781870.png492×647 22.4 KB

Wait step looks like below:

Adds a delay of 5 minutes after record is added. This wait step basically allows the URL to be formed properly in the record’s backend before it is copied into another URL column {Text_Image]

Suvrutt_Gurjar_2-1722763838681.png
Suvrutt_Gurjar_2-1722763838681.png683×512 19.8 KB

The last step of the sets the URL in the [Text_Image] column.

Suvrutt_Gurjar_3-1722763911139.png
Suvrutt_Gurjar_3-1722763911139.png1482×453 30.4 KB

Please use the following expression for setting the URL

CONCATENATE(
“https://www.appsheet.com/template/gettablefileurl”,
“?appName=”, ENCODEURL(“AppName”),
“&tableName=”, ENCODEURL(“Table Name”),
“&fileName=”, [Image Column Name]
)

11

We really appreciate your detailed suggestions with images.
Thank you!

In fact, we had already tried the method of using the following URL expression you suggested.

CONCATENATE(
"https://www.appsheet.com/template/gettablefileurl",.
"?appName=", ENCODEURL("AppName"),.
"&tableName=", ENCODEURL("Table Name"),.
"&fileName=", [Image Column Name].
)

From my verification results, when using the above URL expressions, the URLs were input without any problems, especially without a wait step.

I would like to thank you for taking the time to contact me so kindly.
Thank you so much!

1 Like
12

You are welcome.