In todays blog we are thrilled to highlight the R&D work our colleague Glenn Vercnocke performed as part of our Lesterius Lab program. This Lab program allows our team to claim company time to develop their skills and broaden their understanding of different technical domains and trends.

Glenn from our Belgian team did a deep dive into the possibilities of presenting data in modern data-tables, without the use of any scripting. This development method is both very performant on large data sets, quite generic and of course offers all functionalities available from the DataTables framework.

This first part of a two series blog guides you to uncovering this technique which is outlined in the downloadable demo file below.

Stay tuned for part two.

Getting started: Adding a table to the API_simple_demo application

Download the demo file here.

Login: admin
Password: demo

FileMaker 19 Execute Data API script step: https://help.claris.com/en/pro-help/content/execute-filemaker-data-api.html

Documentation on DataTables framework: https://datatables.net

1. Tables

• Add a new table <<tablename>>

2. Graph

• Position the <<tablename>> occurrence on the graph
• Create a new API_<<tablename>> occurrence

3. Layouts

Application layout
This layout is used in your application for data entry.

• Create a layout [<<tablename>>] based on the <<tablename>> occurrence,
• Set the scripttrigger OnExitLayout (used in API_advanced_demo), with parameter
“test=1” & “|” &
“requestDetailID=” & requests::_k1_request_ID & “|” &
“limit=” & requests::query_limit &
If (not(IsEmpty(requests::parameters)) ; “|” &requests::parameters)

The parameter is used to clean up the $$request_JSON[n] when leaving a detail application layout with a web viewer.

API layout
For the Filemaker execute API to work we need an api_layout where the return fields are located.
Need to know, the order of the field objects are important, this has an impact on the column return order. At first run the JSON will be written for you with the fieldnames as column names, these can be changed.

• Create a layout api_<<tablename>> based on the api_<<tablename>> occurrence
o Put the fields you need on this layout , in the objects tab you can see the fields in order from low to high , the API returns the fields in reverse order. So the columns will be returned as ( _k1_country_ID, country code, country)
o You can also add related fields to this layout.

4. Records

• Add a new record in the “requests” table and fill in the data on the detail tab
o Add the description for example <<tablename>>
o Add layoutname api_<<tablename>>

5. Custom functions

• Duplicate an API_query_nnn_otherTable custom function change it to API_query_nnn+1_<<tablename>>
• Edit the new API_query_nnn+1_<<tablename>>
• Modify the API_queries custom function

6. Running a request

Select the [requests] layout

Go-to the [requests] layout, and press the “run” button .
The system will get the data using the Execute Filemaker Data API and show it in the web-viewer.

Example: Run the requests … this will show you the request records.

The arrow button brings you to the detail [layout] in this case the [requests]
Right to the columnname you can sort the selection.
The web-viewer data set can be searched.