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

[Peppe]

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]

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]

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_Image...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]

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

[Suvrutt_Gurjar]

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]

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]

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]

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]

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]

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 · Accepted solution]

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]

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]

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]

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]

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]

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]

---

@Peppe wrote:

when I add a new image it doesn't create the URL.

---

---

@Suvrutt_Gurjar wrote:

However with physical columns the expression will calculate for the existing records only when the record is edited.

---

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.