Unanswered

Filtering Dataverse data by related table in Canvas app

(0) Share

Report

Posted on by JS-05081236-0

I am loading data from a table into a collection. I would like to be able to filter those items at load time based on a related table.

The table itself is called "Topics" and the related table is called "Topic state".

The "Topic state" holds data for the built-in stage/gate functionality in Model-driven Power Apps

I would like to filter my Topics such that Topics with a specific state is not included. In the Model-driven Power App I have created a View that does just that:

My current filter - that does not take into account the data in the related table - looks like this:

ClearCollect(topics_all,Filter(Topics, 'Show in pipeline' = 'Show in pipeline (Topics)'.Yes && Not(Status = 'Status (Topics)'.Inactive)));

How do I augment this to include the related table?

I can download the "FetchXml" from the View - it is possible to use that data in the filter in my Canvas app?

Thanks,

Jesper 😊

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Jonathan Manrique 2,687 on at

Like (0)

Report
Hi @jrossstocholm

You must add one more filter where you attack the Topic State table and filter by the GUID of the Topic table record and then also have another filter parameter where Active State <> Topic present.

ClearCollect(topics_all,Filter(Topic State(Filter(Topics, 'Show in pipeline' = 'Show in pipeline (Topics)'.Yes && Not(Status = 'Status (Topics)'.Inactive)));Guidtopic = TopicState.topic;ActiveStage <> TopicPresent))

Was this reply helpful? Yes No
ivan_apps 2,187 Moderator on at

Like (1)

Report

If you have created the view in the Dataverse table already, why not just reference that view in your filter? Take a look at this article, it shows you how to use that existing view in your canvas app.

http://powerappsguide.com/blog/post/how-to-create-and-use-dataverse-views

Video walkthrough: https://youtu.be/eKygMP7ySR8?si=UunLz-EkSR3tW0xT

Was this reply helpful? Yes No
JS-05081236-0 83 on at

Like (0)

Report

I have previously had some issues with loading data from a View into a Collection.

Solved: Re: Loading Dataverse data into collection does no... - Power Platform Community (microsoft.com)

I will look into it again to see if I can make it work this time. It would be considerably easier to just use the predefined view.

Was this reply helpful? Yes No
ivan_apps 2,187 Moderator on at

Like (0)

Report

Ah ok I see. So I think you want to use that filter but ultimately have your table show data from both you primary table and the business process flow table associated with it. In that case either you can do an ‘AddColumn’ to add the associated record as a column to your collection, or just use a Lookup() inside your gallery to retrieve that info per row. Basically this is the equivalent of a SQL join which should be achievable. This other community post and answer does a great job of explaining the ways to do a join:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Syntax-for-joining-tables/td-p/61387

Was this reply helpful? Yes No