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!


Monday
Feb232015

A FileMaker Universal Or Constant Relationship

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

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

This may sound like a silly relationship to talk about ... however ... you can do some very elegant scripting by using it. The idea is that every record on one side of the relationship is related to every record on the other side of the relationship. To have a constant relationship in FileMaker, you would use the X comparison operator between the two table occurrences. So clients x invoices means all records in both table occurrences compare, no matter the data in either.

Here you can see an example of a constant relationship defined in FileMaker.

You might be wondering “ where would I ever want to do that?”. One classic example might be a hopper table. This table would accept data that needs to be processed and passed on to another table. So you could use this relationship to see if there are any records in that table and then have a portal show them.

I built a database for a call center that held customer exception requests. If a product was out of warranty, the phone rep could enter in a case for a repair exception. This record went into a hopper table for approval. The approval person would immediately see when new exception requests came in via a portal, no matter what record they were working in their approval / declined table at the time. When they approved or declined the exception, the record was moved via a script to a different table.

FYI... I’m using the above example more for effect than actual practice. The above example would probably be a better candidate for a filtered portal.

How about this, since I didn’t want to delete the above text. Some years after I did the example above, I worked on a Lead tracking database. A sales department would get flooded with leads for sales and if/when the lead was converted to a customer, their records (with their correspondence history) would be moved from the Leads table to the Customers table. If the sales rep found they were a dead lead, like the company went out of business, they would delete the record entirely.

FYI ... For a universal or constant relationship in FileMaker 6, you would have to create a parent and child calculation field that would always equal the same value. In most cases, it would be a calculation field that always equaled 1. If you convert your FileMaker 6 database system to FileMaker 7 and higher, this will still work. FileMaker conversion will not automatically update this relationship to the more elegant comparison operator design. You will need to do that manually.

Here you can see how every record in one file is related to every record in the other file in a universal relationship.
=
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.

Friday
Feb202015

FileMaker Validate: Not Empty

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

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

As you might imagine, the not empty validation will notify you if you try to leave a record with a defined "not empty" field without any data in it. This is likely to be the most popular validation option you will use. I wouldn’t set this option for all your fields but you probably will want it set for any fields that are involved in lists or reports.

Many times if you don't use the “Not Empty” validation right away. However, you quickly consider it after you have run a report and you notice huge holes where data should be. A classic example would be fields such as quantity ordered, payment type, address, city, state or zip code in a contacts related table.

This is also a likely validation setting that you will not allow your user to override. Of course, end users will catch on to this quickly and add such useful data strings as a period or "n/a". If this is a date or number field, you can select the Of Type option in concert with the Not Empty choice, so they can work together. You could also use a calculated value validation setting to make sure the field is not empty and run other types of validation checks.

On a side note, you should do everything in your power to help the user fill the appropriate data into a field that cannot be empty. One option is to use the auto enter options as much as possible. When considering auto enter for a field that cannot be empty, you must consider one thing. One is that the field may not actually contain the correct data. The user may overlook data entry into that field at the time they create the record.

Another option is to have the validated none empty field be attached to a value list. This gives the user the ability to quickly see what data you are looking for.

On yet another side note, what do you do when you have hundreds of records that need data within them but do not? One option is to do a find for the empty fields. This can be done by going into find mode, type an equal sign into the field and perform the find. This will find the records where that field is empty. Then you can use the replace command to populate all the records in the found set. I won’t go into the Replace command here but will cover it in later discussions. If you cannot wait for that, you can always use the FileMaker Help system under the Help menu. It is quite good and often overlooked!
=
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.

Saturday
Jan242015

The FileMaker LayoutNames Function

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

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

LayoutNames
LayoutNames(fileName)
All Current Versions Of FileMaker
Returns A Text Result

The LayoutNames function gives you the name of each layout in a particular file. Each layout name is listed and separated by a carriage return. Which means you could save this information as a text file and even import this list into another database. This function has 1 defined parameter, which allows you to pick the file you want the layout names for. You can use the Get(FileName) function to return the current file.

