The FileMaker Get(TotalRecordCount) Function

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Get(TotalRecordCount)
Introduced With FileMaker 7
Returns A Number Result

The Get(TotalRecordCount) function will return the sum count of all the records in the current FileMaker table as viewed by the foremost layout window. The current table will refer to the source table that is attached to the table occurrence in the current layout. The calculation will need to be unstored in order to be accurate when viewed on the layout. It will count the current users uncommitted records as well as the records that are in the index.

In some layout designs, the status area is hidden from the user. This can be done for security reasons or the developer wants to maximize the use of the screen real estate. Many times this function is used when someone is mimicking the information shown in the status area in browse mode.

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.

The FileMaker Separation Model And Run Script With Full Access

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

In a separation module situation, the user interface layouts reside in one file, the actual data resides in another file and external data sources allow the two files to work in unison (with some exceptions). One exception is the ability to run a script with the full access setting due to its dependent nature on where the script resides.

To recap, the "Run script with full access privileges" checkbox on a script is dependent upon actions within the file it resides. Executing a script to delete records within the interface file with "Run script with full access privileges" activated will not override the lack of deletion privileges within the data file for that user.

OPTION ONE
Run the delete script within the data file and have its "Run script with full access privileges" checkbox selected. The script in the interface file calls upon the data file script and then returns. Depending on the number of records affected (shown within the interface file), the user might see what is going on. Somewhat akin to when Toto pulls back the curtain on the Wizard Of Oz.

OPTION TWO
Add the ability for the user to delete records in the data file but add conditions that can only occur within a scripted process. This is done by linking the ability to delete to a TRUE calculated result. (add link where I discuss this). The most elegant way to do this is to link the calculation to a global value set by the script, either a global field that can be seen in the data file or setting a global variable within the data file.

REMEMBER: Global variables ... like the "Run script with full access privileges" setting ... are file dependent.

OPTION THREE
There are a collection of options here that can get pretty messy. One is to temporarily re-login the user with a delete privilege in the data file for the duration of the script. Another option is to NOT delete the script but flag them for deletion. Later on, a user with delete privileges or a batch script (run by FileMaker Server or a robot) actually deletes the record.

ALL METHODS BREAK THE SEPARATION MODEL MYTH
Adding in of the listed options (and likely those that I didn't list) will break the myth that separation model implementations are seamless upgrades. That whatever the coding update to a solution, all you need to do is pop in a new interface file and you are good to go.

In some cases, the developer can go onsite (even virtually) and add the needed schema changes to the LIVE data file. This can be done outside of traditional business hours, after a proper backup has been done. The "bag of hurt" method can be adding the schema choices to an empty data file and the reimporting all the data records again. Both methods have some level of upgrade risk involved and those risks should be measured properly before executing.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - 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.

Exploded FileMaker Match Fields

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).

An exploded match field is a type of multiple line match field in which the child match field used in the relationship is actually a calculated value of each character of the real child value you want to use. Let me take a shot at it from another direction. For example, say the word FileMaker in the child table is used in a separate calculation field and be broken down into something that looks like this ...

F
Fi
Fil
File
FileM
FileMa
FileMak
FileMake
FileMaker

So the real field we are using in the relationship is a divided version of the real matching information we want to use. This means the child match field is a multiple line match field. The idea is that you can begin typing in part of the information in the parent match field (say Fil) and still have a valid relation link to a whole word (such as FileMaker, File or Filler).

A multiple line or compound key field is an odd little thing in FileMaker but can be very useful in the design of many to many relationships. If a value that is separated by a carriage return in one key field matches a value in the other key field, the relationship is valid and referred to as a multiple line field. That is to say if the Parent key field contains

Red
Yellow

it will have a valid relationship to the Child Key field containing the value of Yellow or Red.

© 2012 Dwayne Wright - dwaynewright.com

Benefits Of Using Anchor / Buoy In FileMaker Relationship Design

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Here is a quick list of some of the benefit for Anchor/Buoy

HELPS DEFINE PROPER DATA MODELING
The aspect that no two base table touch directly can be helpful in understanding how each entity in your business model actually works. When you add a new buoy to an anchor, you are basically agreeing to the fact these two business elements communicate via this method.

LAYOUT CONTEXT IS FILTERED
This was the feature that got me drinking the Anchor/Buoy koolaid. In many places, you only see table occurrences that really do link (from a business perspective) to a default base table.

