# Purging Obsolete Records **URL:** \ **Category:** Questions\ **Tags:** scripting\ **Created:** [December 22, 2021, 7:45am UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659 "2021-12-22T07:45:38Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![steverichter](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/steverichter/32/1022_2.png) [@steverichter](https://the.fmsoup.org/u/steverichter)\ **Post date:** [December 22, 2021, 7:45am UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/1 "2021-12-22T07:45:38Z")
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? ---
**Author:** ![Markus](https://avatars.discourse-cdn.com/v4/letter/m/b782af/32.png) [@Markus](https://the.fmsoup.org/u/Markus)\ **Post date:** [December 22, 2021, 7:55am UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/2 "2021-12-22T07:55:08Z")
You could use IsValid(MotherTable::KeyField) to get 'lost' records from the child-table (for more about IsValid see FM-Help) ---
**Author:** ![bdbd](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/bdbd/32/620_2.png) [@bdbd](https://the.fmsoup.org/u/bdbd)\ **Post date:** [December 22, 2021, 1:55pm UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/3 "2021-12-22T13:55:16Z")
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. ---
**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\ **Post date:** [December 22, 2021, 2:50pm UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/4 "2021-12-22T14:50:12Z")
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. ---
**Author:** ![rivet](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/rivet/32/1727_2.png) [@rivet](https://the.fmsoup.org/u/rivet)\ **Post date:** [December 22, 2021, 3:02pm UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/5 "2021-12-22T15:02:24Z")
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. ---
**Author:** ![steverichter](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/steverichter/32/1022_2.png) [@steverichter](https://the.fmsoup.org/u/steverichter)\ **Post date:** [December 22, 2021, 4:11pm UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/6 "2021-12-22T16:11:00Z")
> [@bdbd](#): > > 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. Is that because performing a find will be faster than looping over thousands of records? ---
**Author:** ![bdbd](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/bdbd/32/620_2.png) [@bdbd](https://the.fmsoup.org/u/bdbd)\ **Post date:** [December 22, 2021, 4:46pm UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/7 "2021-12-22T16:46:38Z")
Definitely! ---
**Author:** ![bdbd](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/bdbd/32/620_2.png) [@bdbd](https://the.fmsoup.org/u/bdbd)\ **Post date:** [December 22, 2021, 4:49pm UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/8 "2021-12-22T16:49:07Z")
> [@xochi](#): > > 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. Isn't it the other way around: perform a GTRR from the child to the parent table, then show the omitted records? ---
**Author:** ![Malcolm](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/malcolm/32/196_2.png) [@Malcolm](https://the.fmsoup.org/u/Malcolm)\ **Post date:** [December 22, 2021, 8:56pm UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/9 "2021-12-22T20:56:36Z")
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 ---
**Author:** ![weetbicks](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/weetbicks/32/206_2.png) [@weetbicks](https://the.fmsoup.org/u/weetbicks)\ **Post date:** [December 22, 2021, 9:18pm UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/10 "2021-12-22T21:18:51Z")
[https://www.teamdf.com/blogs/a-lightning-fast-alternative-to-the-count-function/](https://www.teamdf.com/blogs/a-lightning-fast-alternative-to-the-count-function/) ---
**Author:** ![steverichter](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/steverichter/32/1022_2.png) [@steverichter](https://the.fmsoup.org/u/steverichter)\ **Post date:** [December 23, 2021, 2:05am UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/11 "2021-12-23T02:05:13Z")
> [@Malcolm](#): > > A calculated field in the related table with the function Get(FoundCount) uses internal FMP magic 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. ---
**Author:** ![Malcolm](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/malcolm/32/196_2.png) [@Malcolm](https://the.fmsoup.org/u/Malcolm)\ **Post date:** [December 28, 2021, 7:20pm UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/12 "2021-12-28T19:20:03Z")
You get what you pay for 😀 But that is a really interesting result. Thanks for sharing it. I wonder what allows us to search one and not the other? ---
**Author:** ![steverichter](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/steverichter/32/1022_2.png) [@steverichter](https://the.fmsoup.org/u/steverichter)\ **Post date:** [December 28, 2021, 7:55pm UTC](https://the.fmsoup.org/t/purging-obsolete-records/2659/13 "2021-12-28T19:55:54Z")
According to Jason Wood on the [Community](https://community.claris.com/en/s/question/0D50H00007DOSz0SAH/quick-search-issue) 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.