FILEMAKER: Spaghetti Relationship Design Approach

From Dwayne Wright - Certified FileMaker 9 Developer
www.dwaynewright.com
info@dwaynewright.com
TWITTER: dwaynewright

One of the common names to the organization (or lack thereof) of a relationship graph is spaghetti or spider. This is in when a method other than anchor/buoy or squid is implemented. Here is a little background for our later discussions on anchor/buoy.

FileMaker 6 and earlier versions had a very straight forward relationship setup. In fact, there was little reason to graph FileMaker relationships inside of a FileMaker file because it was simply a “when this equals that” task. You could only see the information from the current table to another table (most likely stored within another file).

BTW... I’m not suggesting FileMaker 6 databases didn’t need to be organized in an ERD or external graphing system. It just didn’t make sense to have the graph inside of the FileMaker file.

Then FileMaker 7 came out and our ability to have multiple tables within the same file literally blew the mind of the FileMaker community. Then everyone had to deal with a learning process in regards to the setup of relationships between these tables. To make matters better and worse, relationships can cascade from one table through any number of other tables until reaching a final destination in another table. Once again, to make matters better/worse is the fact that the relationship can now flow in both directions.

In most cases, any developer that converted a major solution from FileMaker 6 to FileMaker 7, were shocked to see the relationship graph for those files. There is no automated organization tools to represent relationship data and the overall effect can be overwhelming.

In typical FileMaker fashion, developers and users alike jumped in with both feet. In many cases, the below representation of the relationship graph was not that uncommon. This structure (or lack thereof) of a relationship graph are known by a number of names including spaghetti, spider and countless other less that complimentary terms
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
====================== ADVERTISEMENT ==============================
To check out the online FileMaker Crosswords, please visit http://www.dwaynewright.com/crossword.html
===================================================================

FILEMAKER: Contextual Relationships And Layouts

From Dwayne Wright - Certified FileMaker 9 Developer
www.dwaynewright.com
info@dwaynewright.com
TWITTER: dwaynewright

So as you know, FileMaker 7 and beyond has the ability to have multiple tables within a file. This increased capability does have one slightly bitter after taste. Since you can have multiple tables in a file, FileMaker constantly has to know what table you are addressing at the time you do many common things, such as ….

- what layout does the table belong to
- what table(s) do a calculation elements refer to
- what table(s) are touched during the various stages of script execution
- what table(s) are needed for dynamic value lists
- and the list goes on and on

For many of these questions that are not layout specific, still come back to the current layout and how it is setup.

Here you can see in the middle of the layout setup dialog box where you choose what table occurrence you are attaching the layout to.

FileMaker will use the linked table occurrence for all the fields that are on it and scripts that are running on it at that time. The common term for this is table context.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
====================== ADVERTISEMENT ==============================
To check out the online FileMaker Crosswords, please visit http://www.dwaynewright.com/crossword.html
===================================================================

EXAMPLE: Flag Duplicates Via A Relationship

From Dwayne Wright - Certified FileMaker 9 Developer
www.dwaynewright.com
info@dwaynewright.com
TWITTER: dwaynewright

I recently uploaded an example where you could detect duplicates by running a looping script. EXAMPLE: Delete Duplicates Via A Loop

Now, there is a newer technique that I like much more and it involves some relationship setting. In this example, I setup to self relationships in which one of the relationships shows all the records in the table. I do this simply so you can see a portal from the main example layout and see the original and duplicate entries.

The second relationship is defined by what I consider a duplicate and in this case it is the name of a song. A more classic example of a field that you might be checking on duplicates could be name, email address, zipcode, product name, etc...

Then I add a field that identifies the record if it is a duplicate or an original. Here I am looking to see (via the self relationship) if I have more than one matching record in the file. If I do, the one with the lowest primary key value is the original and the other is a duplicate.

Case(
Record_ID = TableA 2::Record_ID, "Original", "Duplicate")

The idea is that if the current record ID matches the related one, we are the first record with that song name. This is because we did not sort our relationship, so our TableA 2::Record_ID is always going to show us the first record created.

An example file can be downloaded at ...
http://www.dwaynewright.com/blogfiles08/Flag_Dups.zip

Here are some links to other posts that might be of interest in regards to this topic...
Self Relations, Self Joins Or Self Reference
Universal Or Constant Relationship
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
====================== ADVERTISEMENT ==============================
Click Here To See The FileMaker Book (via a blog) homepage!
===================================================================

