Programmatically use Gallery image in other places within app?

[jeffminder]

Have a table with configuration info, where a photo is defined as one of the columns. Additional functionality was added to include a photo gallery later, using a different table. Image display order in the gallery is controlled by ‘Display Order”, as shown below. Would like to leverage the gallery photos to obtain the first photo in the list to be used in place of the current hard-coded photo in the configuration table. Tried adding a VC to the configuration table with the following expression:
“Select Photos[Photo] from Photos where Photos[Display Order] = 0.0”

Error: The expression is valid but its result type 'Yes/No' is not one of the expected types: Image

How else would you make that connection between the two tables to be able to pick an image if you know it’s displayOrder value? In my case, the value is known to be 0.0 (zero).

[dbaum · Accepted solution]

In your original post's screenshot of the Photos table, the Row ID column is the key and the Photo column is the label. It sounds like the new column you created in the Configuration table (appropriately) returns a value from the Photos table's Row ID column. If that new column is configured as a Ref type column, then generally across your app its literal text from the Row ID  column would not be displayed and the image from the Photo column would be displayed instead. If some part of that standard design is not applicable to your situation, then you can instead explicitly retrieve the image you're seeking. For example, revise your new column's App formula expression to retrieve not the Photos table's Row ID value but rather its Photo value:

LOOKUP(MINROW(“Photos”, "Display Order”), "Photos", "Row ID", "Photo")

[dbaum]

---

@jeffminder wrote:

Select Photos[Photo] from Photos where Photos[Display Order] = 0.0

---

I don't understand what you're trying to accomplish, but this is not an AppSheet expression. Review the syntax for SELECT and other functions.

[jeffminder]

Their syntax checker choked on every bad expression I've ever entered, but it took this one until I set the VC to type 'image'.   Before it was Y/N type.  Are the expressions not SQL like?   That's what I was going for with the expression.  Anyway... lets skip that for now.  I'll try to explain the question differently.  

App has a gallery controlled by the table posted originally.   Which ever picture has the lowest value for "display order", I would like to use that photo in another part of the app.  I tried to add a VC to the other table and somehow figure out which photo had the lowest display_order.   That is where I ran off the tracks.   Do you think this is possible to do?

[dbaum]

---

@jeffminder wrote:

Are the expressions not SQL like?

---

---

@dbaum wrote:

Review the syntax for SELECT and other functions.

---

----------

---

@jeffminder wrote:

Do you think this is possible to do?

---

Yes. MINROW() - AppSheet Help 

[jeffminder]

MINROW(“Photos”, "Display Order”)
Result:  M88dMSzuF71romLErbbBX0

Looking through records from PHOTOS, see the result above  contained within:  "Photos_Images/M88dMSzuF71romLErbbBX0.Photo.213728.png"

Tried to dereference as bewlo, but not getting it yet... will keep poking aroound.  

[Photos].[MINROW(“Photos”, "Display Order”)]

Thanks!

[dbaum]

---

@jeffminder wrote:

Looking through records from PHOTOS, see the result above  contained within:  "Photos_Images/M88dMSzuF71romLErbbBX0.Photo.213728.png"

---

I'm not sure what you mean by this comment if it's anything beyond a simple description of what you observed. If that value isn't from the row you intended, you may need to use the MINROW function's optional [select-row?] argument.

ICYMI:

• MINROW returns the key value of the identified row.
• If the column in your configuration table is a Ref type column, then that's exactly the value you want--i.e., a key value from the referenced table.
• If the referenced table has another column designated as its label column, that column's value is what will display in views based on the configuration table.
• If you need to dereference a value from a Ref type column, the notation can only be chained explicit column names (e.g., not with embedded expressions).
• If the context for an expression does not allow for directly dereferencing a Ref value, you can still use a row's value in one column to retrieve a different column's value from the row: LOOKUP.

[jeffminder]

Sorry, likely not using the right terms or misunderstanding valid answers.   The returned value from MINROW, selected the correct row, but I'm not sure what's required to get the full value I'm looking for to use elsewhere....

MINROW(“Photos”, "Display Order”)
Result: M88dMSzuF71romLErbbBX0

Below is the value from the Photos table I wanted.   The answer above gives part of that in the snippet below.

"Photos_Images/M88dMSzuF71romLErbbBX0.Photo.213728.png"

Is this a REF I need to de-reference, or otherwise convert it somehow to the full value to the PNG file?  I don't understand what is required with the value returned from MINROW, although it appears to be related to what I'm ultimately looking for.

From your last post, I tried LOOKUP, using:
LOOKUP(MINROW(“Photos”, "Display Order”), Photos, "Display Order", Photo)

This does return from the expression editor/test with no errors, and is of type 'image', but nothing is displayed.   Unclear if I'm on the wrong track or not using this correctly.

[dbaum · Accepted solution]

In your original post's screenshot of the Photos table, the Row ID column is the key and the Photo column is the label. It sounds like the new column you created in the Configuration table (appropriately) returns a value from the Photos table's Row ID column. If that new column is configured as a Ref type column, then generally across your app its literal text from the Row ID  column would not be displayed and the image from the Photo column would be displayed instead. If some part of that standard design is not applicable to your situation, then you can instead explicitly retrieve the image you're seeking. For example, revise your new column's App formula expression to retrieve not the Photos table's Row ID value but rather its Photo value:

LOOKUP(MINROW(“Photos”, "Display Order”), "Photos", "Row ID", "Photo")

[jeffminder]

This works!

Are you saying I should have done this a better way, or this is okay to do,  although its not the easiest way?

In any case.... Thank you!

[dbaum · Accepted solution]

It's unclear to me what you have done in terms of the configuration of your Configuration table and its columns. What I described (i.e., References between tables - AppSheet Help) is the standard, most robust way to implement what I'm inferring you're trying to accomplish, and in at least some circumstances can certainly be better than the alternate technique based on LOOKUP that I drafted for you. Nonetheless, that alternate technique is not necessarily problematic--especially if your dataset volume and other factors are of a size and complexity that any lag in app performance is not noticeable.

[jeffminder]

In retrospect, this was a roundabout way of making one photo from gallery the profile photo.  In my case, the lowest "display order" photo will be used as the profile photo.   Probably should have enabled a toggle of sorts on each photo to say it's the default/profile photo or not... when one is toggled, others are turned off.   Been looking at this so long, i'm just glad its done.  But it could be better...  haha

Appreciate your help reading through this and pointing me to a workable solution.