Allow traveler to see their data and budget holders to see their users data

CANA-IT · January 22, 2025, 3:19pm

I have a Request System using an APDB.
Travelers Request Travel for a project, then the Budget Holder for the project goes in an Reviews and approves the requests.
How do I allow the Traveler to see their requests “only” but the Budget holder to be able to see their requests and anyone requesting for their project(s)?
I have watched, over and over and over, QCrews Timecard video (https://www.youtube.com/watch?v=2Z2qUTtxNFA) I cannot get the IFS to checkout green. I always get parse errors.
Key App Tables:
TravelRequests: key columns are - Traveler, Traveler Email, Project Name (which is Project Number)
Projects: key columns are - Project Number, Budget Holder (which is an email), Budget Holders Name
Employees: key columns are - Account (which is an email), Role (User, BudgetHolder, Admin, SuperAdmin), Department

Steve · January 22, 2025, 4:01pm

Commonly, this might be handled by a security filter on the TravelRequests table. If the Project Name column of that table is a Ref to Projects, this might work:

OR(
  (USEREMAIL() = [Traveler Email]),
  (USEREMAIL() = [Project Name].[Budget Holder])
)

If it is not a Ref, try this instead:

OR(
  (USEREMAIL() = [Traveler Email]),
  (
    USEREMAIL()
    = LOOKUP(
      [_THISROW].[Project Name],
      "Projects",
      "Project Number",
      "Budget Holder"
    )
  )
)

CANA-IT · January 22, 2025, 4:24pm

Let me try that… Thank you.

CANA-IT · January 22, 2025, 4:37pm

The First one Worked, I used the Projects Ref I had setup “Dept Code Long Name”
Next question. How can I add the ability for “Admin” to see all Travel Requests and keep that Security Filter in place?

Steve · January 22, 2025, 7:03pm

Like this:

OR(
  ("Admin" = USEREROLE()),
  (USEREMAIL() = [Traveler Email]),
  (USEREMAIL() = [Project Name].[Budget Holder])
)

CANA-IT · January 22, 2025, 7:26pm

How would that look with this:

OR(
(USEREMAIL() = [Traveler Email]),
(
USEREMAIL()
= LOOKUP(
[_THISROW].[Dept Code Short Name],
“05 Projects”,
“Project Short Name”,
“Budget Holder”
)
)
)

Steve · January 22, 2025, 7:36pm

Like this:

OR(
  (USEREMAIL() = [Traveler Email]),
  (
    USEREMAIL()
    = LOOKUP(
      [_THISROW].[Dept Code Short Name],
      "05 Projects",
      "Project Short Name",
      "Budget Holder"
    )
  ),
  (
    "Admin"
    = LOOKUP(
      USEREMAIL(),
      "Employees",
      "Account",
      "Role"
    )
  )
)

CANA-IT · January 22, 2025, 9:04pm

So, too keep bugging you.. I have tried on my own. I usually only ask as a last resort.
We have The User seeing their info.
We have the Budget holder seeing their info and the Budgets info
We have have the Admin seeing all…

I tried to do another lookup so that the BizOps Role could go in and just see “Awaiting BizOps Review” Requests… It passed the Check but Appsheet Errored it out. Big and Ugly!..
The filter expression cannot reference the table being filtered.

After SuperAdmin I added:

(
“BizOps”
= LOOKUP(
USEREMAIL(),
“08 Employees”,
“CANA Account”,
“Role”
)
),
(
“Awaiting BizOps Review”
= LOOKUP(
‘Awaiting BizOps Review’,
“02 Expense Reports”,
“Expense Status”,
“Expense Status”
)
)
)

Any Idea what I did wrong?

Steve · January 23, 2025, 2:16pm

This is what you did wrong.

A security filter cannot access other rows of the table it’s being applied to.

Explain what you’re trying to accomplish with this latest change.

CANA-IT · January 23, 2025, 5:25pm

First.. I sit here and refresh and never see your replies until much later after you post them. Weird.

What I am trying to accomplish is…
In Expense Reports Table. The Approval Process ends with a BizOps Review.
Of the Expense Report Status’s I would like the Role - ‘BizOps’ to only see:
“Awaiting BizOps Review”, “Reviewed By BizOps” and “Returned with Comments”
I added a Column in Expense Reports which is the BizOps POC - their email address.
I’ve tried multiple different things, all failures.
I truly appreciate your help.

