How might I list orphaned records

Like (0) Share

Report

Posted on 18 Jan 2021 18:52:49 by Medoomi

523

I have two custom CDS tables, "Contact," and "Agency"

Contact has a N:1 relationship to Agency, being a lookup column in the Agency table.

Presently it's possible to delete Agencies without also deleting the associated Contacts.

Is there a way to filter a gallery of these contacts, based on their *absence* from any associated agency? i.e. I would like to exclude any contact that already has a presence in an agency lookup record.

To make it even more complicated, I would need to create a filter not subject to the delegation limit, because there are presently 35,000 agencies in the database.

Any guidance would be greatly appreciated,

Thanks!

Categories:

General Questions

Connecting to Data

All responses (3)

Answers (1)

Medoomi 523 on 18 Jan 2021 at 22:02:34

Like (0)

Report

Re: How might I list orphaned records

Thank you so much @Drrickryp . That was definitely an easy solution!
Verified answer

Drrickryp Super User 2024 Season 1 on 18 Jan 2021 at 21:14:10

Like (1)

Report

Re: How might I list orphaned records

Hi @Medoomi

You could go to the Data > Table > View menu and create a filtered view of your data where the foreign key does not contain data. Then in PowerApps you can use that view to populate a Gallery that will show you all the orphaned records.

If you are using the built in relationships in CDS, you can prevent this from happening by checking the box in the Advanced section under relationships. See how to do this here: https://docs.microsoft.com/en-us/powerapps/maker/data-platform/data-platform-entity-lookup and go to the bottom of the page. If you are using the manual method for defining the relationships (Like you and I discussed previously), you can find them by creating a View like I described above.
yashag2255 24,669 Super User 2024 Season 1 on 18 Jan 2021 at 19:25:42

Like (2)

Report

Re: How might I list orphaned records

@Medoomi

Can you try to use the below expression:

Filter(Contacts,CountRows(Filter([@'DataSources'],Contact in ColumnNameinDataSource.Contact))=0)

This shows a delegation warning, as there is no way to achieve this without the delegation warning.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!