Re: Nested Filter on 2 table Dataverse
Issue Summary:
You need to filter a gallery in PowerApps to show only ongoing projects by combining data from two Dataverse tables: a Master table and a Job Status table. The goal is to display projects from the Master table that have a status of "On Going" in the Job Status table.
Solution Steps:
Identify Relationships:
- Ensure that the Master Lookup in the Job Status table properly references the ID of the Master table.
Use Nested Filters:
- Filter the Job Status table to find entries with the "On Going" status.
- Extract the Master Lookup values from the filtered Job Status entries.
- Use these values to filter the Master table.
Example Code:
Here's how you can achieve this in PowerApps using nested Filter and LookUp functions:
Filter the Job Status Table:
- First, filter the Job Status table to find all entries with a status of "On Going".
// Filter Job Status to get IDs of ongoing projects
ClearCollect(
OngoingProjects,
Filter('Job Status', 'Project Status' = "On Going")
)
2. Filter the Master Table Using the Extracted IDs:
- Use the collected OngoingProjects to filter the Master table.
// Filter Master table based on IDs in OngoingProjects
Filter(
Master,
ID in OngoingProjects.MasterLookup
)
Detailed Steps:
Step 1: Filtering Job Status Table:
- Use ClearCollect to create a collection of ongoing projects.
ClearCollect(
OngoingProjects,
Filter('Job Status', 'Project Status' = "On Going")
)
Step 2: Filtering Master Table:
- Use the OngoingProjects collection to filter the Master table.
Filter(
Master,
ID in OngoingProjects.MasterLookup
)
Final Combined Code:
You can put the combined logic directly in the Items property of the gallery that is supposed to show the ongoing projects:
Filter(
Master,
ID in Filter('Job Status', 'Project Status' = "On Going').MasterLookup
)
Explanation:
Filter('Job Status', 'Project Status' = "On Going"):
- This filters the Job Status table to only include rows where the Project Status is "On Going".
MasterLookup:
- The MasterLookup contains the IDs from the Master table corresponding to the ongoing projects.
Filter(Master, ID in ...):
- This filters the Master table to include only the projects whose IDs are present in the filtered Job Status table.
Additional Tips:
- Ensure that your data connections are correctly set up in PowerApps.
- Verify that the Master Lookup field in the Job Status table is correctly configured to reference the Master table's IDs.
By following these steps, you should be able to filter your gallery to display only the ongoing projects from the Master table. Please mark this as a solution if this helps you resolve your query
