Filter Gallery by variable and Status Reason

Like (0) Share

Report

Posted on 6 Feb 2021 08:55:13 by CU01081947-0

716

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.

Categories:

Using Apps Using Formulas

All responses (9)

Answers (1)

CU-18081211-6 9,266 Moderator on 06 Feb 2021 at 16:48:23

Like (0)

Report

Re: Filter Gallery by variable and Status Reason

Thanks @GarryPope ! I'll update my formula to reflect the right solution ...
CU01081947-0 716 on 06 Feb 2021 at 16:44:40

Like (1)

Report

Re: Filter Gallery by variable and Status Reason

@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
Verified answer

CU-18081211-6 9,266 Moderator on 06 Feb 2021 at 16:08:31

Like (1)

Report

Re: Filter Gallery by variable and Status Reason

@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.
CU01081947-0 716 on 06 Feb 2021 at 14:22:50

Like (0)

Report

Re: Filter Gallery by variable and Status Reason

Hello @gabibalaban,
It's most probably my terrible explanation. Thanks for all your help.

Here's the Transaction table:

Here's the relationship structure for the Transaction table.

Thanks very much,
Garry
CU-18081211-6 9,266 Moderator on 06 Feb 2021 at 12:24:12

Like (0)

Report

Re: Filter Gallery by variable and Status Reason

@GarryPope

There is something I miss ... Can you show the table structure of Transaction table ?
CU01081947-0 716 on 06 Feb 2021 at 11:27:25

Like (0)

Report

Re: Filter Gallery by variable and Status Reason

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.

When I mouse over the .Value the error is Name isn't valid. This identifier isn't recognized.
Thanks,
Garry
CU-18081211-6 9,266 Moderator on 06 Feb 2021 at 11:08:01

Like (1)

Report

Re: Filter Gallery by variable and Status Reason

@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.
CU01081947-0 716 on 06 Feb 2021 at 10:56:11

Like (0)

Report

Re: Filter Gallery by variable and Status Reason

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

Any thoughts on this please? No worries if not.
Garry
CU-18081211-6 9,266 Moderator on 06 Feb 2021 at 09:31:14

Like (1)

Report

Re: Filter Gallery by variable and Status Reason

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 !