Search Project Mgmt
Search FileMaker Blogs

Thank you for visiting the FileMaker Thoughts blog. I recently moved this content over from my blogger account. Hope you like it! When you get a chance, check out the centralized search feature for all the FileMaker blogs found along the right side panel. It is quite handy!


Saturday
Oct252014

The Strange But Possibly Useful FileMaker Relationinfo Function

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

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

Relationinfo
Relationinfo( fileName ; tableName)
Introduced With FileMaker 7
Returns A Text Result

The Relationinfo function will allow you to return information about table occurrences and interlocking relationships. You need to specify within the syntax of the function which file and which table. The function will return 4 lines of information for each relationship to the parameter defined table occurrence. The first line returned is the file name, the second line will have the name of the child table occurrence, the third row will describe any relationship options that are set such as create related, sorting and deleting and the last row will tell you about the match fields used in the relationship.

Each different relationship will be returned in the list separated by double carriage returns.

Here are some examples returned using this function in the database I use to store my blog posts.

The syntax of my calculation is ...

RelationInfo ( Get ( FileName ) ; "Blog" )

and the returned results include ...

Source:Blog Stuff
Table:blog_Chapter_ValueList
Options:Create
blog_Chapter_ValueList::Chapter = Blog::Chapter

Source:Blog Stuff
Table:blog_Crosswords
Options:Create
blog_Crosswords::fk_blogLink = Blog::pk_recordID

Source:Blog Stuff
Table:blog_TERMS
Options:Create
blog_TERMS::fk_blogID = Blog::pk_recordID

Source:Blog Stuff
Table:blog_QUIZ
Options:Create
blog_QUIZ::fk_Blog_Id = Blog::pk_recordID

Source:Blogz_IMAGES
Table:blog_IMAGES
Options:Create
blog_IMAGES::pk_recordID = Blog::pk_recordID

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

© 2010 - Dwayne Wright - dwaynewright.com

Wednesday
Oct222014

The Unequal (non equijoin) Comparison Relationship In FileMaker

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

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

The official term is non equijoin but unequal sounds a little more descriptive to me. In FileMaker, the define relationship dialog box got a nice boost. You can have a choice of operators other than equal to ... for example you can have ...

≠ ( not equal )
> ( greater than )
< ( less than )
≥ ( greater than or equal to )
≤ ( less than or equal to )
x ( means all records in both table occurrence compare, no matter the data in either )


FileMaker allows you to create relationships that comprise of more than one comparison layer. This can be very handy when building date range related comparison using the new greater than and less than operators. Say you have two global date fields, one called start date and one called end date. One layer of the relationship could be greater than start date and that would normally return a set of related records. Add to this a second layer of less than end date and you have a range of related records!

You could add yet another layer that could say, day ≠ ( not equal ) Saturday and add another layer to say day ≠ ( not equal ) Sunday ... and ... the returning related records would be week days!

Here is an example of a multiple layer compare in action!
=
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.

Tuesday
Oct212014

FileMaker Calculations No Longer Say “No Comment”

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

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

FileMaker isn’t that great at internal documentation about schema (calculations, scripts and relationships). It is one of the key areas that I hope FileMaker will continue to approve upon as each new version comes out. In all fairness, I think they have made incremental progress in the area. In FileMaker 8, the good folks at FileMaker added something akin to a sticky note to document relationships internally. In FileMaker 7, FileMaker added a method to tag text strings as comments within calculations.


If you are like most FileMaker developers, you don’t document your code. That is too back because (at some future time) you will likely look at a complex calculation you created and stand agape in bewilderment. If you are inheriting a complex FileMaker project from another developer, schema comments can be a real life saver! This can be used as a reference to what the calculation does, just in case you need to come back to it weeks, months or even years later.

So, enough preaching in this discussion and I apologize for letting a small bit of my passion to peek out on the subject.

Comments within a calculation can be ...

- a reference to what purpose a calculation serves
- a reference to details about how a calculation does, what is does
- a reference to when it was added or modified (and by whom)
- a record to show your previous calculation and the new one

With FileMaker 7 and higher, you can now place comments within a calculation. In fact, 2 different methods are supported.

/* Here is a text comment */
// Here is a text comment ¶

