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

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 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).

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!

Spreadsheet view features

• When you create a new spreadsheet these default fields/columns are created for you: First Name, Last Name, Email, Comments. These default fields can be renamed or deleted from the table.
• You may add or remove columns of these field types: Short Text, Long Text, Number, Drop-down (selection option), Checkboxes (selections), Single Choice (radio selection), Date, Time, Timestamp, Phone, Email, Signature (graphic), & Attachment (graphic/file). The Address type is a special block type and adds several named text columns at once: Address Line 1, Address Line 2, Suite, City, State, & Zip. New as of 18 October (Claris Studio Help: What’s New) are two more types: User & Currency. See below for details.
• After your spreadsheet is set up, you can create these views from the same table that the spreadsheet view uses: Form, List-Detail, Kanban, & Dashboard. NOTE: Member users cannot create views.
• As of the 18 October 2022 update, you can no longer share the spreadsheet view with anonymous users. You can, however, add spreadsheet views to hubs and invite users. Only Managers can create dashboards, but Members can do everything else, including: add/delete fields/column & records/rows. Login to Claris Studio requires Claris ID for all users to work with spreadsheet view.
• You may also view the data (and edit) when the table created for the spreadsheet is shown as a shadow table in Claris Pro. Records can also be added or deleted in Claris Pro, but the schema (columns/fields) cannot be altered in this shadow table.

What’s in a name?

Before we deep dive into the spreadsheet view, we may need to define a few terms used when talking about fields and columns. These may refer to the spreadsheet or other views.

• Name (of field or column, depending on where you use it) can be changed in spreadsheet view (easily on the column header, by popover dialog) or on any view with the sidebar (click on the name to change it). This is the name that should appear in the shadow table in Claris Studio.
• Label (text that shows above the field entry on the form-type view) is not necessarily the same as the name.
• Placeholder (text that shows inside the field in a form-type view) is also not necessarily the same as the label or name.
• Default (value that is auto-entered into a field/column, any view) is shown instead of placeholder, if there is a value (as defined).
• Description (shown in spreadsheet view only) is similar to a comment on a defined field in FileMaker/Claris Pro. There is a little black circle-i next to the field name in spreadsheet view only. This description is not shown in the field of the shadow table as a comment, however.
• Required (form-type fields only) means the form cannot be submitted, if the value in any form field is required, but empty. On the form if the field is required, the “*” is placed after the label. Click on the gear icon in a form-type view to see the required checkbox. In the form view you can quickly toggle all fields as required (or not), by clicking the gear above the header. Setting this toggle on the form does not make the field required in the spreadsheet or other views.
• Type (used when the field or column is created in some views). This may be the “field name” in the Claris Pro shadow table instead of the name. This should be fixed in the future.

Step 1: Create a new Spreadsheet View

From the Views page, click on the “+ Create New View” drop-down and choose spreadsheet. You will get something like this:

This thumbnail header can change depending on the hubs that include this view. Click on the first icon (spreadsheet grid) to show a tooltip with the info about the view. The “Create from This View” icon (document with “+”) shows a drop-down menu to select: Form, List-Detail, or Kanban. Selecting one of these new views will use the same table & fields as the spreadsheet. The “…” icon is a drop-down menu as well, that allows you to: Edit View Name & Delete View. You may also edit the view name, by clicking the name below the thumbnail to select it and change as needed. From the main section of the thumbnail, mouse-over to see the Edit button, and click it to go to the view for editing.

The View name is not the same as a layout in Claris Pro. And Claris Pro layouts are not used when a table is migrated to Claris Studio (see part 2 of this series of articles).

What is the top menu of the Spreadsheet?

1. The first menu item is a drop-down that allows you to: Rename Table & Edit Description. This new table name is what will be shown if you add the shadow table to Claris Pro. The description is optional, but will add a small black circle-i next to the table name, if you add one here. TIP: rename the table so you will know what it is in Claris Pro, otherwise the name is a unique ID, but not at all descriptive!
2. The second menu item lets you add Filter conditions, to allow you to “narrow your found set” by: selecting the conditions (all or any), selecting a field, operator on that field, and the criteria for the filter. Multiple filter conditions may be set per spreadsheet.
3. The third menu item let’s you set up rules to Group your column(s). Multiple group rules may be added and once set, the table is sorted (grouped) with a summary count (of the rows in the group) as a new row in the spreadsheet. The new row cannot be edited, but has some special properties (see Add/Edit Rows and Cells, below).
4. The Sort menu item is sorting rules, but does not add rows or show counts like Group. Multiple fields (one per rule) may be added.
5. The Search box allows you to enter text & works much like Quick Find in Claris Pro. Any visible row and/or column can be searched and the cell (intersection of row and column) will highlighted if there is a match.
6. The Columns drop-down allows you to show/hide each column/field (or all fields). This only removes it from the current table view, but does not delete it. When you edit the record the hidden fields can still have data entered into them. More about row features, below!
7. Create Dashboard is the only place in any of the views that allows you to choose Charts & Summaries based on the data in your spreadsheet. A new spreadsheet with no data rows will allow you to create the dashboard, but it may need to be edited once you enter data. The created dashboard will be shown as a new view with the same related table. More about Dashboards will be detailed in this article. This menu item is not shown for Member users, only for Manager users.
8. Table Actions has a drop-down to: Import from CSV, & Download as CSV. Initial testing only allowed 50 rows to be imported and there is a “mapping” dialog to match the CSV header row to the columns/fields in your Spreadsheet. Only the same data type can be mapped.
9. The (gear icon) Density menu item allows you to change the row height: Compact, Medium, or Comfortable. This allows you to see less or more data in a row of cells based on that row density (height) for the entire spreadsheet.

