Relationship count

[Arny]

I believe this is a rather straight forward question, but I can't seem to wrap my head around the solution.

 

I have a master table that is linked to 7 related tables. Each of the related tables has 1,000's of records. Many of these records have the same master link. All I want to do is determine how many master records have NO relationship inside the related table.

 

For example . . If my master has 10 records and a related table has 900 records. In the related table lets say that there are 100 records for each of 9 master records. The answer I'm looking for is '9', which is the number of unique links between the master and related table. This information lets me determine that I'm 90% of the way toward populating my related table so I have a match for every master table entry.

 

My master table has ~40,000 records and most of the related tables have between 40,000 and 90,000 records, so trying to do this in any manual method seems a bit time consuming and ultimately not worthwhile.

 

Any suggestions would be welcome

[Wim Decorte]

ExecuteSQL() may help here:

 

SELECT DISTINCT(<the foreign key>) FROM <theRelatedTable>

 

Then do a ValueCount on the returned result.

You can of course do a count right there in the SQL statement but returning the result will let you count in FM *and* check which ones of the master records you  have not covered.

[Arny]

Thanks . . I'll give it a try. Not used to using the SQL options.

[Helpful Harry]

All I want to do is determine how many master records have NO relationship inside the related table.

If I understand correctly then, purely within FileMaker's functions, you can create a Calculation Field in the Master Table to test if they are any related records by counting how many are in each related table.

c_HasRelatedRecords = 
     If(
         (
          Count(RelationshipTable1::KeyField) 
          + Count(RelationshipTable2::KeyField) 
          + Count(RelationshipTable3::KeyField) 
          + Count(RelationshipTable4::KeyField) 
          + Count(RelationshipTable5::KeyField) 
          + Count(RelationshipTable6::KeyField) 
          + Count(RelationshipTable7::KeyField)
         ) > 0; 
         "Yes"; 
         "No")

If you only need to know for yourself, you can then simply perform a Find on "No" values to find how many Master Table records have no Related Records.

If you need an actual value displayed on-screen, then you can create a Global Field (g_GlobalNoField) with a value of "No" and then create a Relationship within the Master Table to group the "No" records together, and then use the Count function to count them.

rel_NoRelationship
Match Fields in MasterTable with Fields in MasterTable 
when c_HasRelatedRecords = g_GlobalNoField

c_NumRcordsWithNoRelatedRecords = 
     Count(NoRelationship::c_HasRelatedRecords)