cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GarryPope
Impactful Individual
Impactful Individual

Filter Gallery by variable and Status Reason

Hello Power Apps Community,

Can you help please... and I hope I can explain this correctly...

 

I am building a canvas app, using Dataverse for Teams.

I have two tables: "Employee" which is the parent table, and "Transaction" which is the child table.
So one "Employee" record to many "Transaction" records.

 

I have an Employee screen with a gallery showing all the Employee records.

I have two icons for each record, the icons are called "Open" and "Closed".

 

Search Employee Gallery.png


When I click the "Open" icon or the "Closed" icon, on the relevant "OnSelect" property, it takes me to another screen (Transaction) with another gallery for the child table: Transaction. 

The expression is this: 


Navigate
(
                ScreenEmployeeTransaction,
                ScreenTransition.None,
                {lclThisEmployee: galEmployeeSearch.Selected} 
)

When I land on the child screen and gallery (Transaction) I want to see all related Transaction records of the parent (Employee) record I selected in the previous screen. 
So on the gallery I filtered the Transaction "Items" property with the below expression: 
 
Filter(
         Transactions,
         'Employee Name'.'Employee' = lclThisEmployee.'Employee'

)

Again, this is working great. So if I select John Smith on the Employee gallery I move to the Transaction screen and gallery and just see John Smith's Transactions.

My request: After all that, what I'm now looking to do is:

1. If on the Employee screen, image at the top, I click on "Open" then when I navigate to the Transaction screen and gallery I only want to see John's Smith's Transactions with the Status Reason of "Active".
2. If on the Employee screen, image at the top, I click on "Closed" then when I navigate to the Transaction screen and gallery I only want to see John's Smith's Transactions with the Status Reason of "Inactive".

Any thoughts would be greatly appreciated. 

Garry

PS. Model-driven apps for Dataverse for Teams cannot come quick enough for me. Can't wait to leave these canvas apps behind! Haha.

1 ACCEPTED SOLUTION

Accepted Solutions

@GarryPope 

Auch ...please excuse me. I skip the part that you work with DataVerse.

For Dataverse, the right formula is:

Filter(
         Transactions,
         'Employee Name'.'Employee' = lclThisEmployee.'Employee' && 
         If (selectedStatusReason="Active",
                    'Status Reason'='Status Reason (Transactions)'.'Active',
                    'Status Reason'='Status Reason (Transactions)'.'Inactive'

          )

)

Once again sorry for inconvenince.

 

View solution in original post

9 REPLIES 9
gabibalaban
Super User
Super User

Hi @GarryPope,

Set the onSelect property of the Open/Close button to:

Navigate(
                ScreenEmployeeTransaction,
                ScreenTransition.None,
                {lclThisEmployee: galEmployeeSearch.Selected, selectedStatusReason:"Active" /"Inactive" } 
)
(choose Active / Inactive - based on the button you set it) 
Based on this, change the filter for the second gallery to:
Filter(
         Transactions,
         'Employee Name'.'Employee' = lclThisEmployee.'Employee' && 'Status Reason'=selectedStatusReason

)

 

Hope it helps !

GarryPope
Impactful Individual
Impactful Individual

Hello @gabibalaban,

Thanks for replying. Especially on a Saturday. I really appreciate this. 
So the first part worked on my Open or Closed button, which is great. 

When I added the second expression to the Transaction gallery, I got the below error:

"We can't evaluate your formula because the values being compared in the formula aren't the same type."

Error on formula.png

 

Any thoughts on this please? No worries if not.

Garry

gabibalaban
Super User
Super User

@GarryPope 

Most probably the error is due to the fact that your status reason column is a choice type one.

Use ‘Status Reason’.Value instead.

 

GarryPope
Impactful Individual
Impactful Individual

Hello @gabibalaban,

Thanks for replying again. Sorry, mate, but that didn't work.

I wrote the expression like below:

 

Filter(
Transactions,
'Employee Name'.'Employee' = lclThisEmployee.'Employee' && 'Status Reason'.Value = selectedStatusReason
)
 
But it still didn't work unfortunately. Sorry.

Value error.png
 
When I mouse over the .Value the error is Name isn't valid. This identifier isn't recognized.
Thanks,
Garry

@GarryPope 

There is something I miss ... Can you show the table structure of Transaction table ? 

GarryPope
Impactful Individual
Impactful Individual

Hello @gabibalaban,

It's most probably my terrible explanation. Thanks for all your help.

 

Here's the Transaction table:

Transaction table.png 

Here's the relationship structure for the Transaction table.

 

Transaction relationships.png

 

Thanks very much,

Garry

@GarryPope 

Auch ...please excuse me. I skip the part that you work with DataVerse.

For Dataverse, the right formula is:

Filter(
         Transactions,
         'Employee Name'.'Employee' = lclThisEmployee.'Employee' && 
         If (selectedStatusReason="Active",
                    'Status Reason'='Status Reason (Transactions)'.'Active',
                    'Status Reason'='Status Reason (Transactions)'.'Inactive'

          )

)

Once again sorry for inconvenince.

 

View solution in original post

GarryPope
Impactful Individual
Impactful Individual

@gabibalaban Woohoo! Woohoo! Woohoo!

You can't believe how happy you've made me. Thanks so much. And please don't apologise, you helped so much. 

When I added the formula in it worked perfectly! The labels in the gallery were not populating, but I put an extra ) at the end of your formula and the labels populated with data. 

Thanks so much for spending you weekend helping me. I really appreciate it. 

All the best,

Garry

Thanks @GarryPope ! I'll update my formula to reflect the right solution ...

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,291)