Validation for mandating the data entry of one category prior to other category Shift A

[Neeraj_Malik]

I have a situation where there are activities being carried out in two shifts A & B. I have included following validation for avoiding the duplication of data entry for the respective shift.

OR(
  ISBLANK([Date]),
  ISBLANK([Shift]),
  ISBLANK(
    FILTER(
      "P1A",
      AND(
        ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER]),
        ([Date] = [_THISROW].[Date]),
        ([Shift] = [_THISROW].[Shift])
      )
    )
  )
)

Now I also need to ensure that data entry of Shift A to be entered before Shift B, meaning if the data for Shift A has not been entered then the data entry for Shift B should not be allowed.

Looking for help on how can I modify the above validation to achieve the desired result of entering Shift A data prior to Shift B data. And how the same can be modified further if there are more than two shifts.

[MultiTech]

Just FYI:

Neeraj_Malik:

([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER]),

I take it from this that the P1A table uses the [_RowNumber] as the key

• this is not advisable in most instances.

---

Neeraj_Malik:

Looking for help on how can I modify the above validation to achieve the desired result of entering Shift A data prior to Shift B data.

I would suggest that you actually create a table in your app that will hold information about the various shifts required. Not a table to hold information about the actual shifts people are working, but information about the shifts themselves.

• A very common oversight when building a data source for an AppSheet app, is the lack of supporting tables - which don’t record information about events employees are doing, but hold information that the system can use.

---

If you created a Shifts table, you could include a column that holds the Key value of the prior shift.
Then, inside the table where you record the actual shifts from employees, include a reference link to that Shifts table,

• and from that reference you can de-reference the previous shift - and validate that it has been completed.

Creating a system like this would allow you to have X number of shifts, each one validating that the previous one was complete before the next was allowed to begin.

[Neeraj_Malik]

Another table can be created but still not clear how it would help. Would appreciate if you can explain the reference & dereference thing with the help of an example.

[MultiTech]

@Neeraj_Malik Think about the details in this new table as the settings for things that people are recording, the requirements for each shift that’s being created.

Inside these “settings” records, you can create any number of things you might want to enforce on something. In your case, you only need to know what came BEFORE.

This is kinda a complicated topic, so I made a video showing a similar setup I made previously; the video got long (4 min), but I hope you can glean some insight from it.