In theory this is simple, but in practice, is not so obvious without knowing the platform.
I spent few hours researching this without success.
I have a form linked to a Google Spreadsheet table. Users can enter a new report for the given day/shift.
Table and values
REPORT_ID = UNIQUEID()
DATE = TODAY()
SHIFT = ENUM (there are only 2 shifts AM, PM)
Our team can create a daily report only if it doesnโt exist. If the report already exist for the given DATE and SHIFT.
When the user creates a new report pressing the ADD button, Iโd like the app to query the table, and:
if the report was already created for the selected DAY and SHIFT, then display a message: Report already created
if the report doesnโt exist, then message: New report created.
How do I prevent users from creating duplicated reports? Every shift can only have 1 report.
Thanks ahead for the help
(I could create a combined column key DATE & SHIFT, but my intent is to validate that no other reports have the same DATE & SHIFT. In the future, we might allow the creation of multiple reports per shift)
Presume DATE=TODAY() , you mean TODAY() is initial value and not app formula. Or it will change on every edit of the record.
If so, please try folliwng
1)Concatenate the columns [Date] and [Shift] such as say CONCATENATE([Date],"-",[Shift]) This column is called say [DateShift]
2)In the valid if of the REPORT_ID , please have an expression something like below
ISBLANK( FILTER( "Table Name", ([_THISROW].[ShiftDate]= [ShiftDate]) ) - LIST([_THISROW]))
This expression is based on the article below. The article has some very useful suggestions on using various list expressions apart from preventing duplicates
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |