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
Sep042010

FileMaker Simple Lookup Operations

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

FileMaker has the ability to copy related information from one table to another and this process is called a lookup. In this example, we have set a relationship between a table called people ( used in the example layout ) and a table called zip_address. The relationship between the two tables uses the zip code field.


The zip_address table has about 1700 records that match various zip codes in the United States. This table also includes the city and state information associated to that zip code. So what you can do is create a new record and enter in the data in each field. You can move from field to field by using the TAB key on your keyboard. After you enter in the street address, FileMaker jumps past the city and state fields to the zip code field. A pop up list comes up and you pick the zip code you want to use. FileMaker then looks at the associated zip code in the zip_code file and automatically fills in the city and state fields. So you don't have to spend time entering that data in and you know that the data matches the correct data in the zip code field.

FYI... the zip_code file contains an older version of Illinois zip codes and hasn’t been updated in years. It is probably accurate enough for example work but I wouldn't use in in a production database.

An example file can be downloaded by clicking (here)

© 2010 - 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
Sep042010

Enter Into A Field If ...

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

In this example, we are going to turn a field into a button. This button will run a script that will decide whether or not we get to click into the field.


In this example, we are going to say there a 4 possible ways we can enter into the Test Field and any other situation will not allow you to enter into the field. Here is a breakdown of the options ...

Admin User - If you are signed in as admin, you can click right into the field. I added some buttons on the example layout for you to shuttle between admin access and typical user access.

Find Mode - If you are in Find Mode, you can click into the field. This is because we don’t have a need to protect the data in find mode.

Empty Field - If the field is empty, you can click into it. The first record has data in it but the second record does not. Feel free to navigate to that record and give it a test.

Modifier Key Override - You may need to have a way for some users to be able to override the script. Using the Get(ActiveModifierKeys) function, I allow you to enter into that field.

An example file can be downloaded by clicking (here).

© 2010 - 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
Sep032010

Duplicate FileMaker Records With Related Records

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Ok, this something you would think that you could do easy enough. When you duplicate a record in a FileMaker file, the related records are duplicated as well. You would think this would be some setting in the define relationship dialog box.

So ... just because FileMaker didn’t add this as a dialog box option, does that mean you cannot find a way to do it? If you are thinking ScriptMaker might be able to, you are correct!

So here is the steps you need to cook up this dish. I’ve seen a number of folks post ways to do this using an import process from one table to the next. As a general rule, I avoid importing records from one table to another. If something goes awry with the import and/or the resulting need to use a replace command always factor into my thinking.

This example is a rework of my original FileMaker 6 example. With it, I had two files (of course) and had to use global fields to pass the relational key data between the to fields. Describing the process took about two pages of text and pictures. This revised version of the example is a single file solution and uses local script variables. Using this new method, I don’t really feel the need to describe the technique because a snapshot of the script (with standard use of script commenting) does a pretty good job in itself.

Here you can see how I use a local variable and a looping script to duplicate a record and give the duplicate matching related records.

An example file can be downloaded by clicking (here)

© 2010 - 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
Sep032010

FileMaker Conditional Value List Demonstration From 2003

From Dwayne Wright - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

In this example, we show you how you can have a double layer conditional value list. That is to say that we have one value list that determines what is available in a second value list. Then the second value list data option determines what is in the third value list.

An example file can be downloaded by clicking (here)

So we have a setup where you choose a team, then a position and finally a player. The value list options are filtered based upon what choices you make. This feature uses a combination of relationships and value list settings.

We created a movie on this example and uploaded it to YouTube. CLICK HERE!
=
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. 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
Aug172010

A READER ASKS: Duplicate Outgoing Email Records

© 2010 Dwayne Wright - dwaynewright.com
From Dwayne Wright PMP - Certified FileMaker 10, 9 & 8 Developer
EMAIL: info@dwaynewright.com     TWITTER: dwaynewright

A READER ASKS
I was wondering when you would be available to handle the scripting for me to remove the duplicate email addresses.I am using FileMaker Pro 9, I have attached the file, sorry if it is a duplicate, so you can confirm if you can help me, and can maybe give an estimate in cost. Last thing, one we have the script in FileMaker, will we be able to run it again if we import again? 


DWAYNE RESPONDS
Back in May of 2008, I uploaded an example file called EXAMPLE: Mark Duplicates Calculation. It was a follow up to a posting the month before called EXAMPLE: Flag Duplicates Via A Relationship. This example is a blending of those two and a little bit more. In the example file, you can flip through the records and see flagged duplicates. You can experiment with other duplicate situations and tweak the relationships / calculations accordingly.

What I ended up doing on the first pass was flagging email records that had the same date and to address. This was done by creating a multiple predicate relationship setup. Then I could do a find and find the flagged duplicate records. Going through the records, there were some false positives. Here is a link for more information about a Multiple Predicate Relationship.

FYI... The term False Positive has more implications than I’m aware of. When I did a search for this term on wikipedia.com, it took me to an article about Type 1 and Type 2 statistical errors. Straight searches find all kinds on diverse meanings and I encourage you to check it out (if you are interested). In my FileMaker experience, it is generally agreed to meaning a found set search that is technically correct but the resulting found set isn’t what was intended.

I followed up my original duplicate detector with adding one more relationship that added the sent date as part of the relationship used to detect the duplicates. This could still return a false result because it is possible that two records have the same to address, subject & date and still be originals. For this reason, you can add many more predicates to make sure you flag duplicates correctly. This will likely slow things down a little bit because each added predicate is one more thing to process but it may be quite adequate for your situations. 

An example file can be downloaded by clicking here.