Novice Question: Create a calculated table field in a central table that counts records from other tables.

I'm working on a solution for a friend's small business.

Scenario:  I created a table that stores a list of the FileMaker tables within the database for this solution.
I'll refer to this table as a foundation table.   I've also created a list form to show the different table names listed in the foundation table.

Q: How do I create a calculation field within the foundation table that counts the number of records found in each of the other tables by the table name stored in the foundation table?

Thus far, I've attempted the following and I'm not getting accurate record counts.

  • fnd_tables     - foundation table name.
  • table_names - a field within the fnd_tables table
  • xc_recordcount - a calculation field within the fnd_tables table that contains the following syntax:

GetAsNumber(ExecuteSQL("SELECT count(*) FROM FileMaker_Tables WHERE BaseTableName = ?";"";"";table_names))
Rafael EstrellaSr. Systems AnalystAsked:
Who is Participating?
 
Will LovingPresidentCommented:
You're welcome. Be sure to mark the question as answered when you have a chance. Thanks.
0
 
Will LovingPresidentCommented:
I haven't been able to figure out how to query a BaseTable directly for the Count, but since you CAN query any Table Occurrence, you can simply query one TO for each table. If you have a Table Occurrence in your graph for each Table that matches, exactly, the name of the table, you then use your BaseTable list "table_names" and GetValue for each field. You could also run a script with a simple table that simply loops through the calculation, creates a new record with each loop, and uses Set Field to capture the Field name and record count. With each iteration of the loop increment the Value number until it reaches the total number of tables.

TableCount_01 = ExecuteSQL("SELECT count(*) FROM " & GetValue ( table_names ; 1 )  ; "" ; "" ; "")

with

Table_names = ExecuteSQL ( "SELECT DISTINCT BaseTableName FROM FileMaker_Tables" ; "" ; ""  )
0
 
Rafael EstrellaSr. Systems AnalystAuthor Commented:
Will,

Thank you for the quick feedback.

Okay, suppose I add another field to the foundation table to store the names of corresponding occurrences?    Then, I can reference that new field for the occurrence names instead of the actual tables names.   Based on the information you provided, I'll try several approaches and let you know what I come up with.

Thanks,

Rafael
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Will LovingPresidentCommented:
That should definitely work. Just have the calculation use the name of the TOC named in the field.
0
 
Rafael EstrellaSr. Systems AnalystAuthor Commented:
Will,

Your insight provided some direction in achieving my goal.   Thank you so much.   Here's what worked...

In the foundation table, I created another field ("table_name_ocr") so that I could populate the table occurrence names in my list.  Populated the occurrence name for each table.  Then, I modified my existing calculation field and used the syntax below.

ExecuteSQL("SELECT count(*) FROM " & GetValue ( table_name_ocr ; 1 )  ; "" ; "" ; "")

Again, thank you!  

Rafael
0
 
Rafael EstrellaSr. Systems AnalystAuthor Commented:
Will Loving clearly understood the problem and provide a swift response to my inquiry.   Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.