Beverly Voth, Claris Platform, Claris Pro, Claris Studio, Level: Intermediate

Claris Studio (part 4): Forms the easy way!

January 30, 2023January 31, 2023 beverlyvoth

About the author: Beverly Voth has been in the Claris FileMaker community many years. In addition to FileMaker Pro and its integrated products, she is a Full Stack Web developer & SQL database administrator. The only recipient of the FileMaker Excellence Award for Outstanding Contribution to the FileMaker Web Publishing Community (DevCon 2003), she’s been advocating Claris FileMaker and web since they could work together.

This article covers more Views, a little errata from past articles (Parts 1, 2, & 3), and where we go from here. This is the final article of the planned four. But if something really inspiring materializes, there may be followup article(s). Some famous “trilogies” (films and books, especially) have gotten additional content, so why not?

Welcome back! The Spreadsheet View and the Dashboard View are not the only features of Claris Studio. I chose them to discuss first because the Spreadsheet has more information about the tables and fields. That information may be helpful on all Views. But we have other Views: anonymous Form submission, a List-Detail View (suitable for searching, adding, editing), a Kanban View (just another way to present the data & edit it), and some new ones since the last article. Remember that Dashboards are created from the Spreadsheet View only, so they do not appear on this graphic:

Click on the Create New View button to see these options.

We can start with one of these views to create the “table” or we can start with existing data (from a Spreadsheet setup or migrated from Claris Pro). Let’s get started with a review of the Forms. Continue reading “Claris Studio (part 4): Forms the easy way!” →

Beverly Voth, Claris Platform, Claris Pro, Claris Studio, Level: Intermediate

Claris Studio (part 3) – Spreadsheet: Details, please!

November 1, 2022November 1, 2022 beverlyvoth

This is part three of a series on the new Claris platform. We introduced the overview, Claris Studio, Tell Me More! on 29 September 2022, and Claris Studio (part 2) – Integration with Claris Pro on 12 October 2022. This article gets into more details on the different types of views in Claris Studio. Since the spreadsheet view will likely be a most common usage for many, this article will be devoted to it. The spreadsheet view in Claris Studio has plenty of goodies & features, and can be used to create Dashboard views (charts and summary text).

A new spreadsheet view – with default fields, table name, & view name

Claris Studio, Spreadsheet View

Of all the views available in Claris Studio, the spreadsheet is a very flexible table for data entry & editing, sorting & grouping. These web-based data grids (tables) are more robust than FileMaker/Claris Pro table view, better than web viewer data tables (easier at least!), but probably not as full-featured as Excel or Numbers spreadsheets. There are no “cell” formulas or references as in some spreadsheets, but there is a way to present a grid of rows & columns for data entry, viewing, and reporting. Web-based data grids may use methods that allow drag-and-drop re-arrangement of rows and/or columns, as you will find in Claris Studio’s spreadsheet.

There are several JavaScript & CSS techniques & frameworks or libraries used for tables on the web. Many of them have been integrated into FileMaker Pro (and now Claris Pro) though Web Viewers.

