Re: Moving to SQL - Images Column question

Martina_Hawkins · 04-20-2022 03:23 AM

I am testing moving from MS SharePoint - Excel to SQL database.

I have a products table with an image column

Excel Products table was stored here:

Images Folder was also stored in same location

so my excel table looked like this

and app pulled in images

The Products table is now in SQL Database but images are not pulling in.

What do I need to change or move to get images to show as the data is now no longer in the same location as the image folder?

Suvrutt_Gurjar

You may find the below article and the highlighted excerpt useful.

It sounds you may need to add prefix of "/appsheet/data/Yourapp-123456/" to the Image path in the image column at the following setting.

This path is of course valid if all the relevant apps are at the path "/appsheet/data/" in your case.

https://help.appsheet.com/en/articles/5339970-images-or-files-that-do-not-load

You may also need some experimentation whether only defining the path in the Image column's settings serves the purpose or you may need to prefix the path in the database rows itself for the existing images.

I think new images will work just fine with the path defined in the image column in app editor.

Martina_Hawkins

@Suvrutt_Gurjar , I thought the above needed to be added for when I am adding images through the App. I have an Images folder with images already saved in the folder that need to be displayed in an images column. Do I need to create a folder /appsheet/data/My app../Product Images and move all my images here?

Suvrutt_Gurjar

May we know what is the path of the current images folder relative to the App folder meaning "/appsheet/data/Yourapp-123456/" path?

And were these images added initially through the app or the image folder was already existing? If so how you had earlier linked it to the spreadsheet based app? Meaning how the path was added to the back end table rows?

Martina_Hawkins

Images are in a Sharepoint folder

Default folder is

Images did not have to be in the default folder when using Excel. They were in the same folder as the Excel workbook but that is no longer in use as its in SQL database now.

Martina_Hawkins

Also, the Master Catalogue is a global table that is used in many apps. The Product Images folder holds thousands of images. I can't duplicate that folder in every apps appsheet/data folder

Suvrutt_Gurjar

Thank you. May we know where the image folder currently is relative to the new App path "appsheet / data/ SQLLewishamOrders-5229412"

Martina_Hawkins

It is in the above Sharepoint folder, unrelated to default folder

Suvrutt_Gurjar

Got it. I will test at my end in similar set up and revert. In the meantime someone else may have inputs for you based on discussion so far.

SkrOYC

Try copping your app folder from the table in which the images where, like this:
SHAREPOINT_SITE_ID_australexport.sharepoint.com_AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA_AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA//Apps//DATABASE.xlsx

And take all of the thing that's before your excel workbook and use it as a prefix for one of your rows and test.

So, if the image column has something like:
/Images/One/OneOne/image.png

Try:
SHAREPOINT_SITE_ID_australexport.sharepoint.com_AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA_AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA/Apps/Images/One/OneOne/image.png

or

SHAREPOINT_SITE_ID_australexport.sharepoint.com_AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA_AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA//Apps//Images//One//OneOne//image.png

I'm thinking out loud here but I use sharepoint and it seems it should work.

Also, check your SQL table on appsheet, and see which config is under this:

Martina_Hawkins

Hi @SkrOYC, so I tried the above but still no luck.

When data is in Sharepoint, Source Id is

so I took the above ... Master Product Catalogue/Product Images/ .....jpg and added it to 1st product in table database and got the below error

Also, I think the below is correct as this is where other images (unrelated to products) that are captured through the app are stored.

SkrOYC

@Martina_Hawkins wrote:

Do you see any options there aside from Default?

Also, if you try to match the App Default Folder with the one where the images are, does that display the images as if the default folder were the one with the excel on a "normal" scenario (not sql)?

I'm jus trying to help though, I don't have experience on this.

@Marc_Dillon You have any inputs?

Marc_Dillon

Is there a specific question or a summary, or are you expecting me to review this entire thread?

SkrOYC

Have you dealt with images after a migration from worksheet to sql?

Marc_Dillon

Yes.

The images stay in the same GDrive folder, and the "default app folder" has to point to the appropriate parent folder to make the relative paths stored in the data correct.

Marc_Dillon

In other words, the relative path is no longer relative to the table's sheet location, but relative to the "default app folder".

