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!


Tuesday
Sep142010

Counting FileMaker Portal Rows

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

This is a very basic FileMaker technique but is a very popular " how do I?" question among new FileMaker users. The entire question is " How do I get a count of all the rows in a portal?"


The answer is to use one of the aggregate family of functions called Count. It will give you a count of the number of a times a related field has data within it. So your calculation will look something like ...

Count ( field in a relationship )

So the basic steps are ...

1) Go To Manage Fields (Define Fields pre FileMaker 9) and create A Calculation Field

2) In the upper right corner of the Manage Calculation dialog box, double click the Count selection. This will add the following in the edit calculation dialog box ...

Count (field…)

and the field section will automatically be highlighted.

3) In the upper left corner of the Manage Calculation dialog box, choose the relationship used in the portal from the pull down menu above the list of fields.

4) Double click the field you want to count. This should fill out the rest of the calculation.

5) Add the field to your layout and test it!

NOTE: If that field is empty but the row is in a portal ... it will not be counted. So in many cases, you will want to count the auto enter primary key field. That should never be empty in any table.

NOTE: You can also add teh @@ symbol in a portal row to see the row number for each portal.

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.



Tuesday
Sep142010

A FileMaker Date Fix Calculation

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

Date fields are designed to store date based information. Because of this, the entered data needs to contain month, day and year data and this data needs to be separated by a common text character. The most common format in the United States is month, day and then year ( 02/28/2004 for example ). FileMaker will not allow you to type in date related data it cannot understand as a date. You cannot enter in “Next Wednesday” in a date field. FileMaker will bark at you, via a dialog box, when you violate date based formatting that it understands.

However, FileMaker does have a few holes in it’s protection of the format of a date field. In fact, I wouldn’t call them holes as much as blindspots. Bad date data ( for a lack of a better term ) can be introduced into a FileMaker date field in at least 3 different ways. There may even be more ways but these are the ones that come to mind.