Spreadsheet.js (https://spreadsheet.js.org/)
SheetJS (https://sheetjs.com/)
CloudTables (https://datatables.net/examples/data_sources/js_array.html)
npm, Datatables plug-in for jQuery (https://www.npmjs.com/package/datatables)
AppML Brings Data to HTML (https://www.w3schools.com/appml/default.asp)
more when you start searching!

But they all look like coding down and dirty. That’s great if that’s what you want to do. Would you like something easier? Would you like to send this data to the other Claris Studio view types, including dashboards (charting)? And would you like to share the data collected with Claris Pro? Let’s get started with the finer points of spreadsheets in Claris Studio! Continue reading “Claris Studio (part 3) – Spreadsheet: Details, please!” →

Beverly Voth, Claris Platform, Claris Pro, Claris Studio, Level: Intermediate

Claris Studio (part 2) – Integration with Claris Pro

October 12, 2022October 15, 2022 beverlyvoth

About the author: Beverly Voth has been in the Claris FileMaker community many years. In addition to FileMaker Pro & its integrated products, she is a Full Stack Web developer & SQL database administrator. The only recipient of the FileMaker Excellence Award for Outstanding Contribution to the FileMaker Web Publishing Community (DevCon 2003), she’s been advocating Claris FileMaker and web since they could work together.

Welcome back to part two of our Claris Studio & Claris Pro exploration. This article will cover Claris Pro and how it integrates with Claris Studio. Since we started with Claris Studio, Tell Me More!, there was a major update to the features in Claris Studio. You were advised to be alert! Check What’s New for the latest Claris Studio updates. This article is about conversion of Claris FileMaker Pro database files to Claris Pro files and how Claris Pro works with Claris Studio tables (two way interaction!)

What is Claris Pro?

Claris Pro is an application toolbox full of features that allow you to create user interfaces for data collection, display, and reporting. It can be used to create everything from a collection of Grandma’s recipes to full mission-critical business applications. It’s a cool and R.A.D. (pun intended!) toolbox for use by those starting out to full-stack relational database developers. Claris Pro uses a graphical interface for most creation, but easily works with external data exchanges. Once created with Claris Pro, these apps can be used on iPhones, iPads, or viewed in web browsers, as well as on the desktop/laptop by one user or thousands of users. You can use Claris Pro with Windows and macOS. Continue reading “Claris Studio (part 2) – Integration with Claris Pro” →

Beverly Voth, Level: Intermediate, Version: FM 14 or later, XML

Excel Exports using XML and XSLT

November 5, 2017August 24, 2022 beverlyvoth

Editor’s note: Once again it is an honor and a privilege to present an in-depth guest article written by Beverly Voth.

This is a follow-up to the User-Friendly Excel Exports series by Kevin Frank, but using an XML export along with various XSLT to give you .csv or .xls (for Excel). The first demo is a quick export (to CSV – comma-separated values) using an XSLT to help explain the relationship with FileMaker Pro XML export. How an XSLT style sheet reads that data to transform it into another text format (.csv or .xls) is also explained throughout the article and in comments within the XSLT. The demos will get increasingly more complex, but ultimately more “user-friendly”.

Demo file: Excel-Exports-using-XML-and-XSLT.zip

Continue reading “Excel Exports using XML and XSLT” →

Beverly Voth, Level: Intermediate, Version: FM 16 or later

Export Field Contents (Update for FM 16)

May 9, 2017December 28, 2018 beverlyvoth

Editor’s note: I am thrilled to feature a guest article by Beverly Voth.

In the article “An In-Depth Look at “Export Field Contents” (here), the study of how FileMaker exports text as single field contents is compared to the standard FileMaker Exports. Some of these present a problem when the field (exported) gets converted to something which a receiving system may reject as invalid. Several methods and alternative “fixes” are presented, including using XML & XSLT.

Update! NEW IN FMP 16 – we have a FIX!

There is a new function in FileMaker Pro (and Advanced) 16 that “fixes” a couple problems with Export Field Contents (namely the encoding and end-of-line). Instead of the Base64 nested function trick use the new Function:

TextEncode( text ; encoding ; lineEndings )

Reference: FM16 Help: TextEncode

Using TextEncode() & Export Field Contents

Set Field [ container ; TextEncode ( text ; encoding ; lineEndings ) ]

Continue reading “Export Field Contents (Update for FM 16)” →

Beverly Voth, Level: Intermediate, Version: FM 13 or later

FileMaker UI Tricks: Dual-Purpose Layout

August 22, 2015August 24, 2022 beverlyvoth

Editor’s note: Today it’s my honor and privilege to present an article by guest author Beverly Voth, whose contributions to the FileMaker community are numerous and much appreciated. It’s great to have her back here on FileMaker Hacks.

FileMaker 13 & 14 have some features that can be leveraged to allow one layout to toggle between Form View & Table View and look differently! We will be using the Starter Solution, “Contacts”. Feel free to download the demo file (Contacts14.fmp12.zip) or you may start with a fresh copy of the database file or another Starter Solution and follow the steps below.

NEW to Starter Solutions? Select “New From Starter Solution” under the File menu. Click the “Contacts” icon and click on the “Choose” button. A dialog will come up with the “Save As” field filled in with the name of the database file you chose. Navigate to where you wish to save the file and click the “Save” button. This will open the file to the default layout and be ready to change.

The default layout for Contacts is “Contact Details”, is in Form View and shows two “tab-like” buttons called: Contact List & Contact Details. These are buttons that Switch you to different layouts. Notice how the placement is the same and there is a smooth transition between the two layouts. You may also notice that the view is “locked” to just allow View as List or View as Form (respectively). You cannot select other views on these two layouts.

CONTACTS_details_tab

CONTACTS_list_tab

Continue reading “FileMaker UI Tricks: Dual-Purpose Layout” →

Beverly Voth, ExecuteSQL, SQL

The Missing FM 12 ExecuteSQL Reference

October 19, 2012August 24, 2022 beverlyvoth

There seem to be many questions about the usage of SQL (Structured Query Language) with the ExecuteSQL function in FileMaker 12. This tutorial attempts to explain some of the SQL terms, if you are new to writing SQL statements. Since there are already many examples of how to write the ExecuteSQL queries, links to these will be listed at the end of this article. If you don’t need to learn the terms, jump right to the Helpful Example Databases section, below. There you will find links to solutions that help you create and test your queries.

This is not a complete SQL guide, as other databases may use other syntax. This is not a complete FileMaker and SQL guide, as FileMaker may be an ODBC source and the SQL queries made against it may vary from the terms used by ExecuteSQL(). This is not a complete FileMaker and ESS guide using SQL calls (if using Import or Execute SQL script steps or ExecuteSQL() function or ESS). It may not have all the nuances needed for other data sources. This is the ExecuteSQL() function reference for which you’ve been waiting. The FileMaker 12 ODBC and JDBC Guide is helpful, but it has uses outside (and beyond) the ExecuteSQL() function. Any discrepancies between the reference and the function will be noted here, if possible.

Continue reading “The Missing FM 12 ExecuteSQL Reference” →

Beverly Voth, Level: Any, Version: FM 16 or later

An In-Depth Look at “Export Field Contents”

September 23, 2012August 24, 2022 beverlyvoth

Have you ever wanted to export a single field and maintain all the characters in that field? This article explores the possibility with XML Export and the use of a simple XSLT. But first we’ll explain the good, the bad and the ugly of some different standard ways to export TEXT out of FileMaker.

Using this sample text in one field (and one record) we will make different exports and review the results:

Continue reading “An In-Depth Look at “Export Field Contents”” →

Beverly Voth, ExecuteSQL, Level: Intermediate, Version: FM 12 or later

Aggregates (Summary Fields) in Filtered Portals

September 13, 2012August 24, 2022 beverlyvoth

The question on one or more of the FileMaker forums was asked and answered. Q: How do you get the Count() of the related records in a filtered portal? A: (paraphrased) duplicate the filtered portal and make it one row in height. Place the related summary field, “Count of…”, in the single row filtered portal. Voila! your count is now filter-specific. (The instructions for creating the filter for a portal and summary field are below.)

This recent topic led me to consider what other aggregate fields could be used with the filtered portal. And what about that Go To Related Record script step? Does it only show the related FILTERED records or all related records? The demo (bvoth_aggregates_in_portals.fmp12) and article have been created to answer these questions.

I started using filtered portals in cross-tab reports shortly after they were introduced. These are generally ways to show something very specific in each portal with sorts and filters and usually one row only. I had not explored the use of aggregates in these cross-tab reports until now.

Kudos to those before me that may have discovered these answers and tricks, too.

Continue reading “Aggregates (Summary Fields) in Filtered Portals” →

Claris Studio, Spreadsheet View

What is Claris Pro?

What is Claris Studio?