Delete the final part of each url from a list retrieved with SELECT

Peppe · April 19, 2024, 3:33pm

Hello everyone,
I have a problem that I really can’t solve and I don’t even know if it’s possible since I’m a beginner.
Using an action I would like to group the URL of the photo of table1 with the URLs of the photos of table2 (the photos of table2 are variable), the URLs must end with the extension and separated by “|”.

I attach the formula I tried to write

In the first url I managed to eliminate the part after the .jpg extension but in the URLs of the AltriIMG table I really don’t know how to do it.
Thanks if you want to help me

Suvrutt_Gurjar · April 19, 2024, 4:04pm

If you could share the table columns and data ( of course pseudo data or test data) and what are you trying to achieve as end result from the input data , I think the community could help better.

Peppe · April 19, 2024, 4:53pm

Hello and thanks for the reply. I hope I can explain what I want to achieve.
I have two tables and in both there is an image column with which I take photos.
I need to concatenate the URLs of these images by separating them with “|”.
The “Warehouse” table will always capture a single image while the “AltreIMG” table will capture zero or 1 or 2 and so on.
With the previous formula I get this:

https://www.appsheet.com/image/getimageurl?appName=xxxx&tableName=Magazzino&fileName=Magazzino_Images%2F53600079.Foto1.155852.jpg|https://www.appsheet.com/image/getimageurl?appName=xxxx&tableName=AltreIMG&fileName= AltriIMG_Images%2Ff4f5e84c.Images.160623.jpg**&appVersion=xxxx&signature=xxxx**|https://www.appshe…

I would like to remove the bold part from all URLs.

The table is made up of 3 columns and is that of the screenshot ID_MAG is Ref with the main table where the main photo is [Foto1]

The formula is inserted in an action and should allow you to group all the photo URLs of the two tables for each ID_MAG. The result will be inserted into a column of another table. However, the URLs must end with “.jpg”

Suvrutt_Gurjar · April 19, 2024, 6:06pm

May we know the purpose of concatenating URLs in this way?

Suvrutt_Gurjar · April 19, 2024, 11:30pm

Based on current understanding, you could first remove the part after .jpg in each URL and then concatenate them. Something like below

CONCATENATE(

LEFT([Foto1], FIND(“.jpg”, [Foto1])+3),

IFS(IN(COUNT(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG])), LIST(1 , 2)),

(" | ",

LEFT( INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 1),

(FIND(“.jpg” , INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]),1))+3)

)

),

IFS(COUNT(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]))=2,

" | ",

LEFT( INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 2),

(FIND(“.jpg” , INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]),2))+3)

)

AppSheet does not have a native looping mechanism, so I believe you will need to add as many sub statements as many images in the AltreIMG table.

Note: I could not test the above expressions, so there could be minor typos or missing/additional parenthesis but hope you get the idea.

Peppe · April 20, 2024, 6:44am

The app aims to photograph objects and collect other data and then produce a csv that will be uploaded to a platform. The platform asks that URLs be written in this way.
So far I’ve done it with a formula in the spreadsheet but it’s getting painfully slow

Peppe · April 20, 2024, 6:59am

Thanks for your help, I really appreciate it.
From what you tell me I should therefore decide what the maximum number of photos will be and then repeat the instruction as many times.
I’m going to do some tests

Peppe · April 20, 2024, 8:51am

I wasn’t able to use your formula but modifying it a bit worked. It works up to 3 images, Foto1 and 2 in AltreIMG but when I tried to add another condition to simulate the case of 3 images in AltreIMG it always returns the URLs of 2 images.
I’m very confused

CONCATENATE(
LEFT([Foto1], FIND(“.jpg”, [Foto1]) + 3),
IF(
IN(
COUNT(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG])),
LIST(1 , 2)
),
" | " & LEFT(
INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 1),
(FIND(“.jpg”, INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 1)) + 3)
),
“”
),
IF(
COUNT(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG])) = 2,
" | " & LEFT(
INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 2),
(FIND(“.jpg”, INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 2)) + 3)
),
“”
)
)

Suvrutt_Gurjar · April 20, 2024, 11:57am

Please share in text what expression you are using now for three images in the AltreIMG table. You seem to be almost using what I suggested with minor tweaks. The above expression you shared is for two images in the AltreIMG table.

Peppe · April 20, 2024, 2:18pm

