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!


Thursday
Apr242014

Say Hello To FileMaker Scripting

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

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

Scripts are the movers and the shakers in a FileMaker database solution. A script is generally used in association with a button on a layout to perform such tasks as printing, navigation, setting data inside of a field and countless other possibilities. In more robust FileMaker solutions, scripts can be called upon from menu commands at the top of the FileMaker window, when exiting a field (with a help of a plug-in), from a robot (not kidding) or a variety of other options. In fact, the landscape of how and when scripts are called upon is always changing.

Going back to my mention of scripts performing tasks as printing, navigation, setting data inside of a field, you don't have to write an individual script for each of these individual tasks. You can have one script perform all these tasks. One script could (and often does)...

- Set a field to a specific value
- Go to a specific layout
- Print under a set of desired specifications
- Set a field to a totally different value
- Go to a specific layout (perhaps the original, perhaps not)

This is because a script is actually composed of a set of script steps. You organize selected script steps in order (from top to bottom) and FileMaker will execute them in that order. This is nice because many other database products make you code your scripts in a pure text editor. FileMaker is more of a point, click and shoot design experience. Pretty logical don't you think?

So in summary, a script is a set of actions that you want a FileMaker database to perform. The set of actions need to be arranged with the first things at the top of the list and the following scripts in order as you venture towards the end of the script.

Actually, that is not a summary of scripts at all ... it’s just a start. Scripts can perform tests and branch accordingly. Scripts can be made to pause for a duration or until a user enters in data.

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

Thursday
Apr242014

Working With Unique Or Duplicate FileMaker Values

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

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

There are always times when a FileMaker table will contain unique values in fields and times when it will contain duplicate values in a field across a set of records. There are times that you may want to find duplicates and there may be times you want to find only unique values. FileMaker does have an operator for finding duplicate values in a field ( ! ) but does not have an operator for unique values.

However, the Show Omitted Only command (under Records menu) takes the found set of records and makes them the omitted set and vice versa. So your found set and omitted set flip and this means that you can do a find for duplicates and then swap that found set to the omitted set. This would give you a found set of records with only unique values.

Another option is to use the Omit check box in the search. You can also do a find for duplicates but click the omit checkbox in the status area. This is better suited for constrained finds within a found set.

If you are wanting to build a list of unique values, you can define a value list based on that field. Follow that up with a calculation using the Design function ValueListItems. This will give you a unique set of values, since a value list uses the fields index.

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

Thursday
Apr242014

A READER ASKS: Adding Hours To A FileMaker Time Field

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: Just got a quick question for you. I found your name via a Google search for FileMaker Calculations.

I'd like to take a field that gives me the start time of an event, add the number of hours of the event and return a value that's the end time of the event.

Example:
Start time (Time field) = 13:15
Hours (Calc field returning a number) = 1.25
Therefore, End time would be 14:30

Do you have a calculation that would do that?

-------
DWAYNE RESPONDS: Sure, you will want to use the Time function to separate the data into hour, minute and second parameters. Then within those parameters, you use your hour, minute and second functions and add values respectively.

Time( hours ; minutes ; seconds )

then would be become

Time( hours(start time) + add hours ; minutes(start time) ; seconds(start time) )

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

© 2010 - Dwayne Wright - dwaynewright.com

Wednesday
Apr232014

Deleting A FileMaker Found Set (part 2)

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

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

YOUR OPTIONS ( NUMBER 4 )
Start off by using one or a combination of security options and custom menus. Then provide a backup option for your databases. If you are using FileMaker Server on a network, you can backup the database live with a minimum of performance problems. DRAWBACK : This does give you some "limited" protection for unintentional deletes. Even if you implement a "backup often" routine, there is still potential that your user will create and delete something in between backup events. It also is a hassle to find the backup that has the latest version of a record.

YOUR OPTIONS ( NUMBER 5 )
Backup all information to an archive table before deleting via scripting. This is done by using a combination plate of the previously mentioned options. For sets of very key data, you can build an archive file that passes the information to it first and then deletes the master. DRAWBACK : It will take time to pass all the data to another table. Of course, if you are having too many deletes by your users, it’s an indication of other possible problems. You will need to build an archive file for each file you want to do this for. You have to store a lot of extra files / records but you do have excellent recover capability for unintended deletes.

YOUR OPTIONS ( NUMBER 6 )
This is a variation of option number 5, instead of writing the information to a backup table for each table, write them all to one archive table and save the information as a text array. This is something that advanced users are familiar with. The idea is that you take a bunch of information from a bunch of records and save it as one calculated text string. Each piece of information is separated by a flag or marker. If this sounds a little like HTML, you are right. Web pages and the elements in them do resemble text arrays. This allows you to save more information in a smaller space. It also offers a smaller performance hit to users. DRAWBACKS : You will need to build an engine that can breakup the text array. You will use this engine when you have to restore deleted data. This option does take some time to setup and modification time can hurt if your database isn’t quite mature.

YOUR OPTIONS ( NUMBER 7 )
There is a third party product called FMDataGuard that can be used to automatically store copies of deleted files. The setup for this product is not that easy, it isn’t particularly inexpensive but you can download a demo and work with it to see if it can meet your needs.

SO WRAPPING THIS RASCAL UP
As you can see, there are many options when it comes to protecting your FileMaker database against accidental record deletion. None of them are perfect and none of them will make everyone happy. However, one of them ( or a customized combination plate of them) will likely take care of your immediate business needs. Personally, I tend to start at the bottom and work my way up to Option 7.

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

Wednesday
Apr232014

Sorting FileMaker Portals Via A Calculation Field

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

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

One thing you can do is have your portal sorted by a calculation field. The basics are that you set the portal for sorting just like we showed you before. The field that the portal is sorted by is a calculation field. This calculation can be dynamic, which is to say that it can use get or design functions and sort dynamically based upon database conditions.

Another popular option is to sort a portal by what a user selects in a global field that in turn sorts the field based upon a CASE calculation function in a calculation field. This tends to look something like

Case(
Global Filter Field = “Sort Name”, “Name”,
Global Filter Field = “Sort Zip”, “Zip”,
Global Filter Field = “Sort Phone”, “Phone”,
Global Filter Field = “Do Not Sort”, “”, “”)

The advantage of global fields as the switch in the calculation is that the global data will remain the same as you go from record to record and is the staple of most portal sorting routines. Also in a multiple user / networked setting, global data is unique to each user once it has be modified.

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