MAKES GREAT USE OF MULTIPLE PREDICATE RELATIONSHIPS
This is because the multiple predicate relationship can easily be identified in other areas and you don’t pick a multiple predicate relationship to use by mistake. This does require that you use the recommended naming convention.

NAMING CONVENTION ALSO INDICATES RELATIONSHIPS OTHER THAN EQUAL TO
Yeah, what I said above. One of the biggest hurdles in advanced FileMaker design is picking the correct table occurrence (and its associated relationship) to use at a given time.

=
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 Global Field Storage Options

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

The global field option is used to contain a single literal value for all records in a FileMaker table. Any field that has data stored globally has one value and it can be viewed no matter where you are within a FileMaker table.

Data in global fields can be passed from one table to another ... and not have a relationship. I have seen a least one occasion where global field data WOULD NOT pass between tables unless there was a valid relationship. The only time that I have seen it fail is when the user is working with FileMaker via a web browser using a method called Instant Web Publishing. So if you are thinking about web enabling your database, that is one thing to keep in the back of your mind.

Fields with the global option can be edited directly on a layout or via a script. Global fields work about the same as normal entry fields in that you can click inside them, cut, paste and add new data.

In FileMaker, global fields a field storage option. If I mention a global field, I mean a regular field with a global storage setting.

Fields can be accessed by opening the Define Database dialog box (from under the File menu) and then clicking the Fields tab. Click the field you want to make a global and click the options button. The third tab should say storage. Click it and you can see where the check box is for making the field a global storage field. All types of fields can be setup to be a global except for summary fields ( which usually returns a static value, just like a globally stored value does ). Some examples of data that could be in a global field

Field Name: Local Tax Rate
Data: 8.25%

Field Name: Company Logo (picture)
Data: The graphic of your company logo for letterheads and such

Usually a field with global storage is set at a particular value for use in a calculation or for use in a script. The setting of a global value can be done via end user data entry but in most cases it is done via a FileMaker script. This allows you to use a global field like a programmer uses a variable. Enter or set in a fresh value and it will immediately reflect on all records.

Finally, let’s cover how fields with global storage behave in networked FileMaker settings. In these cases the field behaves like a local global for each user. One user on the network cannot see what is in another users global field. By default, the global will contain the data it had when last closed in single user mode. The global field for a networked user will contain unique data, only for that user and only during that network session. When a networked user closes a file with global fields, none of the data in that global will be saved for the next session.

PLEASE NOTE: It’s a common technique of FileMaker developers to put a small g in front of the names of Global fields. This way you can find and identify them, when you have a large list of fields.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - 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 Anchor / Buoy Segmentation Makes Better Printing

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

In previous discussions, we have chatted about how the relationship graph TOs (table occurrences) can be grouped. We have chatted about how FileMaker Anchor / Buoy leverages TOGs (table occurrence groups) as a key element in its strategy to tame the FileMaker Relationship Graph. When you have all your table occurrences grouped in a manner such as this, it makes printing the Relationship Graph much easier.

Why would you want to print the Relationship Graph? Well, it might help you plan where you want your relationships to go and how you are going to get there. You may use this as a method to describe the interactions between modules to project stake holders. You might want to have a history of the evolution of your Relationship Graph for project management needs. You may want to print the Relationship Graph to pdf and send it to a FileMaker Consultant for their recommendations or technical support.

In the lower right corner of the Relationship Graph are the two buttons that help you setup your Relationship Graph for printing.

Page Guides - The first button in the print button group is used to turn on or off the page guides for printing the graphic area. This can be a worthwhile organization tool and is a great idea if you are going to print out the relationship graph area for a presentation or part of your overall database documentation.

Page Setup - The next and final button in the bottom button row is used to bring up the print preview screen for the relationship graph area. This can be very useful if you are going to print out the relationship graph area for a presentation or part of your overall database documentation.

ORGANIZING YOUR GRID TO A GRID
So in some ways, your Anchor / Buoy groups can be considered a grid of relationships. So the next step is to turn on the Paper Guides (via the button mentioned above) and then place your TOGs neatly into the spaces added by the grid.

So I decided to practice what I’m preaching here. I had my Relationship Graph in very good shape for my InBizness SOHO product. However, I have never made sure that it printed perfectly. This is a large task because InBizness SOHO 2.5 (the release I’m working on) has 211 table occurrences for the 45 tables it currently has. It took about 30 minutes for me to line up all those table occurrences to be in synch with the Page Guide grid. I know that many of my competitors in the FileMaker framework business market could never print all the relationships for their solution from one place and it all fits into the printable grid.

