Purging Obsolete Records

Questions
1

I have a parent table (Series) and a child table (Subscribers). I want to identify, flag, and delete records in the Series table with no corresponding records in the Subscriber table.

One way would be to loop through the Series table and use GTRR to identify parent records with no children by capturing the GTRR error code (101).

Another approach would to add a summary field to the Subscriber table (to get a record count) and use an auto-enter to populate a field in the Series table.

Any other approaches I should consider?

2

You could use IsValid(MotherTable::KeyField) to get 'lost' records from the child-table (for more about IsValid see FM-Help)

1 Like
3

You may want a count-of-children field in the parent table if the record count in the parent table is large, especially if the file(s) is served over WAN. This field should be updated by scripts or auto-entry, again especially if the file(s) is served over WAN. A find for the count-of-children field = 0 will give you the found set you seek.

2 Likes
4

You could do GTRR from the parent table to the child table (which will find all related records) and then invert the found set to show the orphans.

2 Likes
5

Since the parent key is indexed in both tables, I might consider creating two value lists. One for the id in the parent table and another value list for the parent key in the child table. Then I would compare the two lists for unique values in the parent value list.

1 Like
6

Is that because performing a find will be faster than looping over thousands of records?

7

Definitely!

1 Like
8

Isn't it the other way around: perform a GTRR from the child to the parent table, then show the omitted records?

9

A calculated field in the related table with the function Get(FoundCount) uses internal FMP magic to provide the exact count of records related to the current record. It does not involve any data transfer, so it doesn't care if your related record set is none or 1million.

if you want more details @weetbicks has posted on this in his blog in the past

10

https://www.teamdf.com/blogs/a-lightning-fast-alternative-to-the-count-function/

3 Likes
11

I created two fields in my related table - one uses the count function and the other uses get found count. Then, I added these fields to my parent layout. They both display the same results. However, I can search on the “count” field but not the “found count” field.

12

You get what you pay for :grinning:

But that is a really interesting result. Thanks for sharing it. I wonder what allows us to search one and not the other?

1 Like
13

According to Jason Wood on the Community site, “You can search in an unstored field or one without an index (although it is slow because it needs to evaluate the value in every record in the table), but what you can't do is search a related field where the relationship is based on a field without an index.”

I believe this is the case with my layout and the “found count” field.

1 Like