cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
supersonicevan
Level: Powered On

PowerApps Expense Report Template User Filtering

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.

1 ACCEPTED SOLUTION

Accepted Solutions
K-A-R-L
Level 10

Re: PowerApps Expense Report Template User Filtering

Hi @biglilb0y ,

On your LabelOpenValue change the Text() Property to:

"$" & If(
    Sum(
        Filter(
            LineItems,
            ReportID.Id in OpenReports.ID && Author.DisplayName = User().FullName
        ),
        Cost
    ) > 0,
    Text(
        Sum(
            Filter(
                LineItems,
                ReportID.Id in OpenReports.ID && Author.DisplayName = User().FullName
            ),
            Cost
        ),
        "[$-en-US]#,###.00"
    ),
    0
)

----------------------------------------------------------------------------
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.

View solution in original post

16 REPLIES 16
K-A-R-L
Level 10

Re: PowerApps Expense Report Template User Filtering

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

Community Support Team
Community Support Team

Re: PowerApps Expense Report Template User Filtering

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.

FormulaExpression.PNG

 

Best Regards.

Yumia

supersonicevan
Level: Powered On

Re: PowerApps Expense Report Template User Filtering

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
Level 10

Re: PowerApps Expense Report Template User Filtering

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




TopShelf-MSFT
Level 10

Re: PowerApps Expense Report Template User Filtering

Hi @supersonicevan how are you progressing with this?

 

@TopShelf-MSFT 

biglilb0y
Level: Powered On

Re: PowerApps Expense Report Template User Filtering

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
Level 10

Re: PowerApps Expense Report Template User Filtering

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
Level: Powered On

Re: PowerApps Expense Report Template User Filtering

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
Level 10

Re: PowerApps Expense Report Template User Filtering

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.

 

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,261)