Walking the Relationships
Example file: Splits
[How to use relationships to move between and perform operations on different sets of records during scripts.]
I don’t know what others call this, but it is a fairly common task in any kind of scripting. I think they call it “walking the lists” in AppleScript. By “walking the relationships” I mean using the power of relationships (often self-relationships) to move between defined groups of records during a script.
Each group can then be operated on independently, while preserving not only the current found set, but returning to the same record. As far as the user is concerned, all (s)he did was click a checkbox or something; several operations occurred, but the screen just blinked for a second.
A situation where this is needed is when something happens to one record, but the change then requires running an operation on several records, a set that is different from the current found records.
It’s hard to come up with specific examples. It generally occurs when you have some complexity, records tied together with self-relationships; then have another element added (or deleted) later by the user. This element is often “arbitrary,” simply up to user discretion as to when and to which records it happens.
What needs to happen is for the change that has been made to be applied to all the related records. But, in this case, they aren’t part of the found set. If you Go To Related Records and Replace the change into the related records, you’ll lose the found set.
Two simple examples of this kind of change that come to mind are, 1. a simple checkbox, and 2. user deletion or editing of the first marked record of a set. I’ll cover the first problem in this article.
Split Transactions
An example from my own files, which everyone can relate to, is a checkbook-like register for expenses (in this case for materials).
I went to some trouble to create the ability for “split transactions.” An entry, which is normally one transaction to one store, can be split, each partial amount assigned to different people (or jobs), while still remaining available as the single transaction.
The way I did this was to actually create a new record for each person added to the split, tying them together with a self-relationship using the value of the first record’s Serial ID field.
This value, auto-entered and unique in each record, was set into the second record into a separate ID field, Split ID, in both the original and its split partner.
Any subsequent split entries would use this same Split ID. A self-relationship on the Split ID ties the transaction records together. The transaction total is the Sum of the related entry amounts, =
Sum (Self-Split ID::amount).
In my file, I am not that interested in the transaction total, after it is entered. I am usually viewing the entries by client (or job*), and I am mostly concerned with how much each job is costing. The entries for that job, whole or split, show on the list view, and are totalled at the bottom of the screen.
The transaction total is only viewed in a “drop-down” view when clicking the Splits button. It’s kind of the opposite of the way checkbook registers usually show this; but it makes more sense in structural terms for what I’m doing. Each split entry is a record of its own, can be assigned to a job separately, and shows up in summaries etc..
*In my own file I have Clients and Jobs; but it added extra fields, so in the examples I just have it by Client. You could easily change it to Jobs. You’d get the client’s name through a Jobs file, reaching back to a Clients file.
I’ll cover this reaching or “chaining” in a later article. Or email me. It’s simple, but kind of off the subject.
Not a Ledger-type Structure
As I said, my structure is different from the type of register that you see in most accounting programs, which show the transaction total on the main line.
In that case, you’d need the total of the splits, the transaction, to be an indexed number, not a Sum(Relationship::number).
It comes to the same thing in the end, as the Summary of all the splits, when you Find All, is the same as the Sum of all the transactions.
It would need a slight re-write, mostly in the Set Remainder script. It would need a different layout to show the transaction total, not the amount for each client. But it’s also off the subject, which I haven’t really got to yet 🙂
Check Off
OK, not bad so far. But I also want to have a checkbox to check off entries, like in any checkbook, say for transactions transferred to some other accounting software. This is the arbitrary element. The user can click it whenever he wants.
The problem is that the other parts of the split are now separate records, usually not in the current found set. We have to go get them and check them off too.
Returning to the Same Place
The first thing to do is remember where we are, so we can return. Let’s start by storing the current record location.
First we set the Serial ID into a global Serial ID g field, for temporary storage.
Also create a relationship, named “Serial ID g,” Serial ID g = ::Serial ID.
These two fields and this relationship are needed in almost every file.
You could also use Status(CurrentRecordID), but I usually have and use my own auto-entered Serial ID.
Now we just need 1 step to return to the current record:
Go To Related Record [“Serial ID g”]
Notice that the “Show” parameter has been left off. This leaves the current found set as is.
Check It On or Off?
The other thing to do right away is find out whether it is being checked on or off. It is a “toggle,” and has to change whenever it’s clicked.
The checkbox field itself should be formatted (Field Format) as a Boolean number, with 1 for true and nothing for false (just delete everything in the little box).
It uses a value list, Checkbox, which has only one value, 1. That means it’s either checked or not, 1 or empty.
Check Button
We need to do it with a script in order to run our operation. So just format it in layout mode to be a button (Format Menu, Button), then attach this Script.
Set Field [“CheckOff”, “If (CheckOff, “”, 1)”]
[Notice that since Checkoff is a number (in fact can only be 1 or nothing, is Boolean) just seeing if it exists is enough. Don’t try this on text fields; use IsEmpty(field) instead.]
We now need to check off the other split entries for that transaction as well.
This would be done with a Replace step, after isolating those records.
Remember that the field to be Replaced must be on the current layout (in this case it is, but always check).
The script will need to know whether it’s “on” or “off” as we run the Replace, so let’s put that value into a global, CheckOff g, where it will be accessible.
Set Field [“CheckOff g”,”CheckOff”]
Exit Record/Request
Check Off Script
Now we have most of the pieces in place. Here’s the whole script:
Enter Browse Mode []
Freeze Window
Set Field [“Serial ID g”, “SerialID”]
Set Field [“CheckOff”, “If(CheckOff, “”, 1)”]
If [“Count(Self Split ID::Constant) > 1”]
Set Field [“CheckOffg”, “CheckOff”]
Exit Record/Request
Go to Related Record [Show, “Self Split ID”]
Replace [No dialog, “CheckOff”, “CheckOffg”]
Find All
Go to Related Record [“Self Serial ID g”]
End If
Notice that the first Go To Related Record step has “Show,” while the last doesn’t; also that the last is after the Sort step (which always ends up on the 1st record).
I put “Find All” so you end up with all of them and can see what’s happening.
In my own files I have it end up with the current Job’s records. All records would be a bit much. Without either of these steps you would end up with just the records on the current split. Take it out and see. It won’t hurt anything.
Check Off Many Records
The above script is limited to doing one transaction at a time. You could do multiple transactions, the entire found set, returning to the same records. But that would require more scripting.
It would need a loop that walks through the records one at a time, performing the above script, as each must be checked separately for its “split partners.”
If you need such an animal, it’s in the Splits2 example file. But you’ll have to write your own article 🙂
Splits1 vs. Splits2
I made two files, so that you can see the basics in Splits1, but also see more how a working file might look in Splits2.
Also notice the difference in the data in the Last Name fields between the two Split files (go to the Calc layout in each file). I can’t really go into detail about how and why I set up the lookup, and why there is only data in the first entry for each Client, other than that it is the most efficient way to store the transaction data without using a lot of disk space. The client’s name would also be in a Contacts-type file.
The Client ID field carries all the weight. It is in every record. With it you can access the names and keep everything properly linked. A self-relationship on it allows you to store the names only once, but still see them in every line of the list.
I will write more about it in a later article. If you read past articles on marking the first entry of a group, you will see the basic mechanism that makes it possible.
Fenton Jones
Fenton Jones is a FileMaker database designer and consultant, based in San Diego, CA. FileMaker is a cross-platform rapid-development tool for affordable relational databases. If you have need of a FileMaker Pro expert, please be sure to visit his home page at http://www.fentonjones.com
Leave a Reply
You must be logged in to post a comment.