Here you can see a 50% print preview look at part of InBizness SOHO 2.5 Relationship Graph. All TOGs are sorted alphabetically, fully anchor / buoy, full consistent naming convention and now totally printer friendly.

SUMMING IT UP
The TOG setup for Anchor / Buoy makes the printing of a Relationship Graph a reasonable experience. Printing your table occurrence groups occasionally can be very beneficial for project documentation. The process of reorganizing your graph this way also gives you the opportunity to take a very close look at it. That means you can also use this time to delete unneeded TOs and rename them as necessary.
=
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.

The FileMaker FieldIDs Function

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

FieldIDs
FieldIDs( fileName ; layoutName)
All Current Versions Of FileMaker
Returns A Text Result

This design function will give you the internal FileMaker id number for all the fields on the specified layout. This will even work for related fields on the reference layout. Each internal FileMaker ID will be presented with a carriage return between each value.

As with most of the design functions, if you hard code the parameters of fileName or layout name, the results can be broken if you change the name of the file or layout afterwards.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.

FileMaker Table Occurrences And Pick Lists

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Many times in your FileMaker development, you have to pick a table occurrence for your reference point (generally referred as Context). In free form relationship design, every table occurrence is linked in one way or another. Think of it as a “Six Degrees Of Kevin Bacon” for the FileMaker developer. Anchor / Buoy does away with that by creating islands of linked table occurrences.

Here you can see where you set the relationship context for a layout.

Here you can see where you set the relationship context for a calculation.

After you choose the context, the number of items you can pick are shown. You will see table occurrences that have a relationship (based upon the context) to the current layout / calculation and what table occurrences do not have a relationship.

If you use a Spaghetti relationship design approach, you will likely see all possible table occurrences in the pick list because almost everything links to everything. If you use an Anchor/Buoy relationship design approach, this list is filtered because no two base tables touch (again because table occurrences are organized into groups or islands). So you can quickly and effectively choose just the related table occurrences you need and not choose a problematic table occurrence.

Here you can see a listing of related and unrelated table occurrences.
=
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.

Introducing Field Storage Options For FileMaker

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

As mentioned before, the dialog box for setting field options is set into the three tabs of Auto Enter, Validation and Storage. The third tab of the field options settings is for the storage of the field itself. FileMaker 7 has really opened up the options of field storage. This area allows for global storage, repeating fields and indexing. I will cover this in detail in following discussions.

QUICK REVIEW ABOUT GLOBAL FIELDS - The global field option is used to contain a single literal value for all records in a FileMaker file. Any field that has data stored globally has one value and it can be view no matter where you are in a FileMaker 7 file. By anywhere, I do mean that any global field in any table can be view from any record of any other table. Fields with the global option can be edited directly on a layout or via a script. Global fields work about the same as normal entry fields in that you can click inside them, cut, paste and add new data.

QUICK REVIEW ABOUT REPEATING FIELDS - A repeating field is any type of field ( except summary ) with a specific storage option setting. The storage option of repeating fields allows you to have multiple cells of information in the same field. As far as I know, FileMaker is the only database application that has anything like repeating fields.

QUICK REVIEW ABOUT INDEXING - FileMaker indexes focus on the data within a field. So if you have a FileMaker field indexed, FileMaker can look at it and go to the associated records quicker than it can if it is not indexed. For this reason, indexing is a key player in searches, sorts, reports and relationships.

Once again, I will cover these three storage options in detail in following discussions.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - 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 Relationship Caching And Refresh Window

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

In regards to the world of computing, the term of caching is a familiar term. In very broad terms, caching refers to a process of storing copied data in a place where it can be accessed quickly by a computer / program. This copying is done before the data is needed, in anticipation that it is going to be needed in the near future. So when the data is needed, it is gathered from the faster cache resource instead of the true place the data resides.

So let us say that FileMaker (the application) may anticipate a need from a user for record data. It may load that anticipated data into a local cache on the users computers, just in case. If the user does need the data, it is quickly available right there. Where do you think you might run across an occasion for something like this on a local level?

If you thinking of related data, we are on the same page. In a complex FileMaker solution, you might have dozens of relationships based upon the table occurrence you have associated with a layout. Those relationships likely have other relationships, that also have dependent calculations and so forth.

In some cases, FileMaker Join Caching may be working behind the scenes to give you fast and reliable data. It is possible that you might see a relationship that doesn’t seem to be accurate and you want to refresh it. In most cases, this freshen up is an update of the temporary storage cache with data from the actual resource. This is often called flushing the cache or FileMaker can call it “refreshing the join results”. You many find this command option is a number of places, most notably under the Records menu as a Refresh Window command or as an option in the Refresh Window script step.

