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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresident, Dedication Technologies, Inc.Commented:
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 )  ; "" ; "" ; "")


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

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.


Will LovingPresident, Dedication Technologies, Inc.Commented:
That should definitely work. Just have the calculation use the name of the TOC named in the field.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rafael EstrellaSr. Systems AnalystAuthor Commented:

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!  

Will LovingPresident, Dedication Technologies, Inc.Commented:
You're welcome. Be sure to mark the question as answered when you have a chance. Thanks.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rafael EstrellaSr. Systems AnalystAuthor Commented:
Will Loving clearly understood the problem and provide a swift response to my inquiry.   Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.