A Lightweight Cross-Tabular Data Entry Method
February 21, 2011 By Daniel
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:
• Parent - This is what constitutes each row, 1 parent record per row
• 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.
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
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.
Comments
Bart
22 February, 2011
Again, well done Daniel! Clean explanation of some nice slight-of-hand.
Have you tested this over a network? LAN, WAN? Seems like it light run a tad slow with many records (rows) and users. I don't think the number of repetitions would effect it as much.
Great OTB thinking!
Lorne Walton
22 February, 2011
Wow! What kind of mind must one have, to put together something like this?
Quibble: Daniel, when you say ANY value of any field, I know you mean THE value of any field, right? There can only be one value per field... except repeating fields, which we're not allowed to talk about! ;-)
Daniel Wood
22 February, 2011
Hi guys, thank you for the comments :)
Lorne, well spotted, you are exactly right, and I have changed the wording to be just field contents of any field.... To answer your question: an obsessive mind hehehe. Oh and "fur" - you are part right on that one, I probably should have put "fir" in there instead, as in Douglas Fir :)
Bart, not tested on wan or lan. If I had to put some kind of estimate on it, I would say it would be no slower than displaying that secondary portal in the example file of all children records if it were sorted. Every child record is going to need to be downloaded from database to client machine because of the display calculations primarily. If the display field was not present then it would not require downloading of each record right away (though it defeats the whole purpose of the field then...)
As for multiple users I don't think this would be an issue, once the records are downloaded the only performance hit is entry/exit of the fields which is minimal.... unless you are referring to the "rows" of the cross-tab of people, in which case its the same overhead as the child records - they would need to be downloaded.
You are welcome to test that example file on a server and let me know :)
Cheers!
Bart
22 February, 2011
I thought about doing that and will when I get some time.
The other thing I noticed is a decided "event occurrence" as one clicks into a field that's not previously been cached. IIRC, you mentioned caching all the child records at startup, but I didn't see where you were doing that here. It seems, on the other hand, that if one is "in" a field, makes a change and then clicks into another field he doesn't get the delay as such, unlike when you are not in a field and then click into one.
Daniel Wood
22 February, 2011
Hi Bart, what version of FM are you running and on what platform? This was done on OS X in FMP11A. The records would be downloaded as soon as the cross-tab is shown given all of the repeating fields used for displaying values are required to obtain the values from those records. That is just the display portion. When the user clicks into a field they are clicking into the data entry field which at this stage is empty. The script has to do a couple of set fields at this stage to populate the entry field, and to update a relationship to the child record for when it is updated when the user exits the field.
I'm not sure the exact reason for the "event occurrence" you are describing, but I wonder if it has to do with the fact that clicking direct into a field runs just one script, whereas the action of tabbing to another field from one already has the effect of running two scripts, the exit & the entry... Dunno!
Bart
22 February, 2011
We're using the same FMP specs and platform.
I realize that the display fields are cached, but it's the child values that don't seem to be until set by the the trigger scripting. Say you click into (1.1), isn't it true that via the scripting the value for the child record is now entered into cache? Doing this incurs a slight delay action while the scripting happens and the portal refreshes. Now, if you click outside the portal anywhere on the screen and then back into (1,1) there isn't the noticeable delay as before.
What I'm thinking is scripting a looping set field using the same sort of commands your triggering scripts use right at the beginning of hitting on a new parent record, probably by script triggering via OnRecordLoad. That way the portal's all set to go for that record.
I'll have to play around with it a little, but maybe my thinking isn't correct ?!?! Dunno. It's this delay that I've seen, brief that it may be, that might compound in a more robust environment.
Hope that's a little clearer, Daniel.
Bart
22 February, 2011
Aw, heck, Daniel. You're already throwing those "values" into cache with your second child value portal. Forget my above post.
My guess is that it's more to do with my MacBook Pro specs that your technique. Can't wait for the new ones to arrive this weekend. It's time for me to update! I keep thinking the more I upgrade my equipment, the more I upgrade my mind. Hmm...doesn't seem to work that way however! :D
HOnza
22 February, 2011
Really cool. I haven't thought how script triggers can innovate the technique I published 4 years ago (http://www.24usoftware.com/Portals). Thanks! Reminds me how cool it is to look back to my past work when new technologies appear and think how I would have done that past work having the today's technologies in hands...
Andreas
04 March, 2011
Very nice technique.
Suppose one of the child records is deleted. Can you propose a way to maintain the order of the following child records from the same parent, i.e. preserving the gap?
Janet
30 March, 2011
Daniel, thanks for sharing. I've incorporated your method into weekly payroll layout which will greatly simplify the relationships needed to summarize data and create reports. I did make a small adjustment in my 'Leave the CrossTab Repeating Field' script to check if the value in the child record is equal to the result of get (activefieldcontents). If they are already equal, I skip the update of the child value. Otherwise, the value is updated even if you are simply tabbing through the columns.
Chris
20 April, 2011
Daniel, just came across this jem! I haven't looked at it closely but how would I create totals or subsummary or summaries?
Gary
19 October, 2011
I love the simplicity of your solution, but have noticed one problem. The child records are displayed in the order they were created, which does not allow column headings to represents a relationship to the child record being displayed.
Caroline
17 February, 2012
This is just the prefect example of what I need. I don't understand it 100% but i will do everything it takes :-) thanks !!
Jonathan
23 February, 2012
This is an easy to implement structure with a very well explained process. Thank you!
What would happen if the columns were based on text entered into another table with an unknown number of records?
Instead of 1 to 7, say that it is Monday to Friday. This would mean there are 5 columns. Data is entered. Then one day Saturday needs to be added to the columns. now there are 6 columns needed. Can this method be adapted to table as source for both row and columns instead of just row?
Caroline
23 February, 2012
It's not exactly what I need after all. I must follow Jonathan's question. I need the columns to be flexible.
Daniel Wood
24 February, 2012
Hi Jonathan & Caroline. In the example file the columns are the 7 related records for each of the parent records (the rows). The relationship in the example file that determines which child record goes in which column is the one "zParent to Child for Calculations" which in the example is unsorted, so the first related record goes in column1, the second in column2 and so on.
However that is not to say you cannot sort this relationship to change the order of the child records as they appear in the cross-tab display.
For example if you wanted to display your child records monday to friday then you would need to sort your relationship so that the first child record is the monday record, the second the tuesday and so on. You could do this by having a calc field on the child record that contained the day number, and then sort the above relationship by this number. Alternatively you could create a value list of day names, and set your relationship to sort by dayname, and then instead of ascending/descending, you could use the option to reorder based on the order of a value list of day names....
Hope this helps!
michael
06 April, 2012
Hi
I'm trying to create a weekly monday to friday timetable layout detailing a set of rooms, what activity is happening in each and who is in each (5 people max).
Your solution seems very elegant. However I'm struggling to work out an easy way to populate the grid in the first place. If for some reason there is a missing record e.g. Room 2 (parent) and Tues (child) then everything just gets shuffled left and the last day (Friday) ends up with a question mark. I know there must be an easy answer to this, but is there a robust way to make the grid work when the children don't all exist. I mean could you have a record for only Monday and Wednesday in a particular room and have them appear in columns one and 3 and make is so that when you select column 2 for instance you create the Tuesday child record?
Daniel Wood
10 April, 2012
Hi Michael. For the situation you are describing, the method in this article is probably not the easiest way to achieve what you are after. If you are after a quicker implementation of what you want then the best way may be to establish 7 separate relationships from parent to child (via parent ID = child::parent ID) but with an extra predicate for each that is the day of week. Given the # of days in a week is fixed then you aren't going to require more than the 7 relationships. The extra predicate in the realtionships would be from a constant field containing a week day number/name, to the weekday number/name field in the child table. Turn on the option to create records via the relationship, and then place the fields from the 7 child relationships in your portal. This way you can easily create the records by just typing a value into one of the 7 child relationships.
I don't doubt that this crosstab example could be adapted to do what you are after, but you should always look for the simplest solution to a problem and I think in this case going with the 7 relationships is probably going to be easier - and you are guaranteed that you won't need more than 7 unless a comet hits the earth and affects the earths rate of spin and we end up having to have 10 days a week ;)
Michael
10 April, 2012
Thanks for the advice. Now you put it like that it seems obvious! But it wasn't before, at least to me. That's a great time saver.
Something to say?