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



Monday
Sep132010

Sorting On Multiple Fields

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

As you probably know (or at least suspected), you can sort a set of FileMaker records by more than one field. Each of these fields can have their own sort field settings such as ascending or descending. The first specified sort will take place and then any duplicates in that sort order will be sorted by the following order ( and so on for as many sort orders that you have in the sort operation).


In this example, we performed a multiple field sort by adding the following fields to the sort order and setting their options appropriate to our needs.

Market field ( ascending )
State field ( descending )
Company Name ( ascending ).

So here is what we get when we click the following fields...

SORT COMPANY
ABC Company IL Shoes
ABC Company CA Shoes
ABC Company CA Backpacks
LMN Company TX Shoes
LMN Company TX Handbags
LMN Company NY Shoes
LMN Company NY Backpacks
TRU Company IL Handbags
TRU Company CA Handbags
XYC Company TX Shoes
XYC Company NY Backpacks

SORT STATE
TRU Company CA Handbags
ABC Company CA Shoes
ABC Company CA Backpacks
ABC Company IL Shoes
TRU Company IL Handbags
LMN Company NY Shoes
LMN Company NY Backpacks
XYC Company NY Backpacks
XYC Company TX Shoes
LMN Company TX Shoes
LMN Company TX Handbags

SORT MULTIPLE FIELDS
LMN Company NY Backpacks
XYC Company NY Backpacks
ABC Company CA Backpacks
LMN Company TX Handbags
TRU Company IL Handbags
TRU Company CA Handbags
LMN Company TX Shoes
XYC Company TX Shoes
LMN Company NY Shoes
ABC Company IL Shoes
ABC Company CA Shoes

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
Sep102010

Relookups And Alternate Methods

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

The Relookup Contents Script Step is used to replace information in lookup fields with the current related information for all the records in the current found set. Much like entering information in a key field triggers any associate lookup commands, the relookup will do the same thing, except it will do it for all the records in the current found set.

Sometimes, a relookup can be a very bad idea because it will replace all the contents for all fields using that lookup setting for all records in the found set.

In a case where I want the latest related data in a particular field for a found set of records, I tend to use the REPLACE command with a calculated result equal to the related data. In a case where I want the latest related data in a particular field for just one record, I tend to use the SET script step with a calculated result equal to the related data.

In this example, we have the city and state fields set up as lookup values. When a zip code is entered in, the city and state information is automatically copied over. Click the step 1 button to set the city and state information to text values. Click the step 2 button to see how the relookup command sets the city and state values to their related data via a relookup.

Click the step 1 button again to set the city and state information to text values. Now click the Alternate Step 3 button and you will see the test data was only changed for the current record. This was done by using the SET script step.

Click the step 1 button again to set the city and state information to text values. Now click the Alternate Step 4 button and you will see the test data was only changed for the City field for all records in the found set. The State field is unchanged. This was done by using the Replace Field Contents script step.

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.