A Lightweight Cross-Tabular Data Entry Method

One of the shortcomings of FileMaker is its inability achieve easy cross-tabular reports and data entry in a normalized structure. There are various techniques to replicate cross-tabular interfaces, but they often involve many relationships for either the columns/rows, or are for display only. The goal of this article is to present a lightweight method for cross-tabular data entry using as few relationships & fields as possible.

Disclaimer:

My apologies for the length of this article. To try and make this as easy to follow as possible I have tried to make it as clear as possible, and have tried to explain every step of the process with pictures. As a result, it turned into a bit of a monster :) Hopefully you don't switch off half way through - if you do, book mark it and come back later, or you can skip to the example file and just get stuck in :) - Daniel

What is Cross Tabulation?

Displaying data in a cross-tabular format basically means representing data in a grid 2-dimensional format. Think of a spreadsheet with rows and columns. Each cell is defined by a row number and a column number

In the image above, the rows are defined by the people, and columns are numbered 1 through 7. Each cell is a piece of information.

What is the Ideal Underlying FileMaker Structure?

The underlying format ideally should be a normalized structure of tables & records with 1 record per "cell". This is why cross-tab in FileMaker is so difficult - we can easily display the vertical component of a cross-tab with a portal, but the horizontal component is the hard part. FileMaker has no horizontal portals, and so to display the columns one must resort to other methods...

Other Structures That Should be Avoided

I have seen cross-tab been done in the past where the columns are done as repeating fields, or even individual fields. Consider a grid of 10x10. The 10 rows are done with 10 records in a list or portal which is fine. For the 10 columns, a repeating field may be built with 10 repetitions, or even 10 individual fields added to the table responsible for the rows.

This is a huge no no! Repeating fields should never be used for storing actual data in this manner, it will only lead to nightmares, cold sweats, and post repeating-field trauma in later life. Repeating fields are not good normalization structure for any database.

The Solution - use Repeating Fields

Okay, you may be scratching your head at this stage. "Didn't you just tell me NOT to use a repeating field? Won't I have nightmares if I do?" Well, yes you will, but only if you use them for Data Entry

Repeating fields are perfectly fine to use as an interface tool for displaying data, or as a means to manipulate data, just so long as they are not the source of that data! I love the repeating field so much in fact that I try to talk about them any chance I get, much to the chagrin of my colleagues :)

The rest of this article is going to show a method of using repeating fields to achieve a nice cross-tab data entry portal, in fact the same portal you saw at the beginning of this article.

Setting the Scene for What We Want to Achieve

Lets have a look at what the end result should be:

Each cell here is to be a record containing its piece of information. In this example we have 2 tables:

1. Parent - This is what constitutes each row, 1 parent record per row
2. Child - Each child record is a cell in a column.

One parent record has 7 children records, and we can assume this is established at the point the parent record is created. Children are categorized as 1 thru 7, so for any given parent we know it will have 7 children numbered 1 thru 7.

To begin, we can build a portal whose context is based on a parent table occurrence, this gives the following:

We know that each row has 7 associated children record that will need to have their value shown as columns. How can this be done?

A Common Method of Relationships & Fields

As mentioned in the abstract, one method of cross-tab production is to construct a series of relationships to each specific record for the purpose of showing it in the portal. This method is works fine when the number of child records is known, but one of its downfalls is the large number of relationships required, and the fields required to build those relationships.

Have a look at how this might look if we were to pursue this method:

In this graph you can see our home context, and the table occurrence for the parent that is showing Parent records. Related to the portal table occurrence is 7 individual child relationships, each one to a different one of the 7 children.

In order to establish a relationship to each specific and unique child record, 7 fields are required, each containing a unique identifier which lets the parent record relate to each child individually:

This is an awful lot of management required to build your cross-tab. Think of a cross-tab with 50 or 60 columns, can you imagine what the graph is going to look like, let alone the parent table with all of these interface fields!

Back to Repeating Fields as the Solution

If only we could just stick a repeating field in the portal and be done with it. This would give us a nice cross-tab appearance, as repeating fields can easily be shown horizontally. If the number of columns ever changed, then it would simply be a case of extending the # of repetitions and widening the portal, simple stuff.

Here's how we will do it....

Get ( ActiveRepetitionNumber ) & GetNthRecord

Two of FileMakers most awesomest functions. The GetNthRecord function allows us to obtain field contents from any field of any specific record through a relationship. The Get ( ActiveRepetitionNumber ) Allows us to know at any point in time what repetition number of a repeating field the user is in. More on these two functions later.

Step One: Get the children record data displaying in the portal

The first step to this method involves being able to display values from the 7 children records in the portal using a repeating field. If we can at least display the data, then this will give us a good platform for modifying that data.

We have added the repeating field to the Parent table as shown above, and given it 7 repetitions, 1 per child record. This is a calculation field so live child record information can be displayed. The calculation is:

Here you can see both the GetNthRecord and Get(CalculationRepetitionNumber) functions being used. GetNthRecord allows us to obtain a specific records value, and the record for which it is obtained is dependent upon the repetition in which each calculation is evaluated. As an example, for the 5th repetition of the field, Get(CalculationRepetitionNumber) will return 5. When this is used in conjunction with GetNthRecord, the value in record # 5 is returned.

Note that the extend function is required when referencing non-repeating fields in a repeating field calculation.

What we end up with:

The image below shows what we end up with when we put this display repeating field in the portal:

