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!


Wednesday
Oct012014

Brief Introduction To FileMaker Multiple Predicate Relationships

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

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

In FileMaker 6, you could only setup a relationship that said "when this field equals equals this other field, you have yourself a valid relationship." If you wanted to throw a curve ball into the mix, you would need to be a little creative with the parent match field, the child match field or a combination thereof.

Those days are long gone now and one of the little things I like most about modern FileMaker design is the pop up list of relationship comparison operators.


Here you can see that the FileMaker child match data has two layers. The appointment date needs to be greater than then start date and less than the end date. You can see how multiple layers are very handy for relationships that range.

FileMaker allows you to create more than one comparison layer, this is somewhat akin to the multiple available layers in the sort dialog box. The setup for a multiple layer comparison relationship starts off just like a regular FileMaker relationship. You navigate to the relationship tab in the define database dialog box. You draw a relationship between two table occurrences and then open up the relationship setup box.

Here are some of the things that are different about the Edit Relationship dialog box that are used in the multiple layer comparison relationship. First off there is a button below the right side table box with the label of "Add" and sits along side a button labeled "Change." These new buttons are linked to a box below the buttons that lists the levels of valid relationship comparisons in order of first listed is compared first and then on down the line. Normally, you will only see the one comparison line but that may change as you grow to appreciate what a multiple layer comparison can do for you.

Let's illustrate the multiple layer comparison in a likely "real world" situation. Let us say that you have a client information table and a table that holds meetings with clients. The two tables are linked together via a client id field in each table. This all works grand and you have bettered the business lives of your coworkers and clients using it. Now they would like to have portal that shows meetings between a set of dates. This portal is to appear on a layout that is linked to the client table.

A multiple layer comparison (actually called multiple predicate relationship) 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!

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

The FileMaker Get(OpenRecordState) and Get(RecordOpenCount) Functions

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

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

The Get(OpenRecordState) Function
This function  returns the state of the current record for the current user. The state refers to if a user has their cursor within one of the fields and thereby locking the record. The results of this state are returned by this function in the following manner ...

0 - if the record is closed

1 - if this is a newly created record and the current user is still adding the first round of data to it

2 - if this is a previously created record and the current user is currently modifying the data within that record or they simply have their cursor in one of its fields.

This function is not network aware, it will not tell you if another user has a record opened or not.

The Get(RecordOpenCount) Function
The Get(RecordOpenCount) function will return a count of the number of open / uncommitted records in the current found set.

Personally, I had troubles getting this function to work for me but I eventually did. I had tried using an unstored calculation field on the layout but that would not work. I tried multiple open windows with unsaved changes. That did not work as well, which bummed me out because I thought this would be a good way to see if multiple unstored record windows could be tracked. So, this function is dependent upon the current foremost open window.

I resorted to using the Data Viewer (a FileMaker Advanced feature). Then I started to get some results. I would modify one record in a portal and the counter would jump to 2. This includes the parent record and the portal record. I updated another portal record and the counter in the Data Viewer went to 3. This means the other portal record change is still open. I worked my way through 5 portal records and the sequence remained true.

Then I did the revert command, the counter quickly went to zero and the changes in my portal records reverted as well.
=
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.

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

Sunday
Sep282014

FileMaker Script Debugging And Analysis

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

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

Have you ever created a script and it fails? Have you ever created a script that worked perfectly but now is giving you errors? Ever created a FileMaker solution, came back to it months / years later, look at the scripts and say " What in the world is going on here?" Finally, have you ever inherited a FileMaker database that is as organized as a soccer riot?

In a series of upcoming posts, we are going to discuss bugs that can be found in some FileMaker scripts ( preventing, finding and fixing). We also will cover tools that can help provide analysis for your scripts. Sometimes you inherit a database that has been under development by at least a half dozen developers. Sometimes it’s a database that has always been designed on the fly. So not that much documentation is available about the solution. Here we will talk about ways to get out of trouble ... and ... learn more about your databases!

WHAT CAN GO WRONG WITH DESIGNING ON THE FLY
You have probably heard the quote that some folks spend more time on planning a vacation than they do planning for their retirement. You might even be saying to yourself, "I do that!" Well, I’m not going to write a guide about retirement planning but I will tell you a little about the woes you can get when designing a FileMaker solution on the fly.

Can you imagine building a house on the fly? Forget about those blueprints, they are simply a waste of time! You are about through building the house, when you realize that you need a basement. You realize that the pipes for the hot water are running into the den and the bathroom has no electricity.

In the database world, you may realize that your data will not relate to other key files to meet your users needs. You have to do major deconstruction and reconstruction to get it to work. Since it was all on the fly, you might or might not be able to remember exactly what fields, calculations, scripts, relationships and layout do exactly what. You are paralyzed with fear that if you delete something, it may very well break another aspect of the database solution.

Don't get me started on those FileMaker database solutions that have been worked on the fly by multiple developers, over many years and with literally no documentation. These are commonly called Winchester Mansion databases.

