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!


Sunday
Apr132014

FileMaker Custom Function - Name On Email

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

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

This is a ruthlessly simple custom function that I made one evening to help myself out with a little example file I was working on. The example uses a script to pull emails that come from basics accounts such as accounting@dwaynewright.com, sales@dwaynewright.com, support@dwaynewright.com, management@dwaynewright.com and it@dwaynewright.com. I needed to extract the text in front of the “@” symbol to return accounting, sales, support, management and it (sans the @dwaynewright.com).

Since this example file is going to be moved into a number of files that I’m creating, I wanted to make it as portable as possible. So I went on a custom function hunt and didn’t find anything I needed. The needs are very basic. Look for the “@” symbol and get everything in front of it. The calculation is ...

Left ( address ; Position ( address ; "@" ; 1 ; 1 )-1)

Address is the name of the field that contains the the email address.


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

Friday
Apr112014

A READER ASKS: Script Sorting In FileMaker 6

From Dwayne Wright PMP
Certified FileMaker Developer

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

A READER ASKS
I ran across your site and thought may be able to answer a qucick question. Per the subject, can sort criteria be changed within a Filemaker Pro 6.0 script?

-------
DWAYNE RESPONDS
The FileMaker 7 upgrade continues to be my favorite upgrade to FileMaker (after the all important version 3 upgrade). One of the features in FMP7 allowed was the quick editing of find, sort, print and import/export operations. Here is something I wrote way back in my FileMaker 6 Found Set Guide.

(time machine sound would be great here)

The sort script step along with Finds, Page Setups, Import Orders and Export Orders are one of the oddest parts of ScriptMaker. You have to perform the action before you can get ScriptMaker to remember it. If you want to change the sort order in a script, you have to do the new sort operation, then open the script (as if to edit it), close the script and then choose replace from an upcoming dialog box. The same method is also true for the other settings.

In ScriptMaker, if you need to change the layout destination in a script step, you can choose an alternate layout from the option pull down menu inside of that script step.

If you need to change your sort order in a script step, you need to

- Perform Your New Sort Order
- Open ScriptMaker
- Open The Script You Want To Change
- Close The Script You Want To Change
- Then in the dialog box below, click the Replace radio button next to Sort Setup

If you need to change your page setup in a script step from portrait to landscape mode, you need to

- Go To The Layout
- Change The Page Setup To Landscape Mode
- Open ScriptMaker
- Open The Script You Want To Change
- Close The Script You Want To Change
- Then in the dialog box below, click the Replace radio button next to Page Setup

If you need to change the perform find in a script step, you need to

- Go To The Layout
- Perform The Find
- Open ScriptMaker
- Open The Script You Want To Change
- Close The Script You Want To Change
- Then in the dialog box below, click the Replace radio button next to Find Settings

Also up to and including FileMaker 6, you can only have one effective memorized

sort order
find setting
print setup
import order
export order

step in a script. If you want FileMaker to be able to sort two different ways in the same script ( like in an IF statement branch situation ), one of the memorized sorts will need to be in a subscript.

It is quite possible that some FileMaker solutions out there could benefit from sub scripts such as these ...

Print Setup [ portrait - one record ]
Print Setup [ landscape - one record ]
Print Setup [ portrait - found set ]
Print Setup [ landscape - found set ]
Sort [ order date ascending ]
Sort [ order date descending ]
Find [ date field - today’s date ]
Import [ new orders ]
Export [ shipped orders ]

 

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

Friday
Apr112014

When Extensive Planning Of A FileMaker Solution Is Not An Option

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

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

This has to be one of the more ... odd ... titles I have had for a discussion. However, there are times that you do not get the opportunity to do much planning before designing. There are times where you cannot get the necessary data you need to build the best database you can. This is normally the case when the people that want the database but they really don't know what they want (except that they want it cheap, they want it useful and they want it yesterday). Some of you may be laughing because you know it's true.

So here is where I use my golden term for such situations ... the term is ... are you ready ... prototype.

Everybody likes a prototype because it's not supposed to work. It has a low commitment level because we are just dangling our toes in the water. You have to tell the people that are wanting your services that the prototype is a throw away database ... except for those rare cases where it's just what they wanted. You caution them that a lot of time / money might be wasted because the prototype may uncover previously unknown business needs and require redesign. You place a nice red symbol on all the layouts that says prototype, just so they remember.

A prototype is easier for most people to understand than an Entity Relationship Diagram, Flow Charts and just about anything else you can produce. In many cases, you can create a prototype faster and easier than those other proven database planning tools.