Importing Data From Another Source Into The Date Field
(although there is a check box to prevent this on the import dialog box, it’s final results can be less than ideal.

Converting A Text Field To A Date Field
(although FileMaker does warn you via a dialog box that this might not be a good idea)

Using The Set Field Script Step

Each of the above will allow you to place data into a date field without any date format checking.

So your calculations or searches that rely on this information may be incorrect or fail completely. Here are a few incorrectly formatted dates that could be imported into a date field and a function that would help you fix them. We will be using text functions to slice and dice the string of text and then put them together in the proper format. We also used a Left to zero calculation to put in a note where each calculation for the month, day and year start.

If you have a found set of the incorrectly formatted date records, you could use the replace command to fix the date format, otherwise you could use the SET script step to do them one at a time.

FORMAT: 2001.12.25
TextToDate(
Left("Month", 0) & Middle(Fix Date 1, (Position(Fix Date 1, ".", 1, 1) + 1), Position(Fix Date 1, ".", 1, 2) - Position(Fix Date 1, ".", 1, 1) -1) & "/" &
Left("Day", 0) & Right(Fix Date 1, Length(Fix Date 1) - Position(Fix Date 1, ".", 1, 2)) &
Left("Year", 0) & "/" & Left(Fix Date 1, Position(Fix Date 1, "1", 1, 1)))

FORMAT: 25122001
TextToDate(
Left("Month", 0) & Middle(Fix Date 2, 3, 2) & "/" &
Left("Day", 0) & Left(Fix Date 2, 2)&
Left("Year", 0) & "/" & Right(Fix Date 2, 4))

An example file can be downloaded by clicking (here)

The majority of the text for this example was written when FileMaker 6 was the latest version of FileMaker. That of course is no longer the case. So many more and elegant ways to cleanup a date field are likely available. If you have FileMaker Advanced, you might give serious consideration towards a custom function. Here you likely find half a dozen custom functions for this particular need.

Check out the custom function library at

http://www.briandunning.com/filemaker-custom-functions/recentlist.php ,

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

FileMaker Printing Via A GTRR Action

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

In this example we illustrate how you can overcome the limitation of printing portal row data that you only see after scrolling a portal. We show you a standard layout that has some standard information about a person and a portal of some of their favorite things. The portal only shows 3 rows of data but their are 4 rows of information in each portal. To see the fourth row, you click a down scroll arrow button.


Underneath the portal are two buttons ...

This File - Print Preview
which will put you into print preview in the parent table. You will notice that the fourth row of data will not show up on the print out.

GTRR - Print Preview
which will take you to the related records in the child table ... for that portal! It will go to a layout in list mode and enter preview mode. You will see there that all 4 rows of portal data are displayed for printing.

The relationship in the child file is the same as in the parent file. In this case it is the child key field and the record ID field in the parent file. So for a moment, the roles of parent and child are switched for the GTRR print routine.

The layout needs to be viewable in list mode. The data that is in the portal row in the parent file needs to be in the layout body. The unique parent file fields need to be in the header or footer. The parent data you want to show needs to be linked via a relationship to the main file.

The script will need to go to the correct layout, print and then hide itself.

An example file can be downloaded by clicking (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



Monday
Sep132010

Mark FileMaker Duplicates Via A Calculation

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

Have you ever wanted to create a calculation that would tell you how many duplicate records you have in a database solution? Well, you can by using a self relationship, a logical function and a few aggregate functions.


First, create a relationship in a file to itself and use as the key field on both sides ... the field you are using to find duplicates. For example, customer name may be a field that you want to check for duplicates with. It is possible to use multiple fields in a duplicate detection by concatenating them into one calculation field.

Next, we need a logic function to branch if the result is one thing or another. I’m using the CASE function but the IF function would work just fine also.

Case(
Count(Self_Customer name::Customer Name) > 1, 1, 0)

Here we are saying that if there is more than one relationship match ( duplicate ) give us a 1, otherwise give us a zero.

Next we want to get a sum of the totals of the duplicates.

Sum(Self_Customer name::Mark Name Duplicates)

This will give us a total of all the records that have a 1 in them via the customer name relationship. So only the duplicated records will be showing the counts for their corresponding duplicates.

Now we also might want to detect if the record we are on is the original or one of the duplicates. This is done by assuming the earliest occurrence of the record is the original. To do that, we use the Min function to find the lowest occurrence and compare that to the record we are currently on.

Case(
Min(Self_Customer name::Serial Number)= GetAsNumber(Serial Number);"Original";
"Duplicate")

An example file can be downloaded by clicking here

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



Monday
Sep132010

FileMaker Import (Match / Replace) Actions

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

This example shows off the two less used import actions ( Replace Data In Current Found Set and Update Matching Records ). Each of these imports is set to leave the total number of records in the file the same ( 10 ).

When you click the Import Replace, you can see the import dialog box and that its import action is set to Replace Data In Current Found Set. You will also notice that FileMaker has adjusted the found set in the destination file. You now have a found set of 2 files from the overall number of records. This is because the source file we are importing from only has 2 records. Using replace option makes FileMaker dynamically update the found set in the destination file to match the source file.

Be sure to click “OK” and you will notice that you still have the same number of records ( 10 ) but the first two records now indicate they contain replace data. This is because that is the data that is in the source file. You can open up the source file and change that data to something else. Come back to the destination file and run the script again. This will allow you to see how the changes can be made.

The Import Matching button will also bring up the import field mapping dialog box and is set to the Update Matching Records In Current Found Set import action option.

We are using the Company Name field as our match data field. So for the records that have matching company names, FileMaker will import the Competitors With and Company Notes fields.

When the operation has been completed, you will notice that you are looking at the same number of overall records but we have a found set of one record. This is because there was only one matching record using the Company Name field between the two files. Only this one record was updated with the imported data. The record with the company name of Marvelous Products Company has been updated with the competitor information of ABC Company ( match data ).

An example file can be downloaded by clicking (here)

Here are some links to other posts that might be of interest in regards to this topic...
Importing Data Into Filemaker Explored ( part 1 )
Importing Data Into Filemaker Explored ( part 2 )
=
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.