JSON.List_Tables

Lists all Table information in JSON format

Be the first to rate this function Log in to vote

Kyle Williams - Show more from this author
Augusto Digital
https://www.youtube.com/channel/UCpJlB7cmVNMjbjgY96MEsng

Sample input:

JSON.List_Tables()

Sample output:

{
"TableIDs" :
{
"1065548" :
{
"BaseFileName" : "DC_Invoices (DREAMDC01)",
"BaseTableName" : "BatchForQBTransfer",
"ID" : 1065548,
"ModCount" : 98,
"Name" : "batchFor_Invoice.LineItemsAll"
},
"1065549" :
{
"BaseFileName" : "DC_Clients (DREAMDC01)",
"BaseTableName" : "GLOBALS",
"ID" : 1065549,
"ModCount" : 12,
"Name" : "GLOBALS"
}
},
"TableNames" :
{
"AA_Locations" :
{
"BaseFileName" : "DCOS (DREAMDC01)",
"BaseTableName" : "AA_Locations",
"ID" : 1065385,
"ModCount" : 160,
"Name" : "AA_Locations"
},
"ACUinfo" :
{
"BaseFileName" : "DC_Invoices (DREAMDC01)",
"BaseTableName" : "ACUinfo",
"ID" : 1065322,
"ModCount" : 35,
"Name" : "ACUinfo"
}
}

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
Author:     Kyle Williams
Date:       2/29/2020
Purpose:    Create a list of Table Data in JSON format
Note:       Requires FM18+ (uses the While function)
*/

While (

//Initial Variables
[
	 list = Substitute ( ExecuteSQL ("SELECT * FROM FileMaker_Tables";"⟂";"🆕" ); [¶; "\r"];["🆕"; ¶] )
	;FoundCt = ValueCount( list )
	;LoopCt = 1
	;$record = If(LoopCt = 1; ""; $record)
];

//Condition
LoopCt ≤ FoundCt;

//Logic
[
	 TableInfo = Substitute ( GetValue ( list; LoopCt ); "⟂"; ¶ )
	;TableName     = GetValue ( TableInfo; 1 )
	;TableID       = GetValue ( TableInfo; 2 )
	;BaseTableName = GetValue ( TableInfo; 3 )
	;BaseFileName  = GetValue ( TableInfo; 4 )
	;ModCount      = GetValue ( TableInfo; 5 )
	;$record = 
		JSONSetElement ( $record
			;[ "TableIDs."&TableID; TableID; JSONObject ]
			;[ "TableIDs."&TableID&".ID"; TableID; JSONNumber ]
			;[ "TableIDs."&TableID&".Name"; TableName; JSONString ]
			;[ "TableIDs."&TableID&".BaseTableName"; BaseTableName; JSONString ]
			;[ "TableIDs."&TableID&".BaseFileName"; BaseFileName; JSONString ]
			;[ "TableIDs."&TableID&".ModCount"; ModCount; JSONNumber ]

			;[ "TableNames."&TableName; TableName; JSONObject ]
			;[ "TableNames."&TableName&".ID"; TableID; JSONNumber ]
			;[ "TableNames."&TableName&".Name"; TableName; JSONString ]
			;[ "TableNames."&TableName&".BaseTableName"; BaseTableName; JSONString ]
			;[ "TableNames."&TableName&".BaseFileName"; BaseFileName; JSONString ]
			;[ "TableNames."&TableName&".ModCount"; ModCount; JSONNumber ]
			)
	;LoopCt = LoopCt + 1
];

//Result
JSONFormatElements ( $record )

)

It is always important to reference everything dynamically because static names and references are always changing. this function allows you to use JSON functions to extract the information you need for any table.

Comments

		Kyle Williams, Augusto Digital Feb 29, 2020
Easy way to get all info for your current layouts Table: JSONFormatElements ( JSONGetElement ( JSON.List_Tables, "TableNames." & get(LayoutTableName ) )) Returns: { "BaseFileName" : "DC_Invoices", "BaseTableName" : "INVOICES", "ID" : 1065089, "ModCount" : 1097, "Name" : "DC_INVOICES" }

		Kyle Williams, Augusto Digital Feb 29, 2020
Current Layout's BaseTableName: JSONGetElement ( JSON.List_Tables, "TableNames." & get(LayoutTableName ) & ".BaseTableName" )

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

JSON.List_Tables

Comments

Support this website.

Support this website.