Sample output(出力例):
{ "BaseTableName": "TableName", "FileName": "FileName", "LayoutName": "LayoutName", "LayoutTableName": "LayoutTableName", "Record": { "FAX": "", "TEL": [ "0000-00-0000", "" ], "_k": "1000001", "emailAddress": "", "フリガナ": [ "フリガナ01", "フリガナ02" ], "住所": [ "住所01", "住所02" ], "名称": [ "名称01", "名称02" ], "郵便番号": "000-0000" } }
formula(式):
Let ([
/*BaseTableName*/
_query = "
SELECT BaseTableName
FROM FILEMAKER_TABLES
WHERE TABLENAME = ?
"
;_BaseTableName = ExecuteSQL ( _query ; "" ; "" ; Get ( LayoutTableName ) )
/*Record*/
;~query = "select
'[\"'||TableName ||'::'||FieldName||'\"'
,''||''||FieldReps||']'
FROM FILEMAKER_FIELDS
WHERE
TableName ='{TableName}'
AND
FieldClass = 'Normal'
AND
FieldType NOT LIKE 'global%' /*除外 グローバルフィールド*/
AND
FieldType <> 'binary' /*除外 オブジェクトフィールド*/
"
;~query=Substitute ( ~query ; "{TableName}" ; Get ( レイアウトテーブル名 ) )
;~FieldNames = ExecuteSQL ( ~query ; "," ; "" )
;_Record=While (
[
~json=""
;~MAX = ValueCount ( ~FieldNames )
;~n= 1
] ;
~n <= ~MAX ;
[
~Array=GetValue ( ~FieldNames ; ~n )
;~FieldName=JSONGetElement(~Array;0)
;~FieldReps=JSONGetElement(~Array;1)
;~key = GetValue ( Substitute ( ~FieldName ; "::" ; ¶ ) ; 2 )
;~json = If(~FieldReps=1;
JSONSetElement ( ~json ; ~key ; GetField ( ~FieldName ) ; JSONString)
;
JSONSetElement ( ~json ; ~key ;
While (
[
~RepsData=""
;~i = 1
;~index=0
];
~i<=~FieldReps;
[
~RepsData=JSONSetElement (~RepsData ; "[" &~index & "]" ; GetField ( ~FieldName & "["&~i &"]" ) ;JSONString)
;~i =~i+ 1
;~index=~index+1
];
~RepsData
)
; JSONArray)
)//if
;~n=~n+1
] ;
~json
)
];
JSONSetElement ( ""
; ["FileName" ; Get ( ファイル名 ) ; 1 ]
; ["LayoutName" ; Get ( レイアウト名 ) ; 1 ]
; ["LayoutTableName" ; Get ( レイアウトテーブル名 ) ; 1 ]
; ["BaseTableName" ; _BaseTableName ; 1 ]
; ["Record" ; _Record ; 3 ]
)
)