You may notice these top menu items get highlighted if they have been set or selected with the “Keep” toggle. Columns will have a colored highlight, if some of the fields have been hidden, for example. You may create multiple Spreadsheet views of the same table, so perhaps you will have one that is just grouped or sorted or filtered!

Step 2: Add columns/fields

When you click the “+” at the end of the column header “row”, you may add new fields. The chart below shows the field/column/object type in Claris Studio. The notes column shows web form equivalents (if any) & options on display or validation (if any) of the object type. These are the same object types as on other views.

In the spreadsheet, when you select the object to add, you get the Configure Field dialog. Notice how this can be different depending on the object type. When you edit any column, there is a different drop-down menu to allow you to edit the configuration. The column options will show the options in the configure or edit dialogs. Options for the cells is in the next table, including: how this field/column/object type maps to Claris Pro fields, and the summary field(s) options.

This chart has some Configure Field dialogs (newly added column) and some Edit Field dialogs. The “Add Column” button in the dialog changes to “Save”, after the column has been created but now is being edited.

CHART 1

Tips:

• If you make changes in this spreadsheet for columns, you may need to Sync the table in Claris Pro once it is already a shadow table there.
• If you make a very long column/field name, it will be truncated if shown in a form-type view on a mobile devices, so test! If there is a CSS-fix later, this will not matter.
• The column/field names are in the download CSV, so keep that in mind as well.
• On the forms, if you use any of these fields, remember that you have the option of creating a label and placeholder text, so the length of the field name doesn’t matter as much. Keep the label short for form viewed on mobile devices.
• And as of this article, there is a bug in the column names, where the field/object type is shown as the field name in Claris Pro rather than the column as you named it.
• Columns may be re-arranged as well as hidden. Use drag-and-drop to rearrange, or click the column name for the drop-down menu with more features, such as moving the column.

Step 3: Add/edit Rows and Cells

There are properties on rows and cells that make this spreadsheet view really powerful. You may rearrange rows as well as columns with drag-drop. This is not sorting, but actually moving a row of data to another position on the spreadsheet.

The columns in this chart are: the field/object type, column menu, summary options, & Claris Pro field type. Some of the column menu dialogs change based on the field/object type. For example you cannot Chart by field name for long text, or by any field if you are a Member user. The summary options for the field type may be used in the column summary row, grouped-summary row, and in some dialogs that summarize the data. The Claris Pro field type shows what the constraints are on this type of field. If you print the fields in the shadow table to PDF, these are the type and formula/entry Options.

CHART 2

Edit Rows/Records

To edit the row, hover on the row number and click on the icon to the right of the number. You will get a pop-over dialog with the fields to edit, including a section for the hidden fields. In addition you can Add a New Field here:

Another option on the row is this drop-down menu:

A Special Row (Summary)

The summary row at the bottom relates to the column/field type and changes based on that type. See the 2nd chart above with the summary options on each field type. Some types don’t have a summary option, such as long text or signature. These options are the same ones used on the dashboard summary types. The ∑ symbol with the number tells you the number of visible rows, similar to Get(FoundCount) in Claris Pro. This changes as you filter the spreadsheet, but collapsed grouped-summary rows will be the number of data rows, not the number of spreadsheet rows.

Filters, Groups, & Sorting

The Filter is a way to narrow the rows of data, by setting conditions for that filter. You may have more than one filter.

There is plenty to explore, so go play. But first a hint on the groups: If you group by a column, you see that new row (basically a sub-summary row as it would be seen in Claris Pro) and clicking on the row allows you to do something fancy:

Hovering over the Count value in the grouped-summary row & clicking on the “…”, shows a dialog to: Expand group, Expand all, & Collapse all (depending if the row is collapsed or expanded).

Play around with combining filters, sorts, groups, and summary options to see what kinds of reports you can get from the “simple” spreadsheet!

Step 4: Dashboards with Charts and Summary Text

Dashboards can be created from the top menu on a spreadsheet, Create Dashboard, but only by Manager users. If you studied the column menu in the 2nd chart, you’ll see that many of the columns can be used to “Chart by…”.  Dashboards allow these objects:

The header is greyed out in this drop-down because by default, it is added to the dashboard view. You may delete the header if desired. In addition to chart and summary objects, you can add text blocks or images on the dashboard page.

Chart Types

There are three chart types that can be configured from the data in the Spreadsheet: Pie, Bar, Line. These may have different options, so study the dialogs carefully (showing Line and Pie chart dialogs):

Notice the Summary Type options. These use the same options as the summary fields in the spreadsheet (chart 2 above).

Summary objects can also be added to a dashboard view. When you add a summary object, you get a dialog like this:

The Summary Type drop-down matches the column type, so check the 2nd chart above for “summary options”. There is a sample dashboard view showing several chart and summary object when you first start using Claris Studio. Tip: do not delete the view, but use for samples of how to use these objects.

This is not a complete detailed tutorial on the spreadsheet and dashboard views in Claris Studio. Something is left to explore on your own. In addition, don’t forget to check for updates to Claris Pro & Claris Studio, as what you read here may or may not have changed!

Next up: Form View (some very cool flow actions – conditionals!), List-Detail View (a form but with all records shown, to be filtered and/or sorted, as desired), and the Kanban View (really a special arrangement of the data in the table). And perhaps we will explore a little about Claris Studio security as well!