Filtering a list based on values in another list

(0) Share

Report

Posted on by AARON_C

2,235 Most Valuable Professional

Hi everyone,

I am seeking help trying to filter a list of mine.

I have 3 related lists in dataverse: Client list / Case list / Case Client list

Background:

A client can have multiple cases (but only one open case at a time), and a case can have multiple clients.

A client is created and then able to create a case for this client.

The client list holds basic information about the client

The case list holds information about the case (E.g. Start date, end date, description, etc)

The case client list is just the client ID and case ID

I am needing help to filter a gallery that shows the clients list, but be able to filter by start date of a case?

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

AARON_C 2,235 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
More info that hopefully makes more sense:

Above is the gallery I am displaying with the gallery's main item property coming from the Client list.

The CA-0060 is a lookup to the CaseClient list

LookUp(CaseClientLists, ClientID.ClientID = ThisItem.ClientID).CaseID.CaseID

The 5/13/2024 is a lookup to the Case list

LookUp(CaseLists, CaseID = Label17_7.Text).DateIssued \\ Where Label17_7 is 'CA-0060'

Using the date pickers above the gallery, I would like to filter the records to only show if the date that looks up the case list is between the two date pickers.

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @AARON_C ,

I believe you'll need to create a template table using nested AddColumns function based on the Client list and 2-level 1:N relationships:

Filter( With( {wFilter: AddColumns( ClientList As CL, caseid, LookUp( CaseClientLists, ClientID.ClientID = CL.ClientID).CaseID.CaseID ) ) }, AddColumns( wFilter As New, Date, LookUp( CaseLists, CaseID = New.caseid ).DateIssued ) ), Date >= StartDatePicker.SelectedDate && Date <= EndDatePicker.SelectedDate )

Best regards,

Was this reply helpful? Yes No
AARON_C 2,235 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @v-jefferni,

Thanks for your response. I am getting an error saying "Invalid number of arguments: received 1, expected 2"

Error message:

Full code:

Filter( With( {wFilter: AddColumns( ClientList As CL, caseid, LookUp( CaseClientLists, ClientID.ClientID = CL.ClientID).CaseID.CaseID ) ) }, AddColumns( wFilter As New, Date, LookUp( CaseLists, CaseID = New.caseid ).DateIssued ) ), Date >= cmpFilterHeader_MyList.DateFrom && Date <= cmpFilterHeader_MyList.DateTo )

// EDIT: One too many brackects.

Filter( With( {wFilter: AddColumns( ClientList As CL, caseid, LookUp( CaseClientLists, ClientID.ClientID = CL.ClientID ).CaseID.CaseID ) }, AddColumns( wFilter As New, Date, LookUp( CaseLists, CaseID = New.caseid ).DateIssued ) ), Date >= cmpFilterHeader_MyList.DateFrom && Date <= cmpFilterHeader_MyList.DateTo )

Was this reply helpful? Yes No