This is the formula I used in the case of 3 images in altreIMG. As I told you, it only gives me back the first 2.
Maybe I need to think of a different approach, I don’t know.

CONCATENATE(
LEFT([Foto1], FIND(“.jpg”, [Foto1]) + 3),
IF(
IN(
COUNT(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG])),
LIST(1 , 2,3)
),
" | " & LEFT(
INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 1),
(FIND(“.jpg”, INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 1)) + 3)
),
“”
),
IF(
COUNT(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG])) = 2,
" | " & LEFT(
INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 2),
(FIND(“.jpg”, INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 2)) + 3)
),
“”
),
IF(
COUNT(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG])) = 3,
" | " & LEFT(
INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 3),
(FIND(“.jpg”, INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 3)) + 3)
),
“”
)
)

Suvrutt_Gurjar · April 21, 2024, 12:53pm

Please try below. Essentially use IN() function on image 2 as well. Then 2nd image will render when 3 images are there. You had missed adding IN() for the second image , so when 3 images are there it was failing.

Note: I edited the expression on mobile phone. So there could be some typos.

CONCATENATE(
LEFT([Foto1], FIND(“.jpg”, [Foto1]) + 3),
IF(
IN(
COUNT(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG])),
LIST(1 , 2,3)
),
" | " & LEFT(
INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 1),
(FIND(“.jpg”, INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 1)) + 3)
),
“”
),
IF (IN(
COUNT(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG])),
LIST(2, 3)
),
" | " & LEFT(
INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 2),
(FIND(“.jpg”, INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 2)) + 3)
),
“”
),
IF(
COUNT(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG])) = 3,
" | " & LEFT(
INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 3),
(FIND(“.jpg”, INDEX(SELECT(AltreIMG[Images], [ID_MAG]=[_THISROW].[ID_MAG]), 3)) + 3)
),
“”
)
)

Peppe · April 21, 2024, 1:46pm

Great, worked perfectly. If my other images were to be for example 10, it becomes very cumbersome to configure the formula. At the moment I can consider myself satisfied.
I thank you infinitely

Suvrutt_Gurjar · April 21, 2024, 4:31pm

You are welcome. I think it can be simplified with ease as follows.

Please add a column called say [Image_URL] in the AltreIMG table with an expression something like below

CONCATENATE( " | ",

LEFT([Image], FIND(“.jpg”, [Image]) + 3))

Now the overall expression can be much simpler

CONCATENATE(
LEFT([Foto1], FIND(“.jpg”, [Foto1]) + 3),

SELECT(AltreIMG[Image_URL], [ID_MAG]=[_THISROW].[ID_MAG])

)

I believe this will work for any number of images. Again , I might have a few typos because I typed it on a mobile phone.

But I hope you got the idea.

Peppe · April 22, 2024, 9:40am

I tried to do it but maybe I didn’t understand what to do exactly.

I added a new column in the spreadsheet;
Should the formula be inserted in the spreadsheet or in the app? I tried both but failed.

I’m a little good with formulas because I have a background with Excel but with the rest I’m still learning.

Suvrutt_Gurjar · April 22, 2024, 10:31am

You can try adding virtual column as well for testing. With physical column , you will need to add a column in spreadsheet, regenerate the table in the app editor, and add the expression in the app formula setting of the app. However with physical columns the expression will calculate for the existing records only when the record is edited.

Columns: The Essentials - AppSheet Help

Manage the columns in a table - AppSheet Help

Use virtual columns - AppSheet Help

Peppe · April 23, 2024, 8:55am

I have to work on it a little. On existing images, simply enter edit and save, it creates the URL and concatenates it as I wish; when I add a new image it doesn’t create the URL.

Suvrutt_Gurjar · April 23, 2024, 8:59am

Please try the URL column as a virtual column. Also you mentioned you are using an action. The expression should work with action to update , if action is setup properly as an event action. So when a new image is added, if the action fires as an event action, the new image should add to the concatenated URL.

Topic		Replies	Views
I have been trying to create URL in the Google sheet for the image uploaded in app from form view it is not concatenating the image column please help here AppSheet Q&A expressions	4	18	November 30, 2021
Selecting all images that start with certain value AppSheet Q&A expressions	5	11	March 30, 2020
Show image gallery AppSheet Q&A expressions	2	5	October 13, 2019

Delete the final part of each url from a list retrieved with SELECT

AI Suggested topics