Help getting a list of Tables and record count formated in JSON

Questions
,
1

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.

        RecordCount = ExecuteSQL ("SELECT COUNT(*) FROM " & Path; ""; "")

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 )
)
2

I continued to work on this and managed to get it to work. Any suggestion on how to improve this are more then welcome.

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 )
)
3

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.

4

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?

5

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 (70.3 KB)

3 Likes
6

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.

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 )

)
3 Likes
7

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.

1 Like
8

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.

9

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.

2 Likes