Here’s an example: LayoutNames("Clients.FP7") in one of my databases returns...

Form
List
Report

These are the names of all the layouts in that file.

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

© 2010 - Dwayne Wright - dwaynewright.com

Thursday
Dec182014

FileMaker Self Relations, Self Joins Or Self Reference

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

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

Many times we will talk about the parent table and the child table in a relationship. Until this point, we didn’t mention that the parent and child tables in a relationship can be the same table ( although it would take two table occurrences). It is even possible that the parent and child keys are the same field. Just because a relationship is of the self variety, doesn’t mean it can’t also be a relationship of the one to many, many to many, universal or filtered varieties.

Now FileMaker 7 will not allow you to join two table occurrences together twice and that is essentially what you are doing with a self relationship. FileMaker 7 will require you to create a new table occurrence. So a self relationship would probably look something like ... clients ( table occurrence ) linked to clients1 ( table occurrence ). Both table occurrences would reference the same table but you would see them differently any time you are viewing table occurrences for fields, layouts, scripts, value lists and the like.


Here you can see how the “other” file in a relationship is actually the same file as the parent.

An example could be a contacts table that has a type field. You could have a self relationship that uses the aggregate count function to give you a total of other records with this same type in the same table. In this case, the type field would be used as the key field. You would need to setup a second table occurrence to get this to work.

NOTE: This is also called a Self Join Relationship or a Self Referencing Relationship.

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

Friday
Dec122014

FileMaker Database Design & Information Integrity

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

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

For most FileMaker solutions, the primary security concern is ...

- keeping the database solution running smoothly
- make sure the data is entered correctly
- make sure it empowers its users to be more productive.

It is quite possible that a security hole can damage the work flow of a database, damage the accuracy of searches & reports and sometimes cause a database to blow up and crash! For example, say that a new user doesn’t really know how to enter information in correctly. So they go to the wrong layout, enter the wrong information in the wrong fields and then the database crashes or the reports are inaccurate.

SECURITY RETURN ON INVESTMENT
Let’s face it, increasing security in a FileMaker solution can become time intensive and sometimes expensive. There is the time it takes to plan, design, implement, document and upkeep a security system. If you skip any of these, your security might be as bad as not having any security at all. There is also the possibility you may need to invest in third party products ( such as plug-ins ) to get the type of security you want.

As we have said before, many FileMaker developers think of security as an after thought, a necessary evil or a type of insurance policy. So it may be helpful to think of what your return is on your investment of securing your FileMaker solution at a particular level. To do this, you may need to identify what business threats are present if your FileMaker solutions security was compromised. This is typically called quantifying risk.

SECURITY POLICIES AND DOCUMENTATION

To most FileMaker developers, there is at least one thing more dreadful than applying security to a FileMaker solution. That would be providing any extensive written documentation about a solution. How important is the documenting the security of a FileMaker solution? A clear policy on what each user can or cannot do with a database solution can be a great aid to a FileMaker developer. Not only in designing or testing the security system of the solution but other productivity related areas as well.

The security policy does not have to be long winded. A security policy does no good if no one reads it because it is too intimidating. Short, sweet and to the point is generally the best method here.

It may help to think of your FileMaker users grouped into individual business units such as sales, marketing, shipping, etc... , which each having a policy of what they can and cannot do within the FileMaker solution. You can also add sub groups to these business units to define security levels by manager, supervisor or data entry person.

A policy also has the benefit of telling your database users what is expected of them.
This can be as helpful as anything a developer can do. If the database or the developer tells a user they should not be somewhere may not carry as much weight as it does in written form coming from their direct supervisor.

While you are at it, you might want to write a policy for the administrators that are in charge of supporting a FileMaker solution. This can be a policy about how the solution can be supported against possible security threats in the future. This can include checking audit logs, sign in logs, user history and other possible security implementations.

We realize this may not be enough information about security policies but do not let that upset you. There are a number of books about the subject which you can find in your local Borders, Barnes & Nobel or at Amazon.com.

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