THE FIRST METHOD
The first example is used to show where the comment starts and where it end. The comment starts at the /* characters and ends at the */ character. This is most commonly used when commenting out a large calculation. This can be a calculation that you have that almost works but you want to start over fresh. You can put a few carriage returns at the top, enter in your new calculation, add a comment that the below calculation is an older version and finally comment out the entire old calculation.

So after it is done, you have your new calculation (working), your old calculation (disabled) and comments about the particulars!

THE SECOND METHOD
This is a quicker method of commenting, allowing you to preface a single row of a calculation with // and FileMaker will ignore that entire row. This is used primarily for quick notes a developer may make to himself/herself.

THE FILEMAKER 6 WORK AROUNDS .. Just in case you were curious, here is the scoop on the older FileMaker 6 and earlier workarounds for internal script commenting. There is no reason to use this method now but you might come across this when you are working with a converted FileMaker 6 solution.

Use a left or right text function that has it’s character count set to zero. This allows you to hide a text string inside a function that cannot calculate anything.

Left ( “ this calculation does blah, blah, blah”, 0 )
Right ( “ this calculation does blah, blah, blah”, 0 )

Another option is to use FileMaker External function capability, which is less confusing. Only a small percentage of all your calculations will use External functions unless you are extensively using plug-ins. So an external function like the one I show below might be a better option for you.

External("CALC COMMENT", "Put in a comment here!")

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

Monday
Oct202014

FileMaker's Allow User Abort Script Step

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

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

This is one of the more important script steps in regards to running a script in a highly controlled manner. The Allow User Abort script step either allows or does not allow the end user to cancel out of a script that is current running.


During the development process, you will likely want to allow the ability to abort a script. During the times of regular operation, there are times that the integrity of the database can be damaged if a script is not allowed to run it's complete course.

FileMaker has a keyboard shortcut for both Macintosh and Windows machines to get you out of a running script. Depending on what happens if a script gets terminated sometime after it started, this step allows you to control if the end user can or cannot cancel the running script.

There are also scripts that pause for user interaction, like entering in key data before the remainder of a script runs. It’s possible that the integrity of the information within the database might be harmed is the script is not allowed to fully carry out it’s task.

FYI... If you have a long script that you will not allow the user to exit from, warn them first. The best way to do this is using another FileMaker script step called Show Message. You can warn the user that the script may take a long time to run and allow them to abort it's execution BEFORE it gets started.

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

Wednesday
Oct152014

Introducing FileMaker Field Data Validation

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

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

How would you like to have a data entry monitor? Wouldn't it be convenient to have an invisible assistant barking at any person entering data in a problematic way? If this sounds too good to be true (aside from a barking assistant) keep reading. I will discuss how you can actually setup up your FileMaker database to perform a data "quality-control" function. How? The key word is: Validation! A well implemented and designed validation scheme can help in searches, sorts, indexing, relationship setting and reporting. When data is uniform, it is easier to use and understand. In addition, it is much more efficient to set up validation options in the beginning and ensure clean data than it is to clean up hundreds or thousands of records after the fact.

On the flip side, you do need to think about your validation settings before implementing them. If you don’t know all the possible data entry variations that need to go into a field, you might validate yourself out of getting key data. For example, what if you setup a “order shipped” field to only allow “yes” or “no” data. What would you enter in if the order partially shipped?

FileMaker validation options are accessed via the field options in the Fields tab of the Define Database dialog box. Validation can be used for Text, Number, Date, Time, TimeStamp and Container fields. Validation is one of the three choices of field options available. The other options are Auto Enter ( when data is entered or edited ) and the other is Storage ( global and indexing options ).

When data is entered into a field with validation options set, if the data conforms to the validation settings, nothing will happen. This is a good thing. However, if the data entered in an unacceptable format, FileMaker beeps at the person doing the data entry. Yes, it is good for you, but can be frustrating for them. You can choose (when the validation fails) to have the user be presented with a default dialog box or your own custom warning dialog box. You can also decide if the user can override the validation setting. By default, it is set to allow a user to override, so be sure to that option is set according to your validation needs!

The set of options available in the validation dialog box are meant to be used together, but you can select them exclusively if you prefer.

In Conclusion - Validation, used properly, can make your FileMaker database become ruthlessly clean and concise. Validation used improperly can really burden your users and become a major roadblock to one of the major purposes of a database ... to gather information!

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