A Lightweight Cross-Tabular Data Entry Method
By Daniel Wood, 21 February 2011
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.
(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.
Something to say? Post a comment...
Comments
Daniel Wood 28/01/2019 9:20am (6 years ago)
Cheers Dee Dee, glad you enjoyed it :) Yup repeating fields have usages, just not for storing data, however that is what they were originally intended for - now we store related data in tables which is the way it should be done.
Dee Dee 27/01/2019 6:48pm (6 years ago)
AWESOME!!!! Thank you so much for this!
Back in the day, I used Paradox (DOS) to output beautiful cross tab reports. Alas, those days are gone.
You captured me with your dislike/like for using repeating fields. Surely, if used properly (as per your example), perhaps this is what repeating fields were intended for?
Thank you again. I have not read the entire article yet, but just the first few paragraphs caught me. I'm so happy!
Russ 03/08/2016 2:39pm (8 years ago)
Hi
I came across this article in the Filemaker Community and it explains what I am trying to do but since I am a novice with FM, I am having difficulty following along. I guess it's like instructing a pilot to take off, fly in a circle and land. That would be 3 steps. But if the person never flew a plane before, it would be a lot more steps. That's me!
Would I be able to get more help with this even for a fee?
Thanks!
Russ
Dave 15/07/2015 6:01am (9 years ago)
This how-to would be so much better (for me) if it were done in a video. This is more complex than you realize, for us mere mortals.
Dave 13/03/2015 6:55am (10 years ago)
I am looking at using this concept on building a weekly timesheet system for about 40 employees. I scanned over the middle part of the article. Is there a limit to the number of "rows"?
In my scenario, I'd use the columns for Sun-Sat (7 columns, like yours). The "data" in the middle of the matrix would be hours. The rows would be charge-accounts ("I need to charge 5 hours of labor to the XYZ account.")
The other variable in my scenario, is each of these Data Entry forms would belong to an individual employee. He/she would fill one out per week, then next week start fresh with a new one.
Kevin 12/12/2014 2:09am (10 years ago)
Thanks for this information! It took me a long time grasp the concept, but I have it.
I just wanted to know how would I set this crosstab up to where when I click on a new record, It creates a brand new table?
I would like to have multiple tables for a pricing matrix with different products. Like one would be screen printing on light shirts based on quantity (parent) and the amount of colors (8) (child). Another table would be based on dark colors, and another would be based on embroidery and the amount of stitches.
Is this possible with this method?
Joe 03/06/2014 3:53am (10 years ago)
Michael, this was very helpful to me and it works great on my application. I only needed the display part and not the ability to modify the values so I only used the first part of your example. The question I have is how do you limit the results of the repeating via some sort of filter? Instead of showing every record I would like to filter the results to a specific matching value on a separate field. For example, I have 3 furniture finish options (Wood, Fabric and Metal) for each of those I have 6 to 30 additional options (i.e. Wood colors: chocolate, white, black... Metal colors: black, silver, white...) So I want to show the 6 WOOD colors only and not the Metal or Fabric colors. I hope this is clear.
Tom 12/11/2013 9:23pm (11 years ago)
This works great with text. I am having trouble getting the actual Cross Tab Display field to display a repeating container with images. It displays on the file name. On click of the displayed it shows the image, but moving fields shows the file name again.
Michael 10/04/2012 10:00am (13 years ago)
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.
Daniel Wood 10/04/2012 9:37am (13 years ago)
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 06/04/2012 4:24pm (13 years ago)
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 24/02/2012 9:58am (13 years ago)
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!
Caroline 23/02/2012 11:59pm (13 years ago)
It's not exactly what I need after all. I must follow Jonathan's question. I need the columns to be flexible.
Jonathan 23/02/2012 10:26am (13 years ago)
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 17/02/2012 11:30pm (13 years ago)
This is just the prefect example of what I need. I don't understand it 100% but i will do everything it takes :-) thanks !!
Gary 19/10/2011 5:05pm (13 years ago)
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.
Chris 20/04/2011 1:04am (14 years ago)
Daniel, just came across this jem! I haven't looked at it closely but how would I create totals or subsummary or summaries?
Janet 30/03/2011 9:24am (14 years ago)
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.
Andreas 04/03/2011 10:54pm (14 years ago)
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?
HOnza 22/02/2011 10:55am (14 years ago)
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...
Bart 22/02/2011 9:56am (14 years ago)
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
Bart 22/02/2011 9:48am (14 years ago)
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.
Daniel Wood 22/02/2011 9:33am (14 years ago)
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/02/2011 8:56am (14 years ago)
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/02/2011 8:08am (14 years ago)
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!
1 2 next »
No one has commented on this page yet.
RSS feed for comments on this page | RSS feed for all comments