A prototype can also have active feedback. Like our online article database, you can have a feedback button. This button can record information about the user, the computer system the user is using, they situation the database was in when they clicked the feedback button and more. This can be done with scripting, Get functions and Design functions. The feedback can also be used to provide online help and printed documentation.

The prototype can be a useful tool in getting started and I have some prototypes ... that have been in use for years ( grin ).

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

Friday
Apr112014

Introduction To FileMaker Portals

From Dwayne Wright PMP
Certified FileMaker Developer

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

Chances are, you can identify a FileMaker portal object on a layout. Portals can be defined as a window to view a set of records that match the settings in a relationship. The portal looks like a mini list on your layout. You can have more than one portal to a layout, which can come in very handy.


Here you can see a portal that allows you to have an almost unlimited number of phone numbers for a particular contact.

Let’s back up a tad bit and describe a little bit about relational fields on a FileMaker layout. When you create a FileMaker layout, you usually start putting fields on that layout. As you drag these fields over, you get a dialog box showing you what fields are available. You can select fields in the current table. Above these fields is a pull down menu. This menu allows you to select relationships you have previously defined in the current file or any file that you have referenced.


Here you can see how to choose the relationship from the specified field dialog box.

After you select a table occurrence, you can select one of the available relationships and once chosen, the fields in that child file are available for you to select.

If you place a related fields on a layout but not in a portal, then enter Browse mode, you get to see the contents of the field for the first matching related record.

Portals are just a simple graphical interface device that show more than the first related record. On their own, portals don’t have calculation capabilities, filtering capabilities and only the most basic of sorting capabilities. At this point in time, all of the advanced capabilities that you can see in a portal within a FileMaker solution are done in the background with the help of calculations, scripting or inventive setup of the portals relationship.


You can tell the related fields from the local ones because of the classic double colon characters in front of the field name in the field. In browse, find or print preview modes, you cannot tell related fields from local ones.

Using solid design skills, a FileMaker developer can add a lot of flexibility to how portal information is presented and used by a user. That is one of the primary goals of this guide is to introduce you to a vast array of techniques to make your portals much more robust. First however, we need to cover the basics.
=
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
Apr102014

FileMaker Lookups And Data In The Found Set

From Dwayne Wright PMP
Certified FileMaker Developer

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

A lookup is a relationship related auto enter field action that will copy the information from a child table occurrence into a field in the parent table occurrence. The copying of information is triggered when the parent key field in a relationship had data first entered into it or data is refreshed.


A lookup can take place in at least five different situations. When data is first entered into a parent field of a relationship, the lookup is executed for that record. When data is edited in a parent field of a relationship, the lookup is executed for that record. When the replace command is executed within a parent key field of a relationship, the lookup is executed for all the records in the found set. When data is imported in from another file into a parent field of a relationship, the lookup is executed for that record ( note ... this can be turned off via one of the import dialog boxes). When the relookup command is executed for a parent key field of a relationship, the lookup is executed for that record.

A lookup is great when you want a time capsule view of information from the child file at a particular moment in time. An example could be the price of product a customer purchased on an invoice. You want the invoice to show the price of when the order was placed. A related field would change the invoice totals if prices were changed in the inventory file. That would be a bad thing because you would have the current price but not the actual sales price at the time of the transaction.

So the above example would be a PRO of lookups.

Another advantage is screen refresh. When a related field is on a layout, it has to open the related file in order to show the information. The same is NOT true for information via a lookup. Data via a lookup will only need to open the related file if when the lookup is executed.

Finally, a lookup field can be indexed. That is to say it can be used as a parent key field, it be searched much faster during a find command and it will sort faster ... particularly in databases with a large found set of records.

So lets even things out by talking about the downside of using lookups. For starters, a lookup process includes the copying of information from one file to another file. This means that data is in both places, repeated and redundant. So it will take up more room in a FileMaker solution. Although I haven’t seen many FileMaker database files crash because of their size, it could always be a factor.

Another disadvantage of lookups is the flip side of the advantage we spoke of earlier. Lookup data is a time capsule of the data in the other file when the lookup was last executed. If you want to show the latest information in a field of related data ... a lookup is NOT the way to go. Related data is always dynamic. When it is changed, it can be seen almost immediately.

Replacing via a relookup can be done by clicking into the parent key field of the relationship and choosing the Relookup Contents command under the Records menu. This will refresh the lookup data from the child file for every field that uses it within the found set.

A relookup cannot be done for a single field replace but we have not talked about the replace command yet. I do what to say that you can do the same thing as a single field relookup by using the replace command. This is done by replacing via calculate the related value.

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