Here you can see the Refresh Window script step with its options of cache flushing.
=
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.

The FileMaker Get(WindowName) Function

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Get(WindowName)
Introduced With FileMaker 7
Returns A Text Result

The Get(WindowName) function is used to return the name of an active FileMaker window. Normally, this would be the active window in the foreground ( like if you have a field using this function on the layout ). However it is possible to use this function in a script that might be working on another window ( when used in conjunction with the Freeze Window script step).

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.

FileMaker Anchor/Buoy, A Closer Look At Its Advantages

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

The anchor buoy system leverages that requirement to design a more layout centric approach to the organization of your relational graph. In many ways, a crafted Anchor/Buoy graph resembles the old FileMaker 6 relationship method but a more power implementation of it (particularly when leveraging more

advanced FileMaker technology).

ADVANCED RELATIONSHIP FEATURES (always good)
Some of the advanced benefits of FileMaker relationship design that works very well to both traditional relationship design and Anchor/Buoy include,

- ability to access data through a relationship to the other side (multiple hops away). Anchor/Buoy allows for a more focused use of this feature.

- relationships can now include multiple fields in their setup, commonly referred to as multiple predicates.

- increased number of relationship operators instead of just the equal to (=) setting

ADVANCED RELATIONSHIP FEATURES (that can have a bitter aftertaste)
Some of the benefits that are sometimes confusing for traditional relationship design but anchor/buoy seems to disregard include…

- relationships can be bi-directional but this can be equally confusing in traditional relationships but anchor/buoy simply ignores.

- relationship graph looks like an entity relationship diagram but it really isn’t the same thing. Anchor/Buoy design doesn’t look like any type of ERD I have seen but you still get a great visual aid in how your solutions relationships flow. In some ways, it is even better because you can focus your attention one entity/table at a time.

- context has to always be on the mind of the developer and a complex relationship graph can make that extremely challenging. Anchor/Buoy doesn’t have this problem because it is a direct translation of what you see on the graph to what you program within your solution.

- when defining your database for things like picking a field within a table, you will see all related table occurrences. In the spaghetti / spider approach, since everything is connected one way or another, choosing what you want can be very challenging. In Anchor/Buoy, you only see the relevant table occurrences and this empowers you to select the correct one.
=
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 Validation Failure Messages To Users

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

So for the sakes of recapping, there are times you want to validate the information that is put into a field. This validation is passive and doesn’t do anything if the data in the field matches the parameters you set for it’s validation. Field validation response is triggered when the data within the field falls outside of the parameters you set. The validation exception response is normally a dialog box that pops up on the screen.

Depending on the validation problem, the message may be unclear to the user. If your validation is that the field needs to have unique data, the FileMaker error dialog box tells the user that the data entered was not unique. However, if you have multiple validation settings and / or calculated validation going on, the message simply says you have a problem.

