# Help getting a list of Tables and record count formated in JSON
**URL:** https://the.fmsoup.org/t/help-getting-a-list-of-tables-and-record-count-formated-in-json/3618
**Category:** Questions
**Tags:** scripting, executesql
**Created:** [June 28, 2023, 10:33pm UTC](https://the.fmsoup.org/t/help-getting-a-list-of-tables-and-record-count-formated-in-json/3618 "2023-06-28T22:33:09Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@Susurrus](https://the.fmsoup.org/u/Susurrus)
#### Post date: [June 28, 2023, 10:33pm UTC](https://the.fmsoup.org/t/help-getting-a-list-of-tables-and-record-count-formated-in-json/3618/1 "2023-06-28T22:33:09Z")
I am working on making a script that will set a variable that stores all the table names of the current file and there record count in JSON. I am close but I am having issues getting the count to work. How dose one make this work?
Below is what I am using and it is working but when I add the RecordCount section it fails. I think I need to add in a let inside the Conditions section but I am not sure how to format it correctly.
```auto
RecordCount = ExecuteSQL ("SELECT COUNT(*) FROM " & Path; ""; "")
```
```auto
While ( [
//initial Varables
FileName = Get ( FileName )
; TableNames = ExecuteSQL (
"SELECT BaseTableName FROM FileMaker_Tables
WHERE BaseFileName LIKE ? GROUP BY BaseTableName ORDER BY BaseTableName"
;""; ""; FileName & "%"
)
; TableCount = ValueCount ( TableNames )
; LoopCount = 1
; JSON = "{}"
; Path = ""
] ;
//condition
LoopCount < TableCount
; [
Path = GetValue ( TableNames ; LoopCount )
; JSON = JSONSetElement ( JSON
;[Path & ".Count"; 10 ; JSONString]
)
;LoopCount = LoopCount +1
] ;
//result
JSONFormatElements ( JSON )
)
```
---
### Author:  [@Susurrus](https://the.fmsoup.org/u/Susurrus)
#### Post date: [June 28, 2023, 11:30pm UTC](https://the.fmsoup.org/t/help-getting-a-list-of-tables-and-record-count-formated-in-json/3618/2 "2023-06-28T23:30:40Z")
I continued to work on this and managed to get it to work. Any suggestion on how to improve this are more then welcome.
```auto
While ( [
//INITIAL VARIABLES
FileName = Get ( FileName )
; TableNames = ExecuteSQL (
"SELECT BaseTableName FROM FileMaker_Tables
WHERE BaseFileName LIKE ? GROUP BY BaseTableName ORDER BY BaseTableName"
;""; ""; FileName & "%"
)
; TableCount = ValueCount ( TableNames )
; LoopCount = 1
; JSON = "{}"
; Table = ""
]
;
//CONDITION
LoopCount < TableCount +1
;
//LOGIC
[
Table = GetValue ( TableNames ; LoopCount )
; Records = ExecuteSQL ( "SELECT COUNT(*) FROM " & "\"" & Table & "\"" ; ""; "")
; JSON = JSONSetElement ( JSON
;[Table & ".Count"; Records; JSONString]
)
;LoopCount = LoopCount +1
] ;
//RESULTS
JSONFormatElements ( JSON )
)
```
---
### Author:  [@steve\_ssh](https://the.fmsoup.org/u/steve_ssh)
#### Post date: [June 28, 2023, 11:42pm UTC](https://the.fmsoup.org/t/help-getting-a-list-of-tables-and-record-count-formated-in-json/3618/3 "2023-06-28T23:42:57Z")
I'm thinking that it looks like it makes the assumption that there will always be a Table Occurrence named the same as its parent base table (which is often the case in most solutions I look at).
In the case where there is a BaseTable that does not have a Table Occurrence named after it, I'm not sure that your SQL to get the record count will return a result.
HTH.
---
### Author:  [@Susurrus](https://the.fmsoup.org/u/Susurrus)
#### Post date: [June 29, 2023, 12:30am UTC](https://the.fmsoup.org/t/help-getting-a-list-of-tables-and-record-count-formated-in-json/3618/4 "2023-06-29T00:30:55Z")
Thanks for the reply. I am definitely still learning to use SQL in my solutions and would not at all be surprised if I am missing how they work. That being said I am not completely following you.
The **TableNames** variable should get a list of the table names not table occurrence right? and then the **Records** variable will use that list of TableNames to then count how many records they have.
Am I just wrong on how the SQL query work?
---
### Author:  [@steve\_ssh](https://the.fmsoup.org/u/steve_ssh)
#### Post date: [June 29, 2023, 3:50am UTC](https://the.fmsoup.org/t/help-getting-a-list-of-tables-and-record-count-formated-in-json/3618/5 "2023-06-29T03:50:14Z")
Everything that you are doing and which you have stated above is very sound in its thinking, but there is one nuance about how ExecuteSQL works, which I am calling out.
Unless it has changed in recent versions without my realizing it: Table references in the SQL written for use with ExecuteSQL are actually not BaseTable names, as one would think. They are actually _ **Table Occurrence** _ names.
So, for example, the attached file has two tables in it. One of them will be picked up by the calculation posted above, the other will not. And the reason why one is not detected is because I deliberately changed the name of one of the table occurrences on the relationship graph to illustrate this as a gotcha.
So -- while the calculation is correctly grabbing a list of BaseTable names in the while loop, when it comes to performing the query to get the record count, what you really want to supply is the name of a _TableOccurrence_ based off of the table in question.
In cases where there happens to be a TableOccurrence which has a name that is identical to the BaseTable name, everything works out as desired. But if this code is ever run in a solution where a BaseTable exists without a similarly named TableOccurrence on the graph, then (again, unless this has changed in more recent versions of FMP) there will be a failure to query the record count.
Hope this makes sense. And apologies for having to qualify everything with "unless this has changed" -- presently I am writing from an older machine which is not running the most recent version of FMP.
All the best,
-Steve
[TestFileForSQL.fmp12.zip](https://the.fmsoup.org/uploads/short-url/G25NixDKog6diAKlZydZpqEpuV.zip) (70.3 KB)
---
### Author:  [@Malcolm](https://the.fmsoup.org/u/Malcolm)
#### Post date: [June 29, 2023, 9:11am UTC](https://the.fmsoup.org/t/help-getting-a-list-of-tables-and-record-count-formated-in-json/3618/6 "2023-06-29T09:11:49Z")
Here's how I would do it. Latest version of FileMaker includes a handy new function called `BaseTableNames( fileName )`, and the base table name is returned when you query filemaker\_tables using the `ExecuteSQL()` function.
I've formatted the output for readability for humans, though in actual use this is never required. Just use the data output without formatting.
```auto
While ( [
fileName = Get ( FileName )
; BT = BaseTableNames ( fileName )
; n = ValueCount ( BT )
; i = 0
; json = JSONSetElement ( "" ; "" ; "" ; JSONArray )
]
; i < n
; [
i = i + 1
; BTN = GetValue ( BT ; i )
; TO = ExecuteSQL ( "SELECT TableName FROM filemaker_tables WHERE BaseTableName = ? FETCH FIRST ROW ONLY" ; "" ; "" ; BTN )
; RC = ExecuteSQL ( "SELECT COUNT(*) FROM " & Quote ( TO ) ; "" ; "" )
; jsonItem = JSONSetElement ( "" ; ["BaseTableName" ; BTN ; 1] ; ["Records" ; RC ; 2] )
; json = JSONSetElement ( json ; i - 1 ; jsonItem ; JSONObject )
]
; JSONFormatElements ( json )
)
```
---
### Author:  [@Susurrus](https://the.fmsoup.org/u/Susurrus)
#### Post date: [June 29, 2023, 5:16pm UTC](https://the.fmsoup.org/t/help-getting-a-list-of-tables-and-record-count-formated-in-json/3618/7 "2023-06-29T17:16:56Z")
Thanks for the detailed description of the issue. Luckily our policy is to always have a table occurrence with the same name as the base table. But this is good information to have. If I had ran into it I am not sure I could have figured out why it was not working.
---
### Author:  [@Susurrus](https://the.fmsoup.org/u/Susurrus)
#### Post date: [June 29, 2023, 5:22pm UTC](https://the.fmsoup.org/t/help-getting-a-list-of-tables-and-record-count-formated-in-json/3618/8 "2023-06-29T17:22:43Z")
Thanks for the example code. We are still testing 2023 and lots of our customers have not upgraded yet. I wish we could use the BaseTableNames function.
I really like your JSON method a lot more then mine. I am not sure why buy JSON and FileMaker seems to really confuse me. I have been forcing myself to use it more to try and beat it into my head. Having your example is a lot of help.
---
### Author:  [@Kirk](https://the.fmsoup.org/u/Kirk)
#### Post date: [January 5, 2024, 7:35pm UTC](https://the.fmsoup.org/t/help-getting-a-list-of-tables-and-record-count-formated-in-json/3618/9 "2024-01-05T19:35:36Z")
> [@Malcolm](#):
>
> `BaseTableNames( fileName )`,
Tip: Hard coding the filename is OK, but If you want to "cheat" a little from the abstraction standard `Get ( FileName )` Construct, a` ""` in place of it, will refer to the existing filename.