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
Jun232011

The FileMaker TableNames Function

From Dwayne Wright PMP
Certified FileMaker Developer

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

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

TableNames
TableNames( fileName )
Introduced With FileMaker 7
Returns A Text Result

The TableNames design function will return a carriage return separated list of all the specified table occurrences in the relational graph in within a specified FileMaker file. This is different that the tables within a FileMaker file because table occurrences are the objects found in the relational graph. In the relational graph, a table can (and often is) be represented by multiple table occurrences.

My blog articles are crafted within a FileMaker database (built without much planning and growing like a weed) has a long list of table occurrences in the relationship graph. Here is an edited copy of some of the names returned when I use the TableNames functions in that database. You may also notice a naming convention that is used in many FileMaker  anchor / buoy relationships ecosystems.

Advertisements
AFA_Join
Blog
blog_ADS__active
blog_ADS__current
blog_AFA_BLOGS
blog_AFAJOIN__
blog_BLOG__byBlog
blog_BLOG__selfCurrentDate
blog_Chapter_ValueList
blog_Crosswords
blog_Crosswords__new
blog_IMAGES
blog_MAIL_Tech
blog_QUIZ
blog_RandomThoughtChaptersVL
blog_TERMS
blog_ValueList
Blog Chapter Value List
blogBook
Crosswords
QUIZ
QUIZ_ANSWERLOG
Terms
Test Table

BTW: Anchor / Buoy
Buoy uses relationships organized into groups and each group doesn’t touch and each group has only one anchor and any number of buoys. Since each group has only one anchor, this means that we are likely to have the same number of groups as we do tables (aka Anchors).

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

© 2010 - Dwayne Wright - dwaynewright.com



Monday
Jun202011

A READER ASKS: Duplicate Flagging Sans FileMaker Scripting

From Dwayne Wright PMP
Certified FileMaker Developer

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

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

A READER ASKS
I have a simple database of email reminders -- web-submitted email addresses of people who want to receive email reminders for particular events.  I can easily filter the list of email addresses based on events, but how to filter for duplicates?

For example:  10 people signed up for a reminder for Event A, but someone submitted the form twice, so the portal for Event A displays 11 email-address records, whcih is technically correct, but I'd like to see only the 10 *unique* email address records.  Is there a clever way to do this?

I can write a script to go through all the email-address records and either mark or delete records when there are multiple Event A  records with matching email addresses, but I sense there's a more elegant way of doing it, through a relationship perhaps?

Any tips/insights greatly appreciated!

-------
DWAYNE RESPONDS
There are a number of ways to go about this and I’ll share with you the method I’d try first. I say this because there are many times when you start to code something and then you think of a more direct path.

FLAGGING DUPLICATES VIA A CALCULATED VALUE
Here is a link to an example file that shows how you can can setup a calculation field to detect duplicate records via a relationship setting.  (click here)

OMIT THE DUPLICATES VIA A SCRIPT
There are a number of script related avenues that you can use to sort a found set and omit duplicates. This may be the way to go and you can attach that method as a subscript in the overall script you use to send out the emails, export records or print reports.

DUPLICATES WITHIN A FOUND SET VIA A CALCULATED VALUE
This would get trickier. I did a quick search for custom functions on the popular briandunning.com site. The first search for “duplicate set” didn’t find any records with those key words. A search for duplicate didn’t find what I was looking for either. A search for found set found two functions that mention they can pull values from a found set.

You would probably have to script this but you could possibly setup a multiple line key field relationship that could flag duplicates within a found set. It may be a bit slow but I think it is possible.

Here is a link (click here) to a posting on multiple line key/match fields.

Monday
Jun202011

Multiple Line Or Compound FileMaker Relationship Match / Key Fields

From Dwayne Wright PMP
Certified FileMaker Developer

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

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

Generally speaking, the most common FileMaker table relationship is when a field in this table equals a field in that table. In fact, prior to FileMaker 7, that was all you can do. With the more modern versions of FileMaker, you can setup multiple predicate relationships using more than two fields and operators other than equal to.

However, there was a technique before FileMaker 7 that kind of allowed developers to use multiple fields in a relationship. One of the two fields was actually a calculation field that was made up of many fields.

But what if you wanted to compare one field but have more than one key value?