Martina_Hawkins

I'm getting very confused now!!

Below is not about the Product images but about the images users upload throught the app for other processes which is not working - images not getting stored.

In my App where the data is coming from Sharepoint (location in screenshot above) the default folder is

/appsheet/data/LewishamGatewayPh2Orders-5229412

All that is stored here are my templates in a folder called Contents

Images that users upload through the app are stored in folders of format tablename_Images in the same location as the spreadsheet, not the default folder.

Now that I have no spreadsheet so no path for spreadsheet, where does tablename_images get created?

@1minManager , @MultiTech , maybe you can help me out here?

Martina_Hawkins

This is the shocking reply I got from support:

Hello,

Thank you for contacting Google Cloud Support.

Unfortunately, we are unable to help you with your query since it's a design query and we are limited in providing support to technical issues. Would you please follow this community post or you can create a new one anytime and other AppSheet experts will answer it there. We try to use the community for these kinds of how-to questions so they're public and discoverable by people who may have the same question.

You could also check our partners from www.appsheet.com/partners through they are third parties with their own fee structures.

Best Regards,

Pratip G.

Koichi_Tsuji

Yeah, shocking indeed.......

Suvrutt_Gurjar

As per my understanding, the image file folder needs to be a subfolder of

Appsheet /Data/SQLLewishamOrders-5229412

which is the current path of the app.

You could possibly create a folder under the Appsheet /Data/SQLLewishamOrders-5229412 with the same name as your current image folder, so something like

Appsheet /Data/SQLLewishamOrders-5229412 / Product Images/.......

and test with one or two images.

Martina_Hawkins

I have 2 issues here:

1. Product images. These are already stored in a folder in SharePoint and need to be used by several Apps so cannot be stored in appdata/data default folder of one app.

2. Images that are uploded by the user which will be stored in the appdata/data/tablename_images folder.

I didn't change anything but this is sort of working but something strange going on.

I have 2 tables PODs and Revised POD's.

I uploaded an image to POD and it is showing in the App but not in the Appdata.... folder

I uploaded 2 photos to Revised POD, both showing in the App but only one in appdata...

Marc_Dillon

Ok, I've reviewed this whole thread now (well, thoroughly skimmed, at least).

Here are the facts that I know:

