Table "Are Updates Allowed?" formula

[1minManager]

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

[Steve]

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.

[1minManager]

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?

[Steve]

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]?

[Jason_Stetler]

Hi Simon,

Did you ever find an answer to this issue?

Thanks!

[1minManager]

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