cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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
Solution Specialist
Solution Specialist

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
Solution Specialist
Solution Specialist

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
Community Support

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

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.

 

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
Not applicable

Hi @supersonicevan how are you progressing with this?

 

@Anonymous 

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.

 

 

Solution Specialist
Solution Specialist

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.

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!

Solution Specialist
Solution Specialist

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
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (30,722)