FILEMAKER: Data Tunneling Via Relationships

From Dwayne Wright
Certified FileMaker 9 Developer
www.dwaynewright.com
info@dwaynewright.com

Data tunneling is a term often referred to when a FileMaker table pulls data from another table, through a relationship that goes through one or more other tables. A join table is a classic example of data tunneling but there are tunnels that do not include a join table.

(click picture to enlarge)

Here you can see four table occurrences in a row, this is a good example of a drilling possibility. From the far left table occurrence, I can see all the clients that have purchased the product (from the far right table occurrence). Thing is the primary key data doesn’t exist in the other table. So you cannot find an inventory field in the client table ... or ... a client field in the inventory table. The relationship match for the portals flows through the invoice and invoice line item tables.

(click picture to enlarge) (by the way, this is purely example data)

From here, I can see the names of the customers that have purchased a product and you notice there are no duplicates (although many of these clients have purchased this product many times).
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
====================== ADVERTISEMENT ==============================
For more information on the Virtual One On One Training, please visit http://www.dwaynewright.com/training.html
===================================================================

FILEMAKER: You May Have To Unlearn What You Have Learned

From Dwayne Wright
Certified FileMaker 9 Developer
www.dwaynewright.com
info@dwaynewright.com

The saying of ... "Everyone wants to go to heaven but no one wants to die" might apply to some FileMaker developers in regards to the FileMaker upgrade. Everyone wants a significantly more powerful FileMaker application but they can get a little frustrated to have to learn a bunch of new stuff!

FileMaker is a fantastic database program and that is probably preaching to the choir. No debate from this guy what so ever! Now that I have that little disclaimer out of the way, FileMaker relationship graph can be a little tricky for the person that may have mastered or was on the way to mastering FileMaker 6. The multiple tables in one file is one of those areas, that looks very easy on the surface. However, it has a few twists and turns that can take some getting used to.

Things like lookups, portals, layouts, calculations and value lists can get a little weird due to a thing called relationship context and generally is affected by the current layout. A layout can be linked to some relationships but not related to others. FileMaker 6 didn’t have any relationship restrictions based upon the layout you were using.

I am about to publish a series of discussions on a design method called Anchor/Buoy. This is not a method that I have seen discussed in the FileMaker manual or online help system. However, it is not a hidden secret either. It is (for lack of a better description) one way you can go in your relationship graph. For me, it is the only way to go. So you are warned that I might try to sell you on the idea that I have purchased myself.

Thing is, it might require you to “Unlearn Some Of What You Have Learned”. I ask that you have an open mind because this method can be very powerful of cleaning up relationship clutter that can occur in a complex database.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

FILEMAKER: Purchase Order Number Is Not A Key Field

From Dwayne Wright
Certified FileMaker 9 Developer
www.dwaynewright.com
info@dwaynewright.com

I have done this a number of times in the past but thought I would take a moment to document it here. I’ve designed dozens of purchase order modules for FileMaker users of the years. One thing that commonly identifies a purchase order is the purchase order number. This number needs to be unique to each order, just like an invoice number is unique to an invoice. The aspect that it has to be unique for each record makes it an attractive option for making the purchase order number field my primary key field.

Thing is, some customers want the purchase order to convey some sort of information about the order itself. So the coding of the purchase order number might include a date value, a vendor id, the person placing the order, the location the order is going to and what not. Although unlikely, it is possible that we might get some sort of duplicate purchase order number. Also, some customers want to be able to edit the purchase order number and it is their database solution. As their developer, I have to be able to bend to their will but still keep the integrity of the data.

I cannot allow my customers to change key data unless it is to fix a problem (like an import gone horribly awry). Even then, the answer is no, no, no, maybe, no, no and then finally OK.

To allow a custom to noodle with some unique (like a po number), I tend to use an “under the hood” incremental field as my primary key field and don’t use the purchase order number in any relationship setup. The same rule would follow for invoice number, production job number, requisition numbers and the like.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
====================== ADVERTISEMENT ==============================
Click Here To See The FileMaker Book (via a blog) homepage!
===================================================================

EXAMPLE: Each Field Is A Record

From Dwayne Wright
Certified FileMaker 9 Developer
www.dwaynewright.com
info@dwaynewright.com

