I want to control who can update row in a Table called TimesheetCompleted via a โAre Updates Allowed?โ formula. Heres the formula Iโm using:
IFS(
IN(โManagerโ,ANY(Select(Staff[Type],[Login Email]=UserEmail()))),โADDS_AND_UPDATESโ,
IN(โAdminโ,ANY(Select(Staff[Type],[Login Email]=UserEmail()))),โADDS_AND_UPDATESโ,
AND(
OR(
IN(โSubcontractorโ,ANY(Select(Staff[Type],[Login Email]=UserEmail()))),
IN(โApprenticeโ,ANY(Select(Staff[Type],[Login Email]=UserEmail()))),
IN(โEmployeeโ,ANY(Select(Staff[Type],[Login Email]=UserEmail())))
),
[TimeSheet].[TimesheetUser]=UserEmail()
),โADDS_AND_UPDATESโ
,
AND(
OR(
IN(โSubcontractorโ,ANY(Select(Staff[Type],[Login Email]=UserEmail()))),
IN(โApprenticeโ,ANY(Select(Staff[Type],[Login Email]=UserEmail()))),
IN(โEmployeeโ,ANY(Select(Staff[Type],[Login Email]=UserEmail())))
),
[TimeSheet].[TimesheetUser]<>UserEmail()
),โADDS_ONLYโ
)
So admins & managers have full access. Employees, Aprentices & SubContractors can add but only edit their records. Timesheet is the parent table. Formula checks out fine when I enter it, but fails after Save & Verify with โcannot find column Timesheetโ. Is this not possible? Iโm guessing the issue maybe you arenโt allowed to reference data in the same or parent table? I know I can do this be controlling each column in the table, or with a sliceโฆ
Any ideas
The Are updates allowed? expression is not evaluated against each row; itโs table-wide. Your attempt to access โthis rowโsโ column value (e.g., [Timesheet]
) is therefore invalid. This is in contrast to a security filterโs expression, which is evaluated against each row separately.
To do what it appears youโre trying to do, youโll probably want to use the Only if this condition is true expression of the Edit system-generated action for the table, or something along those lines. Alternatively, create two separate slices, one for managers and admins that include all rows and allows adds and updates, and one for the current staffer that includes only their own rows and also allows both adds and updates, then make only the appropriate slice visible to the user.
Hi @Steve
What Iโm to do is to control the โAre Updates Allowed?โ using a TRUE or FALSE calcuation of data from a totally seperate table. I already appreciate you canโt do this on the same table. Is this a something Appsheet has hard-coded in that we cannot do?
IFS(
IN(...),โADDS_AND_UPDATESโ,
IN(...),โADDS_AND_UPDATESโ,
AND(
OR(
IN(...),
IN(...),
IN(...)
),
[TimeSheet].[TimesheetUser]=UserEmail()
),โADDS_AND_UPDATESโ
...
In the above, what row contains the TimeSheet column youโre dereferencing with [TimeSheet].[TimesheetUser]
?
Hi Simon,
Did you ever find an answer to this issue?
Thanks!
I think I gave up on it and did it another wayโฆ But its so long ago I canโt remember. Feel free to post here your issue and iโll try and help
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |