Filter Gallery from 2 Tables

[fbarini]

Hello Folks, hope someone can help me out with this since I've spent quite an amount of time on it and could not solve it..

I have this app, which is made for Hiring Request and has an approval WorkFlow. When I was asked to develop such app, we would have a standard Workflow, which I could solve by using the following. (Huge thanks for @RandyHayes)

The formula is used to filter a gallery, which shows, to each specific approver, the items waiting for his "Ok/Not Ok".

Filter(BaseAppRH; 
   ('Status Aprov. 1' = "Analysing" && 'Aprov. 1' = User().Email) ||
   ('Status Aprov. 2' = "Analysing" && 'Aprov. 2' = User().Email) ||
   ('Status Aprov. 3' = "Analysing" && 'Aprov. 3' = User().Email)
)

The project evolved, and now the standarized workflow isn't so standard anymore. For each role that is requested, there is a different (sometimes the same) workflow. The Approving person varies accordingly to 2 pieces of information as following.

1. Company ID (I.E.: Building 1) and 2. The Role Itself (I.E.: CEO).

The challenge is to filter the gallery with the new Workflow. I have two tables at SharePoint (1 Dimension - RolesTable and 1 Fact - BaseApp). 

RolesTable is as follows

Building	Department	Roles	Approv. 1	Approv. 2	Approv. 3
Building 1	Sales	Sales Analyst	myemail@company.com	other@company.com	another@company.com
Building 3	TI	CTO	other@company.com	myemail@company.com	another@company.com
Building 4	Finance	CFO	another@company.com	myemail@company.com	other@company.com

BaseApp also contains the same information for the first 3 columns, which should be enough for me to filter the gallery, but I just can't find the solution. My best bet, which failed, was to make a column using "Building&Roles", to make a unique "ID" on both Tables, but I couldn't make it work out.

[v-bofeng-msft]

Hi @fbarini :
Could you please tell me:

• What the Detailed filter rules of BaseApp is? Do you want to get current user's Building/Roles/Approve stage from RolesTable, and use these info to filter the records in Base APP?
• Is it possible for the same user to exist in multiple recrods of RolesTable?
  
• Is it possible for the same user to exist in multiple Approv stages of a recrod in RolesTable?
• Could you please show me a demo of BaseApp?

Best Regards,

Bof

[fbarini]

Lets-go

• What the Detailed filter rules of BaseApp is?  When someone enters the Approval Screen, the displayed items  criteria is: A- Status Approval 1, 2 or 3 should be analysing (there can only exist one column "Analysing" per time) and B- User().Email needs to be the same listed as Approver 1, 2 or 3 (which varies accordignly to Building&Role.  Do you want to get current user's Building/Roles/Approve stage from RolesTable, and use these info to filter the records in Base APP? Almost Correct. When someone creates a Hiring Request, they'll input the specfic Building&Role they want to hire, which is used to determine who are the Approvers 1,2 and 3. In other words, I need to filter something like ("Status Aprrov 1 = "Analyising" && CHECK IF "USER().EMAIL" IS EQUAL TO APPROVER 1 OF THAT SPECIFIC BUILDING&ROLES COMBO).

• Is it possible for the same user to exist in multiple recrods of RolesTable? The only column with users here is the approval 1, 2 and 3 columns. I only have one approver per column, but there are some specific combos that one of them might not be elegible to approve.

• Is it possible for the same user to exist in multiple Approv stages of a recrod in RolesTable? No

• Could you please show me a demo of BaseApp? How could I do this?

Thanks!

[fbarini · Accepted solution]

Solved it by Patching the approvers directly to my DB (BaseApp.Approver1, BaseApp.Approver2, BaseApp.Approver3) with the LookUp(TableRoles; "Building&Role" = "DropDownBuilding.Selected"&"DropDownRole.Selected").Aprov1.

Now I can just Filter my gallery as posted on the original post.