Form: submit only if table query returns 0 results

FHLPC · 04-19-2021 08:05 PM

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)

Suvrutt_Gurjar

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