How to find orphaned records

Lets say we have 2 tables:

Parent (table 1)
contains the following columns
UniqueID
List of children

Children(table 2)
contains the following columns
UniqueID
Name

As time goes on there are children added that don’t get added to parent.

How do I locate children that are not contained in ANY parent’s list of children?

Hi there, @wardey0513… I just created a quick example on my end, and I think you are going to need an advanced filter on this one. So, if you were going to populate a repeating group with a list of the orphaned records, the repeating group’s data source would be Search for Children:filtered (no constraints on the search), and the advanced filter would be Advanced: Search for Parents:each item's List of children doesn't contain This Children (no constraints on the search for parents).

It probably goes without saying that such a filter will likely be pretty inefficient from a performance perspective (especially because advanced filters take place on the client side), and a way around that would be if you had a field on the Children data type to store each children’s parent. That probably seems unnecessary because you have the List of children on the Parent data type, but I will sometimes include seemingly unnecessary fields like that just in case I need to do something like what you have described.

Anyway, hope this helps.

Best…
Mike

1 Like

Thanks @mikeloc ! That worked like a charm. I was on the right path on how to do it but this was what I was looking for.

Agreed that this would not be super efficient but I would just use this from time to time to clean up data (after today’s pricing scare, every row counts!!)

Thanks again.

1 Like