Background, I created this system a few years ago and works perfectly in the Area120 Tables. I am trying to move the System to Appsheet because Area120 Tables is no longer being supported.
In Area120 Tables it allows you to create views for different Roles.

Steve · January 24, 2025, 8:04pm

This forum was poorly implemented by a team that was not sufficiently familiar with the complex framework. Now we’re stuck with it.

After SuperAdmin I added:> > (> “BizOps”> = LOOKUP(> USEREMAIL(),> “08 Employees”,> “CANA Account”,> “Role”> )> )

The above should be fine, but the below is a problem:

(> “Awaiting BizOps Review”> = LOOKUP(> ‘Awaiting BizOps Review’,> “02 Expense Reports”,> “Expense Status”,> “Expense Status”> )> )

The problem is that this security filter is for the 02 Expense Reports table, so you can’t perform a query against 02 Expense Reports itself. Instead, just try this:

("Awaiting BizOps Review" = [Expense Status])

CANA-IT · January 24, 2025, 8:54pm

I’ll give it a try, thanks

CANA-IT · January 24, 2025, 9:11pm

I tried to comma in a couple more Status’s but that didn’t work, I tried a few different things. I’m learning as we go, I appreciate your help.
BizOps needs to be able to see multiple status’s "

Awaiting BizOps Review"
“Returned with Comments”
“Reviewed By BizOps”

No, you can’t do it is an acceptable response.. LMAO

Steve · January 24, 2025, 9:26pm

Either this:

("Awaiting BizOps Review" = [Expense Status]),
("Returned with Comments" = [Expense Status]),
("Reviewed By BizOps" = [Expense Status])

or this:

IN(
  [Expense Status],
  LIST(
    "Awaiting BizOps Review",
    "Returned with Comments",
    "Reviewed By BizOps"
  )
)

See also: IN(), LIST()

CANA-IT · January 24, 2025, 9:39pm

I swear I tried the first one and it didn’t work… Now I try it and it is fine. I guess it had to be your suggestion… LOL…
Thank you.
Steve = Genius Potential

CANA-IT · January 27, 2025, 4:22pm

Good Morning,
I had a couple users look at the App and found out… maybe I put the lines:

("Awaiting BizOps Review" = [Expense Status]),
("Returned with Comments" = [Expense Status]),
("Reviewed By BizOps" = [Expense Status])

in the wrong place. These are being applied to all users. They should only be applied to “BizOps”
I have it listed like this… it that wrong? Thanks for the re-look.
(
“BizOps”
= LOOKUP(
USEREMAIL(),
“08 Employees”,
“CANA Account”,
“Role”
)
),
(“Awaiting BizOps Review” = [Expense Status]),
(“Returned with Comments” = [Expense Status]),
(“Reviewed By BizOps” = [Expense Status])
)

Steve · January 27, 2025, 6:13pm

Try this instead:

AND(
  (
    "BizOps"
    = LOOKUP(
      USEREMAIL(),
      "08 Employees",
      "CANA Account",
      "Role"
    )
  ),
  OR(
    ("Awaiting BizOps Review" = [Expense Status]),
    ("Returned with Comments" = [Expense Status]),
    ("Reviewed By BizOps" = [Expense Status])
  )
)

CANA-IT · January 27, 2025, 6:45pm

Expression was unable to be parsed: Number of opened and closed parentheses does not match.
(
“Admin”
= LOOKUP(
USEREMAIL(),
“08 Employees”,
“CANA Account”,
“Role”
)
),
AND(
(
“BizOps”
= LOOKUP(
USEREMAIL(),
“08 Employees”,
“CANA Account”,
“Role”
)
),
OR(
(“Awaiting BizOps Review” = [Expense Status]),
(“Returned with Comments” = [Expense Status]),
(“Reviewed By BizOps” = [Expense Status])
)
)

CANA-IT · January 27, 2025, 7:01pm

So… I figured out the parenthesis issue.
But it is now not seeing “BizOps” at all.. Only User and “BudgetHolder”
I tried OR and AND options before the statuses

Topic		Replies	Views
Clarification with Security Filters AppSheet Q&A automation , expressions	26	33	August 23, 2020
Security Filters for different level of app users AppSheet Q&A admin	6	112	February 17, 2024
Trouble with the permissions so app use only see their data AppSheet Q&A expressions	12	303	August 10, 2025

Allow traveler to see their data and budget holders to see their users data

AI Suggested topics