FileMaker Example: Each Field Is Now A Record

There may come an occasion when you need to take data that is in a field and make it a related record. Let's say that you have a half dozen or so fields setup for phone numbers. Now you want to move all the phone numbers into a related table. In this example, we have two tables called Parent Table and Child Table. Our goal is to take the 6 data fields in a Parent Table record and make an individual related record for each in the Child Table.

This is done via a combination of techniques including concatenation, parsing, create new record, status function and a looping script. (here is the download link)

SETTING OUR BIG TEXT FIELD
Our first step is to set one text field equal to all the contents of the fields in the tab order and separate each value with a carriage return.

Here at the script steps for this process with a brief description of what we are doing.

Set Field [ Big Text , “”]

This is to initialize the text field for our script by setting it to empty.

Go To Field [ Field A ]

This is our first field in the tab order.

Set Field [ Big Text, Status(CurrentFieldContents ]

This sets the big text field equal to the first field that our cursor is within.

Set Field [ Counter, 1 ]

The counter is a global number field. We will be counting each field we enter into. This is used to compare how many fields we have been in ... to how many fields we want to be in. When they match, we exit the script.

Loop

The process between the Loop and End Loop will repeat until we achieve the requirements of the Exit Loop If script step.

Set Field [ Big Text & "¶" & Status(CurrentFieldContents)]

Here we are setting the Big Text field equal to itself plus a carriage return ( for the new value) and the contents of the current field.

Set Field [ Counter, Counter + 1 ]

This is used to set the counter equal to itself plus one. This is how we count each field that we do in the loop.

Exit Loop If [ Counter = 6 ]

In this example, we want to do 6 fields in the tab order. If you wanted to do 148, you would set it to 148. FYI... make sure your tab order is set correctly before running the script.

End Loop

Each Loop script step requires an End Loop script step as a matter of proper syntax.

SETTING OUR RELATED RECORDS
Now that we have our big text field ready, we need to create our related values. This is done by setting up a related portal with the Auto Create Related Records option checked. This means the last row in the portal will always be empty, waiting for you to create a related record.

Our script to creating the related records will include the following steps.

Loop

The process between the Loop and End Loop will repeat until we achieve the requirements of the Exit Loop If script step.

Go To Portal Row [ Last ]

This takes us to the last portal row, which is ready for us to create a related record within.

Set Field [ Info, Case( PatternCount(Big Text, "¶") ≥ 1, Left(Big Text, Position(Big Text, "¶", 1, 1)),Big Text)]

Here we are setting the field in the newly created related record equal to the top row of the Big Text field. We do this by looking for the first carriage return and setting the related field equal to everything left of the carriage return. If there is no carriage return left ( which will be the case on the last piece of information ), we set the field equal to the big text field.

Set Field [ Big Text, Case( PatternCount(Big Text, "¶") ≥ 1, Right(Big Text, Length(Big Text) - Position(Big Text, "¶", 1, 1)), "")

Here we are slicing the top row off of the Big Text field because we have already placed it into a related record. We do this by seeing where the first carriage return occurs and setting the field equal to everything to the right of it. If there is no carriage return left ( which will be the case on the last piece of information ), we set the field equal to empty.

Exit Loop If [ IsEmpty( Big Text ) ]

When we have emptied the field, we want to loop to stop.

End Loop

Each Loop script step requires an End Loop script step as a matter of proper syntax.

=

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.

FileMaker: Little Things That Influence Data Entry Workflow

As a FileMaker developer, your decisions (both large and small) can have a huge impact on others. As a developer creates or modifies dozens of data solutions over their career, they may loose sight about the significance of small changes. For example, take something as innocent as the tab order on a layout. 

The tab key in FileMaker browse mode allows a user to quickly jump from one field to the next. In most cases, this setup will align to some sort of workflow or chain of thought. A classic example is when a developer is handed a form that is normally filled out by hand and asked to create a database to replace it. The developer will typically add the standard boilerplate fields (primary key, created by, modified by, etc...) and then add the fields on the layout in the same order as represented on the sheet. When the developer arranges the fields on the layout, they also tend to match the format found on the form. 

A developer might see patterns in the form that could be enhanced from a data entry perspective. They may take some liberties in the layout design and present those to the customer. This can be something as simple as the tab order (mentioned above) or include value list integration, script triggers, lookups or a collection of other basic enhancements. In this example, the developer is potentially adding value on top of the creation of the database value because they are streamlining a process. 

Lets say a change streamlines the data entry process for a record by 15 seconds, you might consider this too small to register as "significant". However, what about the aggregate? Let's say that we have 200 new records a day and 260 working days in the year. That brings us up to 52,000 seconds a year saved for the data entry process alone. Again, we are not talking about an amazing amount of time because that only equates to 14 hours saved per year but the change only took 5 minutes to accomplish. Any business will jump at the chance to have 14 hours of work saved with an overall investment of 5 minutes of process tweaking. 

I have literally had people thank me in rapid machine gun mode for tab order changes (thank you, thank you & thank you). They have said things such as "the way it jumps down to that field at the bottom has been driving me crazy for years!". That type of hiccup normally comes about when someone makes layout changes but doesn't re-examine the impact on the data entry tab order. 

That last sentence is an example of how little things can influence data entry workflow in a negative fashion. A developer could spend a tremendous amount of energy adding features to a solution and have it impacted negatively by users because of the little things. 

I guess if I had to wrap this up, a developer needs to be cognizant of the little things as much as the big things because a database is a type of ecosystem. 

© 2016 Dwayne Wright

Hunt, Gather And Present Search Results From Multiple FileMaker Tables

A READER WRITES
Thank you for your “quickly scripting filemaker…” video. I’d struggled to search my database with twenty tables (with hundreds of records in each one), and your approach worked perfectly.

You might have already solved my next problem: I want to search all at once (instead of one at a time), and present a report of the found records. Can you point me toward one of your videos?
Great work , thanks again,

VIDEO LOCATION: https://www.youtube.com/watch?v=yJcKK3D9BVA

DWAYNE RESPONDS
In most cases, you can do anything with FileMaker and you can take many diverse paths to get there. I tend to lean towards the solution that provides the greatest value, least risk and reasonable effort. It is easier to add complexity to something overly straightforward than it is to simplify something overly complex. 

THE HUNT
You can script a procedure such as the one in the video that goes to multiple table layouts to perform the search. Each search result would be in a separate window and you can even close the window if the found set returned is empty.  You can even do some elegant scripting to name the result windows and present them to the user. The user would need to go through each window to view the results but they may actually like it that way. 

THE GATHER
The gather procedure would be the report you mentioned and that can actually independent of the hunt. All you need to do is figure out a way to present data from multiple tables in a single place. That will probably need to be highly customized based upon what you want to present in the report. 

PRESENT OPTION 1: You can take all the data gathered and copy it to a new search results table. This is a new representation of the data and can have many uses. 

PRESENT OPTION 2: A dashboard can be created for this as well and FileMaker dashboard design can become very time intensive. So I would suggest caution going down that path but it might be the best option for some consumers of the data results.

Hope that helps!

Dwayne

The FileMaker Let Function: Introducing Flexibility Within Calculated Narratives On Component Level Data

Use Case Builder is a FileMaker database that allows a business analyst, project manager, subject matter expert (SME) and/or other project team members to create and store use cases. This is an open product, distributed "as is" without support but also open for customization by a FileMaker developer. 

This system allows you to ...

  • create new use case records quickly & easily
  • duplicate and modify existing use cases for emerging needs
  • copy complete use cases for integration into MS Word documentation
  • be customized to adapt to diverse requirement needs of the organization

THE WRAP UP
A Use Case has multiple components and the Use Case Builder allows you to do data entry on a component level. Although it is nice to have the data entry experience accomplished on a component level, ultimately the use case needs to be consolidated into a single piece of text. The Wrap Up does just this by taking all the component information and then delivers a single piece of narrative text that can be used to get approval / feedback, update core project documentation, create test cases and facilitate technical specifications. 

USING THE LET FUNCTION
Function allows you to use your own variables within the syntax of the function. It can be used to make complex calculations easier because you can reference just about anything as a variable.

The Let function can make a complex calculation easier to read. This is because you can set variables equal to calculated values and then calculate them. A calculation that is easier to read tends to be easier to troubleshoot. This is an accurate statement for calculations that leverage the Let function as well.

The Let function can also be much faster than a calculation that does not use the Let function. This is because a variable once declared within a Let function can be used within that calculation repeatedly without a recalculation.

THE LET FUNCTION AND THE WRAP UP
s mentioned earlier, the Use Case Builder has multiple components and the Wrap Up feature takes all the component information and delivers a single piece of narrative text. By using the Let function, each component is defined as a variable. That makes it much easier to manage, make changes and troubleshoot (if necessary).

The FileMaker Get(LayoutTableName) Function

Get(LayoutTableName)
FMP Version: FileMaker 7 and Higher
Returns A Text Result

Layouts are linked to a defined table occurrence in the relationship graph. Please note that I said a table occurrence and not a table, although by default the occurrence is linked to a source table.

The Get(LayoutTableName) function will return the name of the table occurrence that is linked to the currently viewed layout in the foreground window. In many cases, the name of the table occurrence is the same as the table but not always. In particular, a complex FileMaker database may have many table occurrences linked to the same source table. In a case like this, you may need to have a standard way of naming your table occurrences. Using the same method each time, you might be able to use other functions to extract the base table information from the name of the table occurrence.

For example, if you always name your tables with one word and you always name your table occurrences with the base table name as the first word, then you could use the leftwords function to calculation the base table name of any currently viewed layout.

UPDATE: I wrote the majority of the above content when FileMaker 7 first came out. About a year later, a technique called the Anchor / Buoy (also called the Squid method) became popular. It became extremely popular with me and it is mainly due to how well it handles the ability to track what source table a layout will be linked to.

The source that turned me on to anchor buoy was a presentation by Kevin Frank and Associates. Here is a link, if you want to check it out.

http://www.kevinfrank.com/anchor-buoy.html

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.

Navigate FileMaker Portal Rows Via Buttons

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

In this example, we show a very easy technique of moving from one portal row to the next via buttons. On the surface this seems to be a very trivial example but it does show that you can control FileMaker's ability to navigate portal rows. This can become very helpful as you start to build larger and more complex scripts and interfaces.

In the example we have 5 buttons which are First, Previous, Next, Last and Go To Row Number. We show a portal listing the seasons of the year which are Spring, Summer, Fall, Winter and Football. The last is my favorite, I must say.

The first four buttons are not even attached to a script. You can define a button go to these portal rows directly via the button choices. Originally, this example used the go to portal row by field. During conversion to FileMaker 9, this was automatically updated to Go To Row By Calculation. Going to a portal row by the contents of a field can be scripted. This is exactly what we did. All you need to do is choose a portal row number from the value list and click the button next to it.

I also added a button called Go To Row Dialog. This will bring up a dialog box allowing you to manually enter in a portal row to go to.

I only added one tweak that is a little off of the beaten path. If the Portal Number field is empty, the script brings up a dialog box asking what portal row you want to go to . The script looks like this ...

If [
IsEmpty(Portal_Number)]
Go To Portal Row [ Select By Number ]

Else
Go To Portal Row [ Select, "Portal Number"]
** which is the name of the field **
End If

The final thing we did was add another layout that uses a similar relationship but it can create new related records on the fly. You can get there by clicking the View Add Related button. From here, you can add a season to the global field and click the add new season button. This goes to the last row in the portal ( empty waiting to create new related records ), sets that field equal to the global, sets the global to empty and exits the record.

An example file can be downloaded by clicking (here)

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

The FileMaker Calculation Field And Storage Options

In many ways, the storage of a calculation is the same as the storage of a regular field. In a regular field, the third tab of the field options settings is for the storage of the field in regards of global storage, repeating fields and indexing.

The global field option is used to contain a single literal value for all records in a FileMaker file. Any field that has data stored globally has one value and it can be viewed no matter where you are in a FileMaker file. By anywhere, I do mean that any global field in any table can be view from any record of any other table. Fields with the global option can be edited directly on a layout or via a script. Global fields work about the same as normal entry fields in that you can click inside them, cut, paste and add new data.

FileMaker indexes focus on the data within a field. So if you have a FileMaker field indexed, FileMaker can look at it and go to the associated records quicker than it can if it is not indexed. For this reason, indexing is a key player in searches, sorts, reports and relationships.

A repeating calculation field is any type of field ( except summary ) with a specific storage option setting. The storage option of repeating fields allows you to have multiple cells of information in the same field. If you have two repeating fields in a calculation, each cell repetition will be calculated by the same cell repetition of the other field. In cases where a repeating field is in a calculation with non-repeating fields, you will need to use the Extend function. In fact, with any calculations involving repeating fields, you may need to refer to some of the functions that are particular to repeating fields. I cover this in other discussions but here is a list of those repeating functions and how they are commonly used.

Extend - Extend( non-repeating)
Used to extend a non repeating field to be used in a calculation for each repetition that needs a calculated result.

EXAMPLE: A repeating field has these three values in each repetition ( 2, 3, 6 ). If we extend a field that has the number 2 in it and multiple it by the repeating field, we would get ( 4, 6, 12 ) as our repeating results. Without the extend field, only the first repetition would be multiplied by 2. So you would get ( 4, 3, 6 ).

GetRepetition - GetRepetition( repeatingField ; number)
This function returns a specific value of a particular repetition of a repeating field. For instance it can tell you the second repetition of the field phone type is fax. The function does have the two parameters of repeatingfield and number. The first is the specified repeating field you want to work with. The second is the particular repetition that you want.

Last - Last( repeatingField)
This function returns the last valid, non empty value in a repeating field or a related field. In a relationship, the last can change based upon the setup of the relationship. By default, the last value would be the value that is in the record that was most recently entered. Sorting a relationship may change this because the last value entered may not be the last record in the sort order.

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.

The FileMaker Calculation Dialog Box

The Calculation Dialog Box is the FileMaker developers sandbox for all things calculated. Many times in FileMaker discussions you might hear the term “calculated result” and it’s a powerful term to latch on to. A calculated result is defined via the calculation dialog box. This box can be found in a number of areas. One is a calculation field, ie.. you have a field that contains a calculated result. It can also be found in scripts, so you have a calculation that will control when a script runs, which script steps run and when the script ends. It can also be found in validating the information entered in a field, so you can control what a user types into a field via a calculated result.

How about we take a step back and start again at the most basic level. Let us create a simple calculation field, shall we? Under the File menu, choose Define and them from the sub menu that appears on the right, choose Database. In this dialog box, you will see three tabs for Tables, Fields and Relationships. Click the second button and here we are going to create a new calculation field. In the field name, type in test ok to delete. I give fields names like this just in case I forget to delete the field later on. From the Type menu, choose calculation and finally click the create button.

The top half of the calculation dialog box is your calculation resource toolkit. It is divided into three main sections for picking fields, operators and functions. This is consistent with the point, click and shoot design method FileMaker uses in so many areas.

The first section is your field pick list. By default, it will list all the fields that can be found in the table you are currently working in. Above the field list, you will see a pull down menu that will allow you to see the fields within other tables in a file (some with valid relationships and some without). In fact, you can even bring up the “define new relationship” dialog box via this pull down menu. This is nice because it allows you to define a relationship without closing out of your working calculation!

Continuing on with our conversation about the calculation dialog box ....

The second section is where you can find the right operator for the right task. The eight most popular operators can be found as easy to click buttons. They are the concatenate, text constant (quote marks), carriage return and precedence in the first vertical row.

In the second vertical row, you have the division, multiplication, subtraction and addition buttons.

Concatenate - connects or concatenates two text strings together

Text Constant - each shows the start and end of a text string constant

Carriage Return -inserts a text carriage return or paragraph break between elements

Precedence - performs the calculation left to right inside of the parentheses

Now the second section ( part two ) is a scrollable list of operators which will contain your comparison operators ( greater than, less than, greater than or equal to, less than or equal to, equal, not equal ) and your logical operators ( AND, OR, NOT, XOR). We discuss logical operators in detail in later conversations.

The third area is a scrollable list of the functions that we will be discussing in the great detail in later discussions. By default, you will see all the functions, sans the Get, Design, Custom and External, listed in alphabetical order. Above the list you will find a pull down menu that will allow to to view a particular family of functions exclusively ( just the text functions, just the logical functions, etc...). This pull down menu is the only way to see the Get, Design, Custom or External family of functions.

The large scrollable area in the middle is where you will be placing your calculation.

At the bottom of the calculation box is where you can define the result type of the calculation ( text, number, date or container ). There is also a button to define the storage options for the calculation. I discuss this in detail later in a section called “Discussion About Indexing.”

FYI... Both FileMaker Client and FileMaker Advanced can use custom functions but you can only create/edit custom functions using FileMaker Advanced.
=
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.

The Format Of FileMaker Calculation

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

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

In the FileMaker calculation dialog box, most users will have a point and shoot design mentality. It goes something like this...

- there is that thing I want to add to my calculation
- I have selected that thing by clicking once upon it (point)
- I double click that thing to add it in my calculation dialog box (shoot)
- I see my selection appear in the text area as text
- I edit the text string as need, perhaps even by pointing and shooting other things.

Very few of you, simply type what you need into the dialog box, because you are familiar with the syntax of the calculation you want to use. This reduces the time to enter a calculation and is a much easier transition to calculation documentation. Hugh, what documentation? You can type text into a calculation work area that is for comments only. You just have to proceed or end that string of text with a flag. Anyway, when you type in your calculation without using any of FileMaker’s helper buttons/menus, you can quickly type in comments to refer to later (if/when) you or another developer has to edit that calculation.

Anyway .... how about we start talking about calculation formatting?

A calculation in FileMaker has to be formatted correctly to get expected results. It is not that hard to do and it may be second nature to you. The calculation test format is familiar because FileMaker calculation formats are basically the same as regular mathematic formula formats.

To have a successful calculation, you need to determine what result you want to get and in what format (text, number, date, time, timestamp or container). Next you need to enter in the calculation itself and this is normally called the calculation expression.

As a general rule, the calculation expression is read from left to right. So a calculation of 2 + 2 * 4 would be calculated as two plus two and then that result is multiplied by four. So our returned value would be 16. This is because two plus two equals four and four multiplied by four equals sixteen.

Now we need to chat about parenthesized expressions or those calculations that are within a left and right parenthesis. Parenthesized expressions are calculated first and then the calculation goes back to reading from left to right. So a calculation of 2 + (2 * 4) would be calculated with the parenthesized expression first ( 2 * 4) and then that result is added by two. So our returned value would be 10. This is because two multiplied by four equals eight. The we go to the left of the calculation and add two, which of course is 2 + 8.

In following discussions, I will chat about the formatting of FileMaker functions. The format of a calculation can still take a turn when we start discussion operators. Then I will chat still later on ways to format complex calculations with multiple functions, multiple operators and parenthesized expressions to be easily read.

However when it is all said and done, your first and primary control over a calculation is the use of left and right parenthesis around expressions.

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

The Go To Layout FileMaker Script Step

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

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

This script step will take you to a layout in the current window / file.

You cannot go to a layout in another window unless to go to that window first. Also, you cannot have a GO TO LAYOUT step for a layout in an outside file. A way around this is to have your script, call an external subscript in another file, which in turn would have the GO TO LAYOUT step.

You also can specify a layout by it’s number in the layout order list. This is done by using a calculated value specified in the options of the Go To Layout script step. If you reorder your layouts however, this script could take you to the wrong layout.

You also can specify a layout by it’s name. This is done by using a calculated value specified in the options of the Go To Layout script step. If you rename your layouts (or don’t use unique layout names however, this script could take you to the wrong layout.

You also have the option to take the user back to the original layout. This is used in a script that has 2 or more Go To Layout script steps in it, to take the user back to the original layout when the script was first called upon.

There are times when you need to take ScriptMaker to a particular layout so that it can perform a set of script steps. For example, to use the GO TO PORTAL ROW script step, the portal needs to be on a layout it can be found on when that step is executed. If not, FileMaker simply passes over that step.

COOL IMPLEMENTATIONS OF IT
One of the most simple but yet very cool implementations I've seen is to go to a developers layout in a script. What you do is create one layout in all files that has all the fields and possible portals you use in the file. Then you always go to this layout when executing complex scripts and then use the Go To Layout (original layout) when you are done without any error notification to the user.

Another very cool implementation is going to layout by a calculation. That means you can react to any calculated result before going to that layout. I have a solution that allows a user to determine if they like to go to form view or list view when they go to a different module (such as going from contacts to invoices). I use the Go To Layout (calculated) to go to the layout that user prefers to end up on!

FYI...
It's not enough to take the user to the correct layout/screen. Always make sure they are in the right mode (Browse - Find - Preview).

Got To Know Factor - 8

Here you can see the options for the Go To Layout script step. You can go to a specific layout, a layout in the layout order as defined by a calculated value, go to a layout name by a calculated value or an original layout ( used when you a previous Go To Layout step and you want to go back to where you started).

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

The Basics Of The FileMaker Calculation

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

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

There are 3 main areas of knowledge and experience about FileMaker. These 3 areas involve relationships, scripting and complex calculations using functions. Mastering each of these areas is obviously going to make you a better FileMaker developer. Going beyond the individual knowledge of each of these areas, is the ability to use them in concert. Blending expertise in relationships, calculations and scripts are the advanced criteria to building superior FileMaker database solutions.

Typically we think of a calculation as 1 + 1 = 2. An invoice that totals up line items, tax and shipping charges is another classic calculation and one that you will find within many of a FileMaker solution. These types of calculations are just the tip of the iceberg and a careful study of FileMaker calculations can make your databases take a huge leap forward in productivity and ease of use.

Some calculations within FileMaker come pre-packaged and we call them functions. These functions have dedicated areas were you can plug in the fields from your database, literal text or even another calculation/function. A function will have a name, an argument (within parenthesis) and parameters (which are separated by semicolon characters). I will cover functions in greater detail in other discussions.

Calculations are not limited to calculation fields, in fact calculations take place in many areas of FileMaker design. There is no way I could cover all these in this one topic but let us take a moment to list them for you here.

A calculation can be used

- in calculation fields ( of course )
- to branch a script ( If script step )
- to control script loops ( Exit Loop If script step )
- to place data in a field in a script ( Insert Calculated Result and Set script steps )
- to replace data in a found set ( Replace command and script step )
- to auto enter information in fields when a record is created
- to validate that data entered in a field matches calculated criteria
- to set security privileges for records and fields
- to control relationship comparisons ( via the comparison operator or key fields in the relationship )

and it seems with every new release of FileMaker, calculations can be found in even more areas!
=
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.

FileMaker Launcher Files With Embedded Account Settings

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

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

Overall, I recommend that each user have an unique account name and password and a developer take the appropriate actions to make that a reality. However, not every situation is the same and here is a technique that you can use if you have multiple users using the same account settings and you have multiple files with single tables.

In fact, I strongly recommend against the technique I'm describing but it is important to know that you may run into this type of setup as a professional FileMaker developer.

The purpose of this technique is to create a FileMaker file with a default security account for one purpose ... to open another file with those security access settings. If one FileMaker file has been opened with the same account/password as another FileMaker file, when needs to open that other file ... it will use the same account/password. This helps keep the account/password challenge entry dialog box from opening up all the time as you navigate to newly open FileMaker files.

How it works...
If the invoice and the inventory files both have the account setup with the name of Sales and has a password of PointyHairBoss ...

If you open the invoice file with the account/password of Sales/PointyHairBoss...

If there is a portal to the inventory file, it will need to open the inventory file...

It will open the inventory file with account/password of Sales/PointyHairBoss automatically and the account/password entry dialog box will not come up.

FYI... As with any default password system, many developers will say that a launcher file is a potential security breach. For example, if the opener file were to get into the wrong hands, it would be a potentially major security breach. So for the above reasons, I would suggest this technique for the lower level security settings and not the higher ones.
=
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.

The FileMaker DatabaseNames Function

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

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

DatabaseNames
There is no parameter for this function
All Recent Versions Of FileMaker
Returns A Text Result

The DatabaseNames function provides the names of all open FileMaker database files at the time the function is executed. Each database file name is separated by a carriage return. If you are thinking about using this in script branching, you will probably have to use it in tandem with the PatternCount function.

The extension information is not included in the result of the function. This means that it is possible ( however remote ) that this function might break if you have two FileMaker databases open with the same name but different extensions. This could be the case when you are building runtime databases with FileMaker Advanced.

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.

The Open URL FileMaker Script Step

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

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

The Open URL Script Step opens a specified Internet URL address or performs other specified URL protocol commands. This means that you can have a combination of web and FileMaker actions within a script that you use in your database solution.

The Open URL script step will work with the HTML commands of http, https, ftp, file and mail to perform such things as

- http / https is used to bring up your web browser and go to a specific web page

- ftp is used to retrieve files from the Internet

- file is used to open a file that you specify via a path given

- mail to opens your email app and gets it ready to send a new email message

By default, the open URL script step will bring up a dialog box. In this dialog box, you can set the URL in a field in the dialog box or you can specify to use a field in the database. The first option is more of a user dynamically set option. The second is used more for designer control.

You can make the URL equal to a field. This means that you could have a different URL for each record. This would mean that when a user executed the script on a record, they could go to a different web page or download something from a FTP site. The designer can hard code in a URL that the script will go to when it executes. The end user can even be shielded from seeing the Select URL dialog box. The third way is to have the user enter in the URL via a pop up dialog box at the time the script is executed. For this to work properly, you will need to make sure the end user can see the Select URL dialog box during the scripts execution.

There is a small check box that allows the script step to perform without bringing up a dialog box. If this is chosen, the user cannot specify or edit the URL to execute while the script is running. This may or may not be what you want to happen, so you should plan ahead.

HOW IT IS USED
Creating a FileMaker database of bookmarks. Go to the web site of a listed contact, company or organization.

Got To Know Factor - 7

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

Indexing Of A FileMaker Field Explored

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

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

Indexing is one of the most fundamental aspects of relational databases. It's what makes searches possible, relationship key fields to function, sorting happen faster and a number of other technical things happen in the database system.

If you were reading a FileMaker book, it would likely have an index in the back. This index would tell you on what pages you may find a topic or sub topic. For example, the topic "indexing" may be listed on pages 12, 50, 79 and 112. Indexing, as a topic, would be spread out over that many pages because it may be discussed in detail or only in passing as other FileMaker related topics are discussed. In theory, if the index was not there, you would have to scan each page of a book (front to back) to see if the word or topic you're looking for appears. One can see why an index is a critical addition for books, especially those which cover a multitude of topics. On the flip side, we should look at the drawbacks of having an index in the back of a book First, it makes the book bigger since the printed index takes up physical space. The more words you index for the book, the more pages the book will be. Also, if you decide to add or delete a sentence, paragraph, page or chapter in the book, chances are that you will need to rebuild your index to make sure it's up to date.

FileMaker is similar to a printed book except that it's also completely different. (Indulge me, I just love saying things like that!) FileMaker indexes focus on the data within a field. So if you have a FileMaker field indexed, FileMaker can look at it and go to the associated records quicker than it can if it is not indexed. For this reason, indexing is a key player in searches, sorts, reports and relationships.

Now FileMaker 7 makes a distinction between indexing words and indexing values. An indexed value can and often is a collection of words. For relationships and calculations, the index works on the value. Word indexes are not used in relationship keys. Word indexes are typically used in searches.

So if a database has two records with one field. In one record the field contains “I win” and the other contains “I lose.” The value index would return “I win” and “I lose.” The word index would return “I”, “win” and “lose.”

As a default, FileMaker fields are not indexed when you create them. You can manually set the indexing options for a field by accessing the fields options ( File Menu - Define Database - Fields Tab - Options button - Storage Tab ). There are 3 radio button options for indexing which are All, Minimal and None.

All - The most complete ( and storage intensive ) option that uses both word and value indexing.

Minimal - FileMaker can index a field by each word or each value and the minimal index setting is using one of those choices. There is no clear indication to the user within this dialog box if the minimal index option is using the word or the value index. When you select the automatic index creation option, it will generally select the minimal index option.

None - Does not allow the field to be indexed.

The "Automatically Turn On" option is done via a check box and will index a field when needed but will not store the index in the database. FileMaker will automatically index a field when a find or save operation is executed. The more you use your database, the more it will grow as you build more fields, add more data and do find/sort operations on new fields. This increase in size could be a problem if your about to run out of hard drive space and have no way of increasing the available space on the hard drive the database resides upon. As you can probably guess, running out of hard drive space on a "live" database is not an experience you want to have.

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.

FileMaker Calculation Functions Helpful To Secure Design

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

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

Calculation functions can be used when customizing privilege sets, branching which steps a script can perform, determining access to a record and of course change the results of a calculation field. Here are some of the new functions that come to mind and ways you may ( possibly ) want to use them.

Get ( AccountName ) - returns the name of the account used to open the file. The account name is authenticated and ensures the user is ... who they say they are ( within reason ).

Get ( PrivilegeSetName ) - returns the name of the privilege set associated to the account used to open the file. This can be used to branch a script, when you don't want to change everything a privilege set does. For example, you may only want to change what a particular script does for users of a privilege set temporarily.

Get ( ExtendedPrivileges) - returns the extended privilege settings in a text carriage return based format that is associated to the privilege set associated to the account used to open the file. This could be used for logging activity of a user in a script or to branch a script.

Get ( SystemNICAddress ) - looks at all the network cards in the computer and returns the hardware address of each. When used in concert with Get(AccountName), you may be able to see if someone has "shared" their account sign in information. If a particular account name is be used by multiple NIC addresses, it may indicate you have a security problem.

Get ( SystemIPAddress ) - looks at all the network cards in the computer and returns the IP address of each. Much the same as Get ( SystemNICAddress), when used in concert with Get(AccountName), you may be able to see if someone has "shared" their account sign in information. If a particular account name is be used by multiple IP addresses, it may indicate you have a security problem.

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

The FileMaker FieldComment Function

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

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

FieldComment
FieldComment( fileName ; fieldName)
Introduced With FileMaker 7
Returns A Text Result

In FileMaker 7 and higher, you can add comments to any field. The comments field was added as part of the dialog box that you used to create and edit fields. The FieldComment function allows you to fetch data entered into the comments area of a field defined in the Manage Fields dialog box.

The default syntax for this function is to refer to the current table. If you want to use an outside table, you have to add a little something in front of the fieldname parameter ( nameoftable::fieldname ).

Here you can see the comments area that can be attached to a defined field.
=
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.

FileMaker Script Steps That Need To Enter Into A Field

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

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

There are also a number of script steps that need to enter into a field on the
current layout to perform. So in your script, you have to make sure you have a Go To Layout script step to insure that the needed field is there. So more than likely, your script will look something like this ...

Freeze Window (prevents screen flash going between two layouts)
Go To Layout (that you know has the field you need)
Do That Thing (one of the dependent steps mentioned below)
Any Other Steps You Want To Do While You Are Here
Go To Layout (goes back to the original layout

The following script steps (and this list may grow as new versions of FileMaker come out) need to see the field they are working with ...

Copy, Cut, Clear, Go To Field, Insert Calculated Result, Insert Date, Insert Time, Insert User Name, Paste and Replace.

Also the script step of Relookup needs to be able to enter into the parent key field of a relationship to perform the relookup operation.

Generally, you don’t get any kind of error from FileMaker when this happens ( unless you are trapping for it ). So this can be a hard bug to troubleshoot.

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

FileMaker Storage And Repeating Fields

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

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

Once again I feel the need for a recap. You know that FileMaker fields have a dialog box for setting options such as auto enter, validation and storage. Auto enter allows you to create a set of conditions in which a field is automatically populated with data you selected. Validation is where a field can be verified that it contains the data or type of data you want within it. The storage options area is a flexible way to control how data is stored.

The repeating field is one of those “unique and flexible” storage types. In fact, it is so unique, you probably won’t find anything like it in most other database systems.

A repeating field can be used with any type such as text, number and date. The only exception to this the summary field type. The storage option of repeating fields allows you to have multiple cells of information within the same field. Once again, as far as I know, FileMaker is the only database application that has anything like repeating fields.

To make a field a repeating field, you select the field, click the options buttons, click the storage tab and in the storage options area, you define how many repetitions you want the field to have. There is one additional step you need to take when you put a repeating field on a layout. You have to tell FileMaker how many of the repetitions you want to show on the layout. This is done in layout mode ( of course ), you click on the field and choose the field options selection from under the Format menu.

Repeating fields were popular before FileMaker went relational and portals replaced repeating fields in most FileMaker solutions. Portals have significant advantages over repeating fields. Portal data is easy to search upon, report upon and write scripting events with. So you might wonder why repeating fields are still around.

As you might know, databases can become complex. There are databases that were built with repeating fields and those databases rely on them. Forcing every database owner to rewrite repeating field logic to portal logic would be a tough decision. Also, the act of converting this data isn't always easy to do.

Another good reason is those crazy FileMaker developers started using them for all sorts of things they were not designed to do ... and ... some of those things they did turned out to be downright useful. Repeating fields can be used to hold graphics, sounds, movies and even buttons in a dock like appearance.

PLEASE NOTE: If you are up on your relational design theory, you might remember about a little thing called the rules of normalization? For a database to comply with first normal form standards, it must only contain one value. As you can see, repeating fields are a slap in the face of the first normal form rule.

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

Controlling The FileMaker Found Set Of Records

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

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

Say you want to limit a particular user to access of their own records. FileMaker has a record level security setting, so a user cannot see the information on a record based upon a calculation. However, the user can still get to that record. This is not a security problem but it is a user experience problem. A user might have to view a large number of "access denied" records before they can get to the ones they can read.

You want to limit interface shock when designing a system. Looking at a record in form view or a set of records in list view that you do not have access to ... is ... well ... not very warm and fuzzy. Wouldn’t it be better if the user only saw records they have access to? In other words, when they go to a table, all records they do not have access to are in the omitted found set.

To do this, you may need to work with a self relationship and make use of the script step of Go To Related Record. You will need to build your own controls for going from one record to the next and how the Find command works on a log-in user name. Here is a list of some of things you will need to consider in controlling the found set of records ...

- a relationship that ties the account name to the name of a record creator
- a script from the successful account that goes to those related records
- hiding the status area & locking it so users cannot see the actual record count
- creating buttons to go to each record
- build a search screen and add the users name to all searches and add constrain found set parameters
- add a show all records button that only shows related records
- build your own display of record count and found set

FileMaker makes this much easier than earlier versions. For starters, you can have multiple tables in one file. You can have multiple windows open at the same time. You can have a button pass data to a script. This means a button can be made to branch a script, without duplicating and rewriting the script.

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