You do not have to use the FileMaker error message when validation fails. You have the ability in FileMaker to put up (a rather limited) dialog box message of your own design to the user when a fields validation fails. Chances are, you (the talented developer that I know you are) can come up with a validation failure dialog box that is more useful to your users. You cannot write an essay about the problem but you can add a brief ( 200 characters or so ) message to the user. I would suggest writing a brief message about the encountered problem and perhaps refer them to a help table (you do have a help table in your solution don't you) for further information.

All you need to do is click the option of "Display custom message if validation fails" box and then type your message in the box below this setting. Be sure to test out your new validation settings / message system before you roll it out live to your users.

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.

FileMaker Contextual Relationships And Layouts

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

So as you know, FileMaker 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

Validation Of FileMaker Data: On Entry Only Or Always

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

To keep your database a smooth running data engine, you may need to exercise control over what goes into it. In many ways, it is easier to control what a database allows a user to enter, than to educate and enforce every users data entry habits. Here we will discuss the various settings of the field validation dialog box. In total, there are eleven main choices with a refinement related choice for what to tell the user when validation fails. The eleven main choices are used to know when ( or when not ) to validate data and what validation requirements you want to set for the data.

The first section in the validation setup dialog box is labeled "Validate data in this field." Here you can select if the data in the field is validated only on data entry processes or all the time. When set to always you can even validate imported data. We cover the different aspects of import validation in more detail in a following discussion.

I should also mention that your validation is not rock solid unless you uncheck the "Allow user to override during data entry" option. When checked, users will get a warning dialog box when validation fails but they can override validation settings. There are both good sides and bad sides of this setting, no matter which way you set it.

When checked, you are allowing the user to decide if validation is important during their data entry process. In many cases, the user will make the correct validation override decision. In some cases, the user may make a poor decision about overriding validation and it could harm data integrity in the file.

Unchecked, this setting requires the user to enter in data in the form compatible to validation or revert the field back to it's previous state. They may have a valid reason to override validation but they cannot do so. Also it is possible ( although rare ) to put the end user in an endless loop of wrong answers, where they have to force quit FileMaker to get out. One final note about any of your validation settings, TEST THEM before you go live with the settings.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - 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.

You May Have To Unlearn What You Have Learned About FileMaker Relationships

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

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.

DYK: Calculating The FileMaker Else Command

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

In FileMaker, you can even use a calculation as part of an else statement. This means you can do away with a lot of lengthy nested If scripts or sequential If scripts!

Basically the Else If script step is a combination of the If and Else script steps. I wrote the following on purpose because it the actions are actually flipped (as the name implies).

The ElseIf needs to follow an IF script step (or another Else If) and it allows you to embed a calculation. If that calculation is a TRUE result (1=1 for example), then the script will perform all the script steps between that ELSE IF to a branch ending script step (such as another ELSE IF, END IF, Exit or Halt).

As I alluded to earlier, you can have many ELSE IF script steps in a scripts. This allows you to write a script that has an almost endless number of different branching actions.

Locking A FileMaker Record Via Validation

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

As you know, FileMaker can be setup to validate data that is entered into a field. You can setup the validation settings via a number of dialog box controls. When validation fails, you can even give the user a customized warning dialog box.

There is a fairly straight forward and simple way to lock a record so that the information within it CANNOT be edited. This can be done with validation in the key fields you want to protect. Validation will allow information in a record that conforms with the specified validation method. So you can create a field that is used like a light switch. If the switch is on, you are secure and changes cannot be made. If the switch is off, changes can be made.

The first time I used this technique was building a double entry accounting system. In this system, when a record was posted, the accountant did not want the money transaction information to be edited. Posting in an accounting system means that data is locked. Once locked, they are never to be edited. Adjustments have to be handles as their own credit / debit records adjusting the overall values for those accounts.

When a record was posted, information was sent to a FileMaker file called CreditDebit.FP5. If you unlocked a record, those associated records would be deleted and the record was marked as an unposted record.

The technique of field validation as part of a security scheme isn’t used that much anymore. You can do much the same thing by using a privilege set and controlling the edit capabilities via a calculation. However, you might still run across the above technique in some converted database solutions.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - 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.

The FileMaker FieldNames Function

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

FieldNames
FieldNames( fileName ; layoutName or tableName)
FMP Version: All current versions of FileMaker
Returns A Text Result

The FieldNames function gives you the name for each field on a specified layout or in a specified table. Each field name is listed on a row by itself ( in other words carriage return delimited ).

A somewhat odd and hidden feature is the ability to substitute the name of a relationship table occurrences for the name of the layout. In cases like this, FileMaker will supply you the fields in the TO.

EXAMPLE: FieldNames ( Get ( FileName ) ; Get ( LayoutName ) )
Here is a calculation that will return all the current fields on the current layout. It uses a get functions as the parameter settings for what file and layout you want. ( fyi ... make sure you have the result unstored ) The order in which the fields appear will correspond to the order in which the fields appear in the layer of each layout.

EXAMPLE: FieldNames ( Get ( FileName ) ; Get ( LayoutTableName ) )
Here is a calculation that will return all the current fields for the current table that current layout is using. It uses a get functions as the parameter settings for what file and layout you want. ( fyi ... make sure you have the result unstored ) The order in which the fields appear will correspond to the order in which the fields were created.

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.

A Couple FileMaker Relationship Design Tips

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

CREATE SELF RELATIONSHIP IN A SNAP
When in the relationship graph area, quickly drag a line from your table occurrence out ... then back to the same table occurrence and let go of the mouse button. FileMaker will attempt to create a new table occurrence for you that will allow a self relationship.

ACCESS RELATIONSHIP SETTINGS BY CLICKING THE LINE

Some relationship graphs can get a bit crowded and you cannot find the box that is used to access the control settings. Many developers may not be aware that simply clicking the line that connects two table occurrences will also bring up the manage relationship settings.

=
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.