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?
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(), 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
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
([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
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.