My Labour table has a calculated field called OrderId with a formula value of [Day Sheet Id].[OrderId] linking it back to its grandparent. It is a Show field, Ref type, Required, with a ValidIf constraint of “isnotblank([Day Sheet Id].[OrderId])”.
To further constrain it, I have a calculated field called [On Day Sheet] with a formula of [Day Sheet Id].[Day Sheet Num] linking it back to its parent. That is a Show field, Ref type with a ValidIf clause of ISNOTBLANK([Day Sheet Id]).
In turn, [Day Sheet Id] is the Required, Ref parent field, that Is Part Of the source table with a ValidIf constraint of OrderBy(Select(Daysheets[DaySheetId],[Day Sheet Finished]=false),[Order]). However, [Day Sheet Id] is not visible because I don’t want staff to be able to access the parent record.
My problem is that, even with all these Require and ValidIf constraints in place, I still occasionally find a Labour record being added without a corresponding Day Sheet Id or OrderId. I don’t know what other constraints to implement to stop this from happening and would welcome any suggestions.
So I have run into issues with this as well. For me the issue came from when creating a child record inside of the form view of the parent record. and linking all the items in the form save for cells in the parent. The valid if constraints and the the formula are good but should also be paired with the formula in the initial value as well. A way I solved this was to add another column that would ref to the parent table based on my selection from the ref column make it visible in the form view and see that it populates and have this as a back up using a stacked IF() statement. Making the route to get your ref value have a backup route based on ISBLANK().
SO Virtual column A has a value of [Day Sheet Id].[Day Sheet Num]
and Virtual Column B has a value of IFS(ISBLANK([Column A]), INDEX(SELECT(Day Sheet[Day Sheet Num], [Day Sheet Id] = [_THISROW].[Day Sheet Id],1)))).
Then your formula for OrderId should be IF(ISBLANK([Orderid], IF(ISNOTBLANK([Column A]), [Column A], [Column B])). This way you have two routes to the same answer if these turn up blank. Then there could be missing values in your Order ID table you are pulling from.
There is also the ability to disable the view ref action for the parent. Under the actions for this table. Then you can make the REF column visible and not worry about hiding it. This makes it where the user cant click to view the parent.
Very clever options, I think your second one is pretty quick and simple and I will implement.
In this case, I found out a little more info from one of the offending users and discovered he was going to the Grandparent record, and adding a record to the related Grandchildren table. That’s allowing him to add records to the labour table that aren’t linked to the Day Sheet and hence breaking the links. With your suggestion in mind, I’ll also explore whether I can modify the Add Action to prevent records being added without a parent and grandparent .