A multiple value key field is a great way to setup a many to many relationship. In fact, prior to FileMaker 7, it was the only real way to setup a many to many relationship. It all hinges on the values in the key fields being separated by a carriage return. These carriage return separated data strings are considered their own related values.

That is to say if the parent key field contains

Red
Yellow

and the child key field contains Yellow ... you have a valid relationship! You also have a valid relationship to another record that only contains Red in the child field.

To take it even a step further, it’s possible that a value that is separated by a carriage return in one key field matches a value separated by a carriage return in the other key field, the relationship is valid. That is to say if the parent key field contains

Red
Yellow

it will have a valid relationship to the child Key field containing the value of

Yellow
Indigo.

This is because the carriage returned data string of “Yellow” appears in both key fields.

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

Finding Duplicate FileMaker Records Via A Scripted Loop

From Dwayne Wright PMP
Certified FileMaker Developer

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

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

It has been many years ago when I created my first looped script, but I believe it was created to deal with duplicate records in a found set. This is a technique I used for years and you can use it for other flagging actions besides duplicates.

First we want to find the duplicates. This is done by going into FIND mode and placing an exclamation point symbol ( ! ) in the field. This is the symbol for finding duplicates. You can find this listed on the left side of the screen, in the status area, while you are in find mode.

After you perform the find, you will have all of the duplicate records. You may be thinking "Yee Haw", delete the found set and head to the break room for a freshly toasted bagel. Once returning however, you would be dismayed because along with the duplicates, the originals were deleted as well.

SORT DUPLICATES
We want to put all our duplicates in a row, like soldiers in review. This sets us up for the next step.

MARK DUPLICATES
I think this script was my introduction to the ScriptMaker Loops. What we are going to do is go from record to record asking "Did I see you before?" If the answer is yes, we are going to set the field called "Flagged as Duplicate To Delete" with the letter "X" The individual steps are

Freeze the Window
This speeds things up because the monitor doesn't need to show you what it's doing.

Go To Record (first)
This makes sense, we are starting at the beginning of the line.

Set Field (Global Marker, “the field you used to indicate what was a duplicate”)
This is how we are going to know if each record matches the previous one in the loop. For the sake of this discussion, let us say it is a field called email. We are wanting to tag records with duplicate email address information.

Loop
Now we are going to do the same thing again and again until we have found the last record.

Go To Record Next [Exit after last]

If [EMAIL = "Global Marker"]

Set Field ["Flagged As Ok To Delete", "X"]
We know that a global field has the same information for all records. So we are going to the next record and seeing if the field we are checking for duplicates with (email) is equal to the global field. If it is, we are marking it in a field that we are later going to use the find command on, to then delete (but I'm getting ahead of myself)

Else

Set Field ["Global Marker" = "EMAIL"]
So if this next item on the shelf is different, we need to remember it before we go to the next record ... correct?

End If

End Loop

We are going to be in the loop until we hit the last record. It is just now that I realize I made a mistake. The way it is now, we will not mark the last record if it is a duplicate. We should put the Go To Record Last a little farther down in the script.

Next we do a find command to locate the records we marked and then delete them. I did put in a little error checking logic to halt the script, if no records are found.

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



Sunday
Jun192011

OnObjectEnter FileMaker Script Triggers And Portals

From Dwayne Wright PMP
Certified FileMaker Developer

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

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

The OnObjectEnter script trigger will fire when the layout object it has been applied to becomes active, most likely by a mouse click, keyboard tab action, scripted action or if that field is first in the tab order when a new record or new find request is created.

A portal is a layout object and is also one of the few layout objects that can contain multiple layout objects. That is to say the portal can have objects within it (such as fields) and the instance count of those objects goes up with every related record that portal displays. Doing the math, a field with the OnObjectEnter trigger within a portal row can be displayed 6 times, one for each related record. So that one trigger becomes six triggers in browse mode.

Now lets recap, you can have an OnObjectEnter trigger applied to a portal, objects within the portal or both. The OnObjectEnter trigger assigned to a portal will fire whenever a different row in the portal becomes active. Objects within a portal are repeated for every related record they display and those objects can have the OnObjectEnter trigger applied to them.

What happens when a user clicks into a portal row field and both field and portal have an OnObjectEnter trigger assigned? The OnObjectEnter trigger assigned to the portal will fire first and the field OnObjectEnter trigger will fire second.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

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