Gallery showing the wrong images from a SQL Server column

[nybjkj]

I have a simple Azure SQL table that includes an image column.  A gallery is bound to the table, and one of the gallery template items is a media control, bound to the image column.  This always displays the correct image when the column is popultaed, but when null, the gallery doesn't always display a blank image - it often (especially after scrolling or refreshing) displays images from other table rows - seemingly randomly.  I even tried changing the Media.Image value to be..

If(IsBlank(ThisItem.Image),Blank(),ThisItem.Image) 

... in a vain attempt to 'force the issue', but it makes no difference - it never considers the value of ThisItem.Image to be Null, even though I can clearly see the nulls in the database.

Any ideas or help gratefully received!

[wyotim]

I was encountering the same issue and had to resort to a workaround. An interesting thing I found is that there doesn't seem to be a good way to test for whether there is an image or not because every row of the image column returns a value whether it has an image or not. You can see this value by placing ThisItem.Image in a text label in your gallery.

What I had to do was put a boolean (bit) field in my table (which I titled HasPicture), defaulted to false (0), then check for that value instead of checking for a picture. This worked out okay in my case but I can't imagine it is the best way to handle things in all cases. When a user submits a picture you could then update HasPicture to true. 

As I said, this worked okay for me but, in general, it seems there has to be a more elegant way!

@timl or @ericonline do either of you have any experience with this that you could share?

[nybjkj]

Thanks wyotim - yes, good idea, and easier for me because in fact I am already using a view rather than a table (I simplified my original post).

Would still be interested in what is happening here 'under the bonnet' if anyone knows...

Thanks for your speedy response.

[v-xida-msft]

Hi @nybjkj ,

Do you mean that you could not display NULL image within your Gallery?

Based on the issue that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. Please consider modify your formula as below:

Set the Image property of the Image control within Gallery to following:

If(IsBlank(ThisItem.Image) || ThisItem.Image = "", SampleImage, ThisItem.Image)

If the issue still exists, please consider add a varchar(max) type column to store the image data in your SQL Table, instead of the Image type column, then check if the issue is solved.

More details about storing image data using varchar(max) column in SQL Table, please check the following article:

https://www.youtube.com/watch?v=QnDD7jleHME

Best egards,

[timl]

@nybjkj @wyotim 

That problem sounds really horrible. Surprisingly (or luckily), I've never come across this before.

Unfortunately, I haven't been able to recreate this issue despite scrolling, refreshing, sorting, or searching the records in a gallery as much as possible.

Looking at this a bit further, I notice that adding ThisItem.Image to a text label in the gallery returns in an image location in the format:

"appres://datasources/[dbo].[TableName]/table/[dbo].[TableName]/rows/<PrimaryKeyValue>/inline/PhotoData"

Out of interest, let's suppose you add a label and set the Text property to ThisItem.Image. When the problem occurs, does the PrimaryKeyValue value in the label match the actual primary key value for the record, or can you detect some deviation in the values? If you were to then add an Image control in a separate screen and set the image to the location that you see in the label, do you correctly see an empty image, or do you see some random image?

Thanks @wyotim for the workaround. I'm sure it'll help anyone suffering from the same problem.

[ericonline]

I have not encountered this issue.

[wyotim]

@timl, many thanks for the reply. I did some checking and found some interesting results. For starters, when using a data table (be it directly or through a collection) the number you referred to as PrimaryKeyValue is, in fact, the primary key. When using a view however it is NOT a primary key of any kind. This is was very apparent as the first item I used to check had a 0 and I can guarantee that there is no 0 in my PKs.  Strangely though, that number didn't seem to have much rhyme or reason to it. The numbers don't coincide with any PKs, they aren't in order of the creation of each row, and they aren't in the order that the view is collected or sorted by in PowerApps. 

Also, the image issues I have seen aren't just for blank images but also occasional swapping of existing images. So there's that. 

Regarding the test of adding an image control and pasting the text in, it did reproduce the image it was supposed to in my testing and I was able to change images by changing that reference number, getting the correct image each time.

All that to say this seems like a view-specific issue.

@v-xida-msft, thanks for the video link. Paul did a follow-up video to that one which was also quite interesting: 

https://youtu.be/qX3i8456YwU

And thanks for stopping in @ericonline!

[timl · Accepted solution]

That's some great investigation there @wyotim! Thanks for sharing your results.

It's interesting how the unqiue identifiers are random when you use a view. I reckon that could be the cause of the bug.  Perhaps PowerApps is re-allocating those row ids to ones that have been used before when you scroll, refresh, or carry out some other action.

It's very useful to know that it applies to views only.

[nybjkj]

Thanks @wyotim  and @timl  - that's excellent anaysis.  Indeed, as my second post mentioned, I am using views, so the 'strange' primary key values would explain the behaviour.  For now I have implemented @wyotim's suggestion of a 'hasImage' boolean - only took a few minutes and is working well.

@v-xida-msft thank you for the videos, but I am not going to change from an image to a varchar(max) column, as this results in the App storing the 'appres://' reference, and I want the database to hold the images locally (in binary form).