How might I list orphaned records

(0) Share

Report

Posted on 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:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

yashag2255 24,769 Super User 2024 Season 1 on at

Like (2)

Report

@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!

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Medoomi 523 on at

Like (0)

Report

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

Was this reply helpful? Yes No