Look familiar? (It should, I've shown it 3 times now!) What we end up with is a pretty looking cross-tabular display of our data. Great. If we merely want to show this children information then we might as well stop here because we are done. But this is only half the story. We now need to be able to allow editing of these values within the cross-tab structure, and have the underlying children records be updated, and this is where things get interesting:

Step Two: Building a Repeating Field we can Modify

The problem with the current repeating field is that it is a calculation for display only, and we cannot modify the contents of a calculation. Instead, we require another repeating field that is not a calculation, so that we can enter values into it.

The first image shows the new repeating field for data entry in the Parent table. The second image shows how both repeating fields are positioned within the portal. Note that the display field is placed on top of the entry field and has a fill color so that the entry field is completely hidden from the users view. I have highlighted the entry field in green purely so you can see it.

Note also that entry into the display repeating field is disabled. This has the effect as such that when the user clicks onto it, they are in fact entering the data entry field underneath.

Step Three: Display value when clicking into entry field

Currently, if the user were to click into a data entry repetition, they would get a blank box, and it would appear that the value has disappeared. Instead, we wish for the value they see prior to entering a cell (the display value) remains upon clicking into the data entry repetition.

We will achieve this with the use of an onObjectEnter script trigger attached to the data entry repeating field:

When the user clicks into any given repeating field, we want the associated display value to show in the entry field. The first line of the script achieves this:

Here you can see the field and repetition to be set. We are setting our data entry field, and the repetition being set is the one we have just clicked our mouse into - which can be obtained using the Get ( ActiveRepetitionNumber ) function.

The value that we will set into this repetition, is the value in the corresponding repetition of the display calculation.

Still awake over there? Yes? Good! The end result of the above is that when the user clicks into a repetition, they see exactly what they saw prior to clicking. In fact, the data entry field now contains the value that is in the corresponding child record. The user is now able to type freely and modify this value to whatever they want.

Step Four: Exiting the Field & Updating the Child Record

The user has now entered the cell, modified the value in it, and is ready to exit out of the field. When they do this, we are going to need to update the associated child record with the new value. Remember that each repeating field corresponds to a specific child record.

While obtaining a specific records value using GetNthRecord was easy, going back the other way is not quite as easy. There is no SetNthRecord function, nor is there a script step to set a specific record in a relationship. So in order to achieve this we employ some other smarts.

GetNthRecord is able to obtain the value of any field from any related record. Well, we know what record we are dealing with when the user is in the data entry field, because we obtained the value put into that field in the first place, right? So why don't we grab that child records primary key value while we're at it. That way, we can establish a relationship "on the fly" to that specific record as a means to update it? Make sense? No? Keep reading...

Two new fields have been added to the Parent table. The first of these is "Active Child ID". This is a live unstored calculation that at any point in time contains the child ID (primary key) of the child record that the user currently thinks they are modifying in the cross-tab interface. It's calculation is very similar to that of the Display repeating field calcualtion, but instead of returning the child records value, it returns its primary key instead:

The purpose of zRelationship Refresh Field

You may have seen this field lurking in some of the earlier screenshots. The main purpose behind this field is to force a refresh of the relationship between the Active Child ID field, and its associated child record. The Associated Child ID field is an unstored calculation, which doesn't reference any field on the Parent table. Because of this, when it is used in a relationship, the relationship is unaware of any change to the calculations contents, and thus the relationship does not re-evaluate.

You may have read an earlier article describing this exact issue, and the proposed solution was to use a Cartesian-Join in the relationship as a means to force the refresh. This is exactly what is being used here, and is the purpose of this refresh field. You can read that article here.

When the user clicks into the data entry repetition, the last step of the script is to set our refresh field to 1, this forces a refresh of the relationship to that specific child record. Now it is correctly established, when the user leaves the entry field, we can very quickly and painlessly set the new value into that child record.

Here is script that is run upon onObjectExit of the repeating field via script trigger:

We have a direct relationship to the child record to update:

And we set it to current field contents:

Below you can see the relationship from the portal context to the child record that is being updated.

(Image)

And these are the two predicates in that relationship - the first is the direct relationship to the child being updated. The second is to force the refresh of this relationship

(Image)

Finally, you will note that the last script step is to clear the data entry field. This is because once the user leaves the data entry field, there is no need for it to contain a value, in fact if we kept the values in there, then the size of the database would get large over time. To avoid this, we clear the data entry field contents. If the user ever does click back into that field, it gets set again anyway so no harm done.

To put it all in Summary

Step One: Display the live data in display repeating field
Step Two: Build the data entry repeating field
Step Three: Script triggers to set data entry field contents
Step Four: Update record upon exiting field & tidy up after yourself.

The overall expense in terms of relationships & fields required is:

1x Portal
1x Relationship to single child record being updated
2x Repeating fields, 1 display, 1 data entry
1x refresh field (this could be worked around using scripting if you wish)
1x current Child ID field for updating child record via relationship
2x scripts onObjectEnter and onObjectExit

Overall I think the expense is pretty small for a 7 column cross-tab. using the other method would have required 7x relationships and 7x fields so this method already has that method beat in terms of cost.

But the best feature of this method is the ease at which it can be scaled. Once all of the basic components are in place, adding extra columns is a case of adding repeating fields to your data entry & display fields.

If the child records don't exist initially, then you can modify your onObjectEnter script to test for this via the relationship, and if no child record exists you can create it - it's all good!

Final Thoughts:

If you are still here reading this then well done! Give yourself a pat on the back! This was tough one to write and while I'm sure if I spent a bit more time on it I could have cut down the size of the article, hopefully it wasn't too much of a mission to get through it :) Thanks!

Example File

Please find attached an example file. This file was used for all the screenshots in this article, and is provided to help you fully understand what is going on in this article, and to let you experiment in FileMaker with this solution.

Download Example File