Unanswered

powerapps filter table based on value from another list

(0) Share

Report

Posted on by jpiazza

I'm trying to filter a list which contains "status details". I want to display the all of the status details which are connected to the selected issue record. Where "Ticket #" = the "ID" (ticket# = field in CV Status, ID=the primary key of the record in CV issues and enhancements).

A few things to note:

The CV status table is in a DataCard - should this be in a Gallery?

The field Ticket # (in CV Status List) is a drop down field pulling from the ID field in the CV Issues and Enhancements List

Here is the formula I'm using, but its not working:

Filter('CV Status Updates', 'CV Status Updates'.'Ticket #'='CV Issues and Enhancements'.ID)

- I'm getting an error which says "The filter part of this formula may not work correctly on large data sets"

Thanks in advance

Jim

Categories:

Building Power Apps

All responses (10)

Answers (0)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@jpiazza

Sounds like your Ticket # field is a LookUp field in SharePoint, is this correct? I avoid setting up these field types (ie ALL Complex columns, Choice, LookUp, Calculated, etc) for any lists that I intend to use with PowerApps - they are just so buggy, and restrictive, as you have discovered.

For a fix, you may be able to set your 'CV Issues and Enhancements'.ID to a variable then use that in your formula, eg

Filter('CV Status Updates', 'CV Status Updates'.'Ticket #'= someVarName) // where someVarName is the ID value matching Ticket #

It's a little hard to tell how to set this variable because I cannot see the flow of your app, ie how you are getting other data in. I can help further with that, if need be, if you explain your app a little more.
jpiazza 39 on at

Like
a
(0)

Report
Copy link

Link copied!
Thank you for the response. To troubleshoot my issue, I did create another field which I manually typed in the Ticket #.
Filter('CV Status Updates', 'CV Status Updates'.'Ticket #'= someVarName)
in your formula - the field in the "Status updates" table, needs to match the value of the ID in the CV Issues and Enhancements table. How would I write that? The ticket number is simply the ID record in the CV Issues and enhancements table.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@jpiazza

Can you tell me what types the fields 'Ticket #' and 'ID' (from CV Issues and enhancements) are. Are they both LookUp columns?
jpiazza 39 on at

Like
a
(0)

Report
Copy link

Link copied!
I noticed an issue with using the correct tables (I had the same table in a different MS Team for testing). This is fixed.

Ticket # (number) - this is manually typed in
ID is the primary key in the CV Issues and enhancements table

If I were to create a variable for the ID field in the Issues and enhancements table - where would I define that?
Its strange that I can't apply any filter to the table

This is what I'm thinking for the 2 formulas:
Set(varTicketID, 'CV Issues and Enhancements'.ID)

Filter('CV Status Updates','Innosabi Ticket'= varTicketID)

Thanks for your help
Jim
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@jpiazza

So you are using Teams for your app, is this correct? Meaning that Dataverse is your data source? I don't have a lot of Dataverse experience but I do know that LookUp fields are tricky to use in that they need to be referenced differently.
jpiazza 39 on at

Like
a
(0)

Report
Copy link

Link copied!

I appreciate your help. I'm still stuck. I have everything in sharepoint. If I were to define a variable with the ticket # - where would I define that? This seems like a basic filter - I can filter based on a static # ie: 1 but when I try to refer to another field, it doesn't work (delegation issue). The table is only about 97 records.

Thoughts?
Jim
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@jpiazza

Just so I understand your situation, please confirm that your setup is as follows:

- List 'CV Issues and Enhancements' holds Ticket numbers (ie 'ID') and there is one and only one Ticket number

- List 'CV Status Updates' has a LookUp column called 'Ticket #' that references ID in the above table

- This LookUp column is set to NOT allow multiple values ie Single LookUp

If the above is correct you should be able to do something like this

Filter( 'CV Status Updates', 'Ticket #'.Value = TextInput1.Text ) // where TextInput1 is a Text Input control.

Your initial formula doesn't have a reference to a Ticket number and you need one so you can filter the 'CV Status Updates' List, hence why I'm using a TextInput field. You can also use a reference from click an item in another gallery or some other way but you need a reference to filter the Status list.

I'd also recommend using a Gallery over a Data Table, unless you are just wanting to view the results. Galleries give you far more UI flexibility but if you don't need that then a Data Table will be ok.
jpiazza 39 on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry for the delay - thank you for your continued help on this. I see where you have "TextImput.1.Text" - shouldn't this be a "whole number" ? Because Ticket is a number value. Would it be better to have this as a text value?

Thanks,
Jim
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@jpiazza

You can just wrap that in the Value function to force the text to number data type, ie

Value(TextInput1.Text)

You can also enforce entry of numbers only into the Text Input field by setting the Format property to TextFormat.Number
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I've had an issue that relates to this title and thought I'd share how I solved it in the hopes it might help someone!

I have two tables A and B. Both have a common field/primary key C (in my case a person's name). I want a gallery filtering table A by column D in table B.
(e.g. table B has a column D (Active), I want to show data from table A only when 'Active' is true)

This is how I did it:
Filter( A , C in Filter( B , D = desiredValue).C)

e.g. Filter( B, Active = true).name provides an array of C (names) where D (active column) is true. I am then filtering for C (names in table A) in the array that is sourced from table B.