Reading Many to many relationships in CDS

[pz]

Hello all. For an app I am building, I require the use of many to many relationships. I am using CDS. I have an entity called "agency" an entity called "tags" and an entity called "tagsmap" that maps tags to agencies and vice versa:

Agency {PrimaryID, name}

tagsmap{PrimaryID, Agency_ (a look up to agency), tag_ (a look up to tags)}

tags{PrimaryID, name}

I want to be able to search for tags and get the names of all agencies that have the tag associated. My process right now looks like this:

1) fitler tags based on a text string and collect the filtered tags

ClearCollect(TempTags, Search(tags, searchbar.text, "name"))

2) filter tagsmap based on the collection of filtered tags

ClearCollect(TMapResults, Filter('tag map', tag_ in TempTags))

This retuns a list of tagsmap entities which have tag entities and agency entities nested within them:

{tagsmap: PrimaryID, Agency_, tag_}

I am stuck here. How do I filter TMapResults in a way that returns agency names?

Thanks!

[v-yamao-msft]

Hi pz,

Currently, Common Data Service doesn’t support many to many relationship type, please check the documentation at here:
https://powerapps.microsoft.com/en-us/tutorials/data-platform-entity-lookup/

About your scenario, I guess that this thread could be a reference for you:
https://powerusers.microsoft.com/t5/PowerApps-Forum/Many-to-Many-Table-Relationships-in-SQL-Server-D...

Best regards,
Mabel Mao

[Meneghino]

Here is a post on many-to-many relationships in PowerApps:

https://baizini-it.com/blog/index.php/2017/10/10/powerapps-101-many-to-many-relationships-between-ta...