Setup

The demo file includes all the pieces you need to use accordion tables in your FileMaker database. You will need to copy the HTML to your own database, substitute in your CSS and data JSON, and then display the result as a data URL in a web viewer.

Download the demo file to get started.

Download

The HTML

The HTML, CSS, and Javascript for the accordion tables are all in the PREF::html field. Copy the value of the field over to your own database and put it in an accessible location, such as a single-record preferences table. You’ll be using this as the base source HTML for your web viewers.

This HTML has placeholders for your custom CSS and initial table data. You MUST replace these values with real data or the HTML will not render. The placeholder values are __css__ and __data__ for the custom CSS and table JSON data respectively. We’ll talk more about substituting these values below.

Custom CSS

You will need a place to store your custom CSS if you’d like to make the table match the look and feel of the rest of your system. We recommend creating a CSS text field to store your custom CSS, which will be merged into the HTML. This should be a stored field somewhere (possibly the same preferences table with the HTML) so that you have one place to modify the look and feel of your tables.

Initial Table Data

The table can be updated on-the-fly using JavaScript and the new Perform JavaScript functionality in FileMaker 19. However, if you’re on an earlier version of FileMaker, the only way for you to get your data into the table for display is at the time the HTML is being generated.

One simple strategy for this is to create a field which will store your table JSON. This could be stored in each record or in a global field that you populate using a script.

We strongly recommend copying some sample data from the data field in the demo file to start so that you can see if the table is rendering correctly. You can change this data out later once you’ve made sure your setup is correct and the table is rendering properly with the example data.

Substitute the placeholders

Create a calculated field which will store the final HTML from the text field and merge in your custom CSS and initial set of data. This can be done using the substitute function in a calculation. Be sure to set your calculation result as text.

Remember to strip any return characters from the data you’re passing in in the JSON, as they are not valid characters. It’s also important to make sure your quotes are properly escaped.

Here is an example calculation which inserts the custom CSS and initial data into the HTML to create a value ready to be put into a web viewer. It removes invalid line breaks, properly escapes quotes in data with the Quote() function, and inserts a valid empty JSON object for the data if the initial data field is empty.

Let(
[
~html = portal_PREF::html;
~html = Substitute ( portal_PREF::html; "__css__"; PORTAL::css_c);
~data = Case ( PORTAL::data = ""; "{}"; PORTAL::data );
~data = Substitute( ~data; "¶"; "");
~html = Substitute ( ~html ; "__data__" ; Quote(~data) )
];
TextFormatRemove ( ~html )
)

Displaying the Accordion Table

With the HTML calculated and substituted you’re now ready to display the accordion table on your layout. The generated HTML can be displayed using a web viewer on your layout and a data URL. Set a web viewer to display the value of your calculated HTML field by setting the Web Address property of your web viewer using a data URL like the following:

"data:text/html," & DEMO::html_c

Setup Complete

That should be it! Switch back to browse mode. If you copied the demo data over from the demo file you should see a table with some expandable accordion rows. If you haven’t created any table data yet or set an initial data value you may not see anything in the portal, but it should hopefully be working and ready for you to insert some table data using JavaScript.

Troubleshooting

If you’ve placed some sample data in your data field and still aren’t seeing anything, a good way to troubleshoot problems is to look at the calculated HTML. The HTML calculation field should be completely valid HTML which is usable in any browser. Save the value of the HTML calculation field to an HTML file on your computer and you can use a text editor to inspect it or open it in a web browser to see if you see any problems with your calculation.

Accordion Tables