I have an App with Images. Each image has an associated ENUM column e.g. [Photo 1 Status] so that the admin can set each image to Unchecked, Rejected or Verified. Unchecked is the initial value. So this works when the row is created. But if I take a photo, change [Photo 1 Status] to Rejected, then change the image, [Photo 1 Status] does not reset back to Unchecked. Even though the row has been edited. Iโve tried using ISBLANK([Photo 1]) as the Reset on Edit rule, but it doesnโt change either. Am I doing something wrong?
Solved! Go to Solution.
For Reset on edit?:
([Photo 1] <> LOOKUP([_THISROW], "Table", "KeyColumn", "Photo 1"))
This asks the question: is the in-form Photo 1 column value ([Photo 1]
) different than (<>
) the Photo 1 column value of the row in the Table table with the KeyColumn column value of the in-form rowโs key column value (LOOKUP([_THISROW], ...)
)?
LOOKUP(), SELECT(), FILTER(), MAXROW(), and MINROW() access the table (or slice) directly and ignore whatever is in the form. Because of this, you can use them to compare a columnโs in-form value to its saved value.
For Reset on edit?:
([Photo 1] <> LOOKUP([_THISROW], "Table", "KeyColumn", "Photo 1"))
This asks the question: is the in-form Photo 1 column value ([Photo 1]
) different than (<>
) the Photo 1 column value of the row in the Table table with the KeyColumn column value of the in-form rowโs key column value (LOOKUP([_THISROW], ...)
)?
LOOKUP(), SELECT(), FILTER(), MAXROW(), and MINROW() access the table (or slice) directly and ignore whatever is in the form. Because of this, you can use them to compare a columnโs in-form value to its saved value.
That doesnโt seem to work as expected. Every time I go into the record it resets the value straight away
Testing process
1 = changed photo. synced. Confirmed changed in spreadsheet
2 = Checked approval from Unapproved to Approved. Synced. Confirmed changed in spreadsheet
3 = Edited record, approval has changed back from Approved to Unapproved
Changed the formula to look at Photo 1โs timestamp column instead - same issue
How about:
([Photo 1] <> LOOKUP([_THISROW].[KeyColumn], โTableโ, โKeyColumnโ, โPhoto 1โ))
Tried ([Photo 1] <> LOOKUP([_THISROW].[PhotoID], โPhotoโ, โPhotoIDโ, โPhoto 1โ)) and the symptoms were it wouldnโt change even if the photo was deleted. However, Iโm much more familiar with Select() expressions so I changed it to [Photo 1] <> ANY(Select(Photo[Photo 1],[PhotoID]=[_ThisRow].[PhotoID]))
and this works fine
Thanks @Steve
Thatโsโฆunexpectedโฆ But Iโm glad it works!
Hi @Steve. Do you know if this Select behaviour (of being able to compare the current form data with the same row of data in the spreadsheet) works the same if the app works offline and the data isnโt in the spreadsheet but waiting to be cached?
I donโt know for sure, as Iโve not tested it specifically. My expectation, though, is that SELECT() (and related) will always refer to the data in the table, whether written to the spreadsheet or still waiting to be written. Put another way, I donโt think SELECT() cares whether the data is in the spreadsheet yet or not: SELECT() is looking at the appโs table, not the spreadsheet.
User | Count |
---|---|
42 | |
34 | |
26 | |
23 | |
15 |