For a table that is stored in a cloud service that can also store images (i.e. GDrive, Sharepoint, etc, but not SQL), the value stored in the image/file column is the path relative to the location of the spreadsheet file.
For a table that is stored separately from the image store (i.e. SQL), the value stored in the image/file column is the path relative to the app's "default app folder" (from Info>Properties).
The "Image/File folder path" setting in an image/file column's configuration tells the app where to save the file, not where to read the file from. The text value saved in the data must still be a path relative to the default-folder/spreadsheet, regardless of what is entered into that setting.
Apps cannot load any files that are above the "reference point" (meaning either the "default app folder", or the spreadsheet file) in the file system that you're using.
GDrive allows you to make shortcuts to other folders, even ones that are above them.
1. This is how I've been able to show images from one app's folder, in another app, without having to mess with anything else (like the "default app folder", or the column's folder path setting, or the actual value saved in the column).
2. For example if one app's folder is "appsheet/a", and the image value is "files/pic.png", and I want to share it in the second app, whose folder is "appsheet/b", I would simply make a shortcut to the "files" folder, and place that shortcut in the "appsheet/b" folder.
3. I don't use sharepoint, so I have no idea if it has an equivalent shortcut feature.

The shortcut feature is useful and easy, but I'm not convinced it is the best setup here (especially if it is not available on other image store platforms). I've actually been meaning to get into this topic with one of my clients, but haven't put much time into it yet, but here are some initial thoughts.

Maybe all apps should have their "default app folder" set to the same high-level folder, like "appsheet".
Then for all image/file columns, appropriately set the folder path setting so that files are saved in an organized fashion.

Martina_Hawkins

Thanks @Marc_Dillon for your reply. The shortcut feature cannot be used in SharePoint as far as I know. Saving images in each app to their respective appsheet/data... folder is not a problem as these images are unique to each App. Its just the already existing Product Images in the Product Images folder which is the issue. I am going to look into maybe moving these all into the database. Found this article but don't know if its actually available until I sign up to an Enterprise plan but I need to make sure all is working before I can commit to an expensive plan

https://help.appsheet.com/en/articles/4474114-understanding-cloud-object-stores

Martina_Hawkins

had a look in database at Image column

Image is POD Page 1

lines 2 and 3 were taken on my ipad and are saving to correct place.

line 1 was taken on another users ipad. It is showing in the app but I have no idea where the image is actually saved

Marc_Dillon

That's a 64-bit encoding of the image, that text is the image, it isn't saved anywhere else.

How did you get it to do that?

Martina_Hawkins

I don't know. The user followed the same process as I did on his ipad. Its 9:30 PM here now so I'll have to wait until morning to get him to try it again

Martina_Hawkins

@Marc_Dillon , I'm getting somewhere now with your suggestion of

Maybe all apps should have their "default app folder" set to the same high-level folder, like "appsheet".
Then for all image/file columns, appropriately set the folder path setting so that files are saved in an organized fashion.

So I created Product Images folder under Appsheet and moved some images in here and they can now be seen in multiple apps. The Default folder in properties is set at /appsheet

this next bit is confusing

I then created folders to hold the images that will be taken using the app

I changed the Image/File folder path in the image column of the table to point to created folder

I uploaded an image

- it appeared in the app

- it appeared in the database as

- it did not appear in saved folder and then it disappeared from app - yellow triangle

I uploaded another image to another record

- it appeared in app and is still there

- it did not appear in folder

- it is in database like this

don't know what is going on here

Marc_Dillon

I would remove the first slash from the image path.

Martina_Hawkins

@Marc_Dillon , tried that and tried removing /appsheet.

Don't know why this is happening. Sometimes it saves to OneDrive and sometimes it doesn't .

Has anyone else seen this happen?

Koichi_Tsuji

Is it possible your disk (one drive) availale space is almost full?

Martina_Hawkins

Hi @Koichi_Tsuji , thought it might be something to do with One Drive but storage seems ok.

1023.97GB free of 1024.00GB. I set up a new MS account to test the Database move.

Koichi_Tsuji

Thanks. Ample spaces.....

In case the file storage is full when the app tries to save the file, then appsheet save the files as base64 in order to retain the files, instead of lose. In your case, the data storage should have full capacity to accomodate your jpeg files....

Martina_Hawkins

Contacted Support again. Message was seen an hour ago by Pratip, he is active but has sent no reply.

Koichi_Tsuji

I'm afraid you will be guided to ask your question again in the community!

Martina_Hawkins

So I tested again, no changes made to App.

Image appeared in app

Saved to OneDrive

Shows Onedrive path in database

Then !!! image disappeared from app

Synced the app and it appeared again

All very sporadic. Couldn't put this into production

Martina_Hawkins

Have run into another problem. The reason for testing out moving to a database was because we were getting the error message - Too many resquests... I thought and was led to believe that this was because Excel spreadsheet could not cope with the amount of data.

Still getting the error message. There are no spreadsheets and there is one user!

At my wits end here

Koichi_Tsuji

I never used One Drive as data storage for image file, Appsheet, so no past experiences. I never experienced before with my own apps, where we set the data storage to Google Drive.

Just out of my guess works, your problem is coming from onedrive as data storage for files.

I can not offer any more suggestion, but hope AppSheet support would give you a help if they are active to help you out, rather than dump the problem to the commuinty to solve.

Marc_Dillon

That error message talks about accessing a template file. That would still be on your Sharepoint even after you moved all data to SQL. Also, I believe the images are stored in 64bit as a backup when the cloud storage is not accessible when the record was saved. There's obviously still an issue with the connection between Appsheet and Sharepoint. Have you contacted Sharepoint support at all? Perhaps they can shed some light on why you're running into these (rate-limiting ?) issues?

SkrOYC

@Marc_Dillon wrote:
I believe the images are stored in 64bit as a backup when the cloud storage is not accessible when the record was saved. There's obviously still an issue with the connection between Appsheet and Sharepoint.

👍🏽

SkrOYC

@Martina_Hawkins Your default app folder is a sharepoint one, like this?