PowerApps Expense Report Template User Filtering

[supersonicevan]

How does PowerApps handle user-specific content filtering for this app?

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/expense-report-install
The app only shows open/pending/approved expense reports submitted by the logged-in user; how is this filtered?

I don't like the standard approval process because it doesn't provide details to the approver and I dont' want them messing around with my Sharepoint list directly.

I want to create a clone of the expense app and change the filter from user-created to designated approver. Then I'll have an approvals app that can be used to check unapproved expense reports and view their details.

[K-A-R-L]

Hi @supersonicevan ,

Good day!

You can use an office365 connector or collection to populate the list of your approvers.

To use office365:

You probably need to set up a global variable for Approver and the user profile on the App Property:

On App Property:

Set(
ApproverNameVariable,
Office365Users.MyProfile().DisplayName
) //Makes current user profile a global approver variable

On your Gallery Expense Items: (Just try to play with the codes below: This will filter items that belongs to the Approver)

Sort(Filter(Expenses2, And(StartDate>=ExpenseStartDate, EndDate<=ExpenseEndDate && ApproverName = ApproverNameVariable, Or(ExpenseStatus="All", Status = ExpenseStatus))),DateSubmitted,ExpenseSorting)

Cheers

[v-yuxima-msft]

Hi @supersonicevan 

Could you please reference @K-A-R-L 's suggestion?

And yes, the template only uses collections to create the tables.You can choose to click App.OnStart to check or change the formula expression.

Best Regards.

Yumia

[supersonicevan]

I've added the Set ApproverNameVar in App.OnStart, see below for complete App.OnStart code: 

Set(ExpenseStartDate,DateAdd(Now(),-7,Days));Set(ExpenseEndDate,Now());Set(ApproverNameVar,Office365Users.MyProfile().DisplayName)

I've also added Office356Users as a datasource, so that's running fine.

My ExpenseGallery code was throwing an 'invalid argument' error but once I added .DisplayName to ApproverName, the code executes without error.

Sort(Filter(Expenses, And(StartDate>=ExpenseStartDate, EndDate<=ExpenseEndDate && ApproverName.DisplayName = ApproverNameVar, Or(ExpenseStatus="All", Status.Value = ExpenseStatus))),DateSubmitted,ExpenseSorting)

The problem is the ExpensesGallery returns nothing. I suspect the data feeding into the ExpensesGallery is being filtered prior to reaching here but I don't know where that is happening.

[K-A-R-L]

Hi @supersonicevan ,

First, can you please try to check if your filter really returned something:

Please insert a label and add this to the Text Property:

//This will count the number of items returned by the filter

CountRows(Filter(Expenses, And(StartDate>=ExpenseStartDate, EndDate<=ExpenseEndDate && ApproverName.DisplayName = ApproverNameVar, Or(ExpenseStatus="All", Status.Value = ExpenseStatus))))

Or you can try to Collect the Filter:

Insert a button then add this to the OnSelect Property:

//Check if the collection returns something: View > Collection > CollectedExpenses

ClearCollect(CollectedExpenses, Filter(Expenses, And(StartDate>=ExpenseStartDate, EndDate<=ExpenseEndDate && ApproverName.DisplayName = ApproverNameVar, Or(ExpenseStatus="All", Status.Value = ExpenseStatus))))

Next, If you think data feeding into the ExpensesGallery is being filtered prior to reaching can you add the ClearCollect Code above on the OnVisible Property of the Screen then add the change your Gallery Item to: "CollectedExpenses".


Cheers

[Anonymous]

Hi @supersonicevan how are you progressing with this?

@Anonymous 

[biglilb0y]

Hi, how can i add filter so it will display only the expenses I created, currently i can see all the open, pending and approved expenses created within my organization. Thanks in advance.

[K-A-R-L]

Hi @biglilb0y ,

Try adding the adding the code below to the AppStart Property: (Optional just to set User().email as global Variable on start of your app)

Set(UserDefault, User().Email)

On the Gallery filter the Items using the UserDefault Variable.

Filter(DataSource, UserEmail = UserDefault) //UserEmail is a Column on your DataSource where you store Users or something similar

You might have delegation problem but let me know.

If you add the Set on the App Start you might need to:

Save your app > Close the App > Open the app again.

----------------------------------------------------------------------------
Thanks,
K-A-R-L

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If you thought this post was helpful, please give it a Thumbs Up.

[biglilb0y]

Hi @K-A-R-L Thank you so much for answering my question,

I tried to add at the App OnStart

Set(Createdby,User().FullName)

and added to Expense Gallery

Filter(Expenses,Created By = Createdby)

my problem is when writing the filter the field Created By is not available from the list and it does not allow me to use that field.

Thanks!

[K-A-R-L]

Hi @biglilb0y ,

Are you using SharePoint as Data Source? Can you please replace the space on the Column Name to "_x0020_" .

e.g. Created_x0020_By

And please make sure that "Created By" is tha column you're after.

----------------------------------------------------------------------------
Thanks,
K-A-R-L

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If you thought this post was helpful, please give it a Thumbs Up.