NOTE: The Winchester Mansion was built by the widow of the original maker of Winchester rifles. She built the mansion with fake doors, stairways the went nowhere and the like. The idea was to confuse the vengeful ghosts of those killed with those rifles. From what I understand, additions to the mansion are ongoing and they still are designing it to the original specifications of confusing the ghosts. So you can see how we can make the connection of a staircase that doesn't go anywhere to a FileMaker script or relationship that is never called upon.

EVERYONE WANTS TO GO TO HEAVEN, NO ONE WANTS TO DIE
Much the same can be said for perfect FileMaker scripts. Everyone wants their scripts to be perfect but no one wants to spend the time planning, documenting and debugging them. As we are talking about debugging, we should say there are many levels. In most cases, the bug notification information comes in after the product has been rolled out and is in use.

Some of us, think we run pretty tight scripts. Some of us can get by just dandy with the "at the moment testing" and fix it on the fly routines. In some cases, the tight code we write doesn’t mean anything because we are working on someone else's database.

As we all know " the devil is in the details", debugging is a very important part of the design process. In fact, some believe that debugging is a very under rated skill. If you have 10 programmers at a somewhat competitive level, the best debugger is the best of the bunch.

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

Saturday
Sep272014

A READER ASKS: FileMaker and Accidental Deletes

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

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

A READER ASKS
Great site and thanks for continuing to be the Filemaker Guru!

I have a FileMaker database with 4000+ records and an intern is deleting records by mistake pretty frequently. Do you know of a way to prevent accidental deletions (I read your validation solution) and still be able to edit the record easily?

-------
DWAYNE RESPONDS
My first reaction would be to introduce record level access privileges to the database system. Record Level Security is the method of controlling a users access privileges on a record by record basis. I've written about it in the past, here is just one of those posts (click here).

Although I really like this FileMaker feature, it may not be the solution that best fits your needs. You might want to adopt a delete flag system. Remove the ability to delete records from the interns password but add a delete button / delete flag field. When a user wants to delete a record, they click the button, which then sets the flag. They could also set the flag directly but nothing beats a big old red delete button.

Anyway, at the end of the day or so, a user with delete access can find all the flagged records. After doing the find, you may want to export that found set to an archive (or not) or you might want to make a backup of the database for storage (or not). The idea is that you might have a way to rollback deleted records.

Then the user with delete access can review the found set of delete flagged records (or not) before deleting them. The review process can be used to unflag records you know shouldn’t be deleted. It also will give you a chance to see if you can detect any patterns in the accidental deletes. If you detect a pattern, then you can educate the intern/user or build in programming to sidestep accidental deletes going forward.
=
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.

Friday
Sep262014

Relational Database Design ( A FileMaker Primer)

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

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

Databases, which some may argue are living entities themselves, generally are the answer to our information organization woes. Although if you ever endured a database switch over in the work place, you may disagree. How about if we say a well thought out, designed and executed database is the answer to organizing information? That sounds more on target, doesn't it?

Databases are quickly becoming the lifeblood of companies of all sizes. Normally, databases can be lumped into two categories. The interactive database which gets new information all the time and is used to perform business operations. The other is the warehouse or archive database that is used for research and trend analysis.

Relational databases are used to record and retrieve business information quickly and accurately. There are many things that will help a database be quick and accurate ... and ... there are many things that will prevent it. The fundamentals include useful interface design, accurate calculations, the productive use of scripting and the artful implementation of relationship design.

Databases can designed by a developer, by a team of users, a project manager or better yet, a combination thereof. Depending on how complex the project, you may need a developer, a project manager and a team member for the various departments that will be using the database. There are so many different tools, methods and practices to creating a database solutions, that it can look more like a piece of artwork than an information tool. I’ve seen some folks actually cheer when a new technique is shown at the annual FileMaker Developers Conference.

Relational database programs are commonly referred to as RDBMS or a Relational Database Management Systems. Common ones are SQL, Oracle and Access. FileMaker, although relational in many ways, is still not considered as a common RDBMS by most database professionals. Many times this is because FileMaker does not separate where data is stored and where the structure of the file is stored. By far, the most common relational database is SQL. This is not a database program but a language for working with data.

Relational databases ( like the author of this guide ) was a product of the 1960’s. So you can consider the idea of a relational database as a baby boomer. It was inspired by an IBM employee, Dr. E. F. Codd. He was looking for alternative ways to store and retrieve large pieces of information. He was a mathematician and so you can understand why relational theory is built upon a foundation of mathematics. Databases before relational theory were very hierarchical. To get any information, you always needed to start at the beginning ( normally called the root ) and work your way from there.

This wasn't so bad until you started getting a lot of data. Then starting at the root and going up the tree ( checking each branch as you went ) became unusable. In Dr. Codd's theory, data is stored in relations ( FileMaker calls these files or tables ) and is further broken down into tuples (FileMaker calls these records or rows ) and attributes ( FileMaker calls these fields ).

From here on, we are going to speak of these elements in common FileMaker terms.

Each record in each table needs to have one field that holds a unique piece of data for each record. In most FileMaker files, you would call this the Record ID field. Examples could be the customer id, the invoice id, the product id and so on.

In the last 10 to 15 years, more emphasis has been on the network. A common implementation of a networked RDBMS is called the client / server setup. Here data is stored on a single desktop computer and users/clients interact with the information via a network connection and a local application on their machine.

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