There may come an occasion when you need to take data that is in a field and make it a related record. Let's say that you have a half dozen or so fields setup for phone numbers. Now you want to move all the phone numbers into a related table. In this example, we have two tables called Parent Table and Child Table. Our goal is to take the 6 data fields in a Parent Table record and make an individual related record for each in the Child Table.


This is done via a combination of techniques including concantentation, parsing, create new record, status function and a looping script.

SETTING OUR BIG TEXT FIELD
Our first step is to set one text field equal to all the contents of the fields in the tab order and separate each value with a carriage return.

Here at the script steps for this process with a brief description of what we are doing.

Set Field [ Big Text , “”]
This is to initialize the text field for our script by setting it to empty.

Go To Field [ Field A ]
This is our first field in the tab order.

Set Field [ Big Text, Status(CurrentFieldContents ]
This sets the big text field equal to the first field that our cursor is within.

Set Field [ Counter, 1 ]
The counter is a global number field. We will be counting each field we enter into. This is used to compare how many fields we have been in ... to how many fields we want to be in. When they match, we exit the script.

Loop
The process between the Loop and End Loop will repeat until we achieve the requirements of the Exit Loop If script step.

Set Field [ Big Text & "¶" & Status(CurrentFieldContents)]
Here we are setting the Big Text field equal to itself plus a carriage return ( for the new value) and the contents of the current field.

Set Field [ Counter, Counter + 1 ]
This is used to set the counter equal to itself plus one. This is how we count each field that we do in the loop.

Exit Loop If [ Counter = 6 ]
In this example, we want to do 6 fields in the tab order. If you wanted to do 148, you would set it to 148. FYI... make sure your tab order is set correctly before running the script.

End Loop
Each Loop script step requires an End Loop script step as a matter of proper syntax.

SETTING OUR RELATED RECORDS
Now that we have our big text field ready, we need to create our related values. This is done by setting up a related portal with the Auto Create Related Records option checked. This means the last row in the portal will always be empty, waiting for you to create a related record.

Our script to creating the related records will include the following steps.

Loop
The process between the Loop and End Loop will repeat until we achieve the requirements of the Exit Loop If script step.

Go To Portal Row [ Last ]
This takes us to the last portal row, which is ready for us to create a related record within.

Set Field [ Info, Case( PatternCount(Big Text, "¶") ≥ 1, Left(Big Text, Position(Big Text, "¶", 1, 1)),Big Text)]
Here we are setting the field in the newly created related record equal to the top row of the Big Text field. We do this by looking for the first carriage return and setting the related field equal to everything left of the carriage return. If there is no carriage return left ( which will be the case on the last piece of information ), we set the field equal to the big text field.

Set Field [ Big Text, Case( PatternCount(Big Text, "¶") ≥ 1, Right(Big Text, Length(Big Text) - Position(Big Text, "¶", 1, 1)), "")
Here we are slicing the top row off of the Big Text field because we have already placed it into a related record. We do this by seeing where the first carriage return occurs and setting the field equal to everything to the right of it. If there is no carriage return left ( which will be the case on the last piece of information ), we set the field equal to empty.

Exit Loop If [ IsEmpty( Big Text ) ]
When we have emptied the field, we want to loop to stop.

End Loop
Each Loop script step requires an End Loop script step as a matter of proper syntax.

An example file can be downloaded at ...
http://www.dwaynewright.com/blogfiles08/FieldsToRelations.zip
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

EXAMPLE: Incrementing Check Numbers

From Dwayne Wright
Certified FileMaker 9 Developer
www.dwaynewright.com
info@dwaynewright.com

This example shows how you can have an incremental check number for multiple accounts. We have one table here to hold an account, in the example we have 3 banks entered. In the second table, we have the transactions of check payments.


The account table calculates what the next check number will be by using a relationship between the account and transaction tables. The account table can see it’s highest related check value and then add a 1 to it.

I added a small script to enter in new check number, via a pop up window. This allows you to use a submission type process for data entry. You can write all types of error checking or event logging off of the submit button. A submission type process makes it easier to react to any special needs your database may have ... in regards to submitted data.

An example file can be downloaded at ...
http://www.dwaynewright.com/blogfiles08/check_numbers.zip
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

====================== ADVERTISEMENT ==============================
For more information on the Virtual One On One Training, please visit http://www.dwaynewright.com/training.html
===================================================================

EXAMPLE: Simple GTRR

From Dwayne Wright
Certified FileMaker 9 Developer
www.dwaynewright.com
info@dwaynewright.com

This example is a very simple illustration of the Go To Related Record ( otherwise known as GTRR ) capability in FileMaker 7. In the example, we have two tables Departments and People. The parent table is department and the default layout shows a portal for all the people in that department. You can go from record to record to view different department records and the people assigned to them.


In each portal row are two buttons in red text, the first is GTRR and the second is GTRRS. The first button "GTRR ( go to related record)" takes you to the related child record matching that portal row. We did not write a script for the GTRR button because we wanted to show you that you can define GTRR as a button action and a script may not be necessary. The button action goes to the related record and brings the related record up in it's own window ( a new FileMaker 7 feature !).

The second button is labeled GTRRS and is for Go To Related Record Scripted. Here we do almost the same thing as the GTRR button but we used a script. We did this because we want the button to do more than than just the GTRR. In this case, we have an additional script step that hides the status area on the left side, making the pop up window look more like a dialog box.

An example file can be downloaded at ...
http://www.dwaynewright.com/blogfiles08/gtrr.zip

Here are some links to other posts that might be of interest in regards to this topic...
Go To Related Record Script Step
Relationships In Action
EXAMPLE: Portal Row Buttons
Introducing GTRR
EXAMPLE: GTRR And Printing
EXAMPLE: Simple GTRR
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
====================== ADVERTISEMENT ==============================
Feel free to send me an email about your conversion needs today! info@dwaynewright.com
===================================================================

EXAMPLE: Simple Lookup

From Dwayne Wright
Certified FileMaker 9 Developer
www.dwaynewright.com
info@dwaynewright.com

FileMaker has the ability to copy related information from one table to another and this process is called a lookup. In this example, we have set a relationship between a table called people ( used in the example layout ) and a table called zip_address. The relationship between the two tables uses the zip code field.


The zip_address table has about 1700 records that match various zip codes in the United States. This table also includes the city and state information associated to that zip code. So what you can do is create a new record and enter in the data in each field. You can move from field to field by using the TAB key on your keyboard. After you enter in the street address, FileMaker jumps past the city and state fields to the zip code field. A pop up list comes up and you pick the zip code you want to use. FileMaker then looks at the associated zip code in the zip_code file and automatically fills in the city and state fields. So you don't have to spend time entering that data in and you know that the data matches the correct data in the zip code field.

FYI... the zip_code file contains an older version of Illinois zip codes and hasn’t been updated in years. It is probably accurate enough for example work but I wouldn't use in in a production database.

An example file can be downloaded at ...
http://www.dwaynewright.com/blogfiles08/simple_lookup.zip

Here are some links to other posts that might be of interest in regards to this topic...
Auto Enter: Lookups
Layout Design To Increase Performance (potentially)
About Flat File Databases
You Cannot Lookup A Field Without Access To It
Relookup
Lookups Explored
The Ups And Downs Of Lookups?
Relookup Field Contents Script Step
Relookups In A Found Set
EXAMPLE: Relookups And Alternate Methods
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

====================== ADVERTISEMENT ==============================
Come on by and give our "no charge" FileMaker 6 training video collection a visit at http://www.dwaynewright.com/movies.html
===================================================================

FILEMAKER: Move / Resize Table Occurrences Without A Mouse

From Dwayne Wright
Certified FileMaker 9 Developer
www.dwaynewright.com
info@dwaynewright.com

In the relationship graph area, you typically will move a table occurrence by using the mouse. The mouse allows you to click / drag a whole table occurrence to move it. You can also move a selected table occurrence up/down/left/right one pixel at a time by using control key with the associated arrow key.

Control & Right Arrow - moves the table occurrence one pixel to the right
Control & Left Arrow - moves the table occurrence one pixel to the left
Control & Up Arrow - moves the table occurrence one pixel to the top
Control & Down Arrow - moves the table occurrence one pixel to the bottom

A variation of this is possible using the control / shift / arrow keys. For example you can select a table occurrence, hold down the control & shift keys, then click the down arrow key to make the table occurrence expand vertically.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

====================== ADVERTISEMENT ==============================
Want to take a document from your scanner right into your database? Want the ability to have email in and out capability without using your email application. You can features like this and more! Send me an email, info@dwaynewright.com
===================================================================