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

Thursday
Apr102014

A READER ASKS: Go To Next Portal Doesn’t Seem To

From Dwayne Wright PMP
Certified FileMaker Developer

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

A READER ASKS

I am attempting to copy data from one portal to another using a loop to copy each line of the first portal. It copies the first line OK but does not go to the next row. It just loops around and around the first row. I just can’t see what is wrong.

I am using Filemaker 9 Pro, code attached. I would be very grateful for any pointers.

Enter browse mode
Set Variable[$ClientID;Value:Invoice::ClientID_fk
If[not isEmpty(getField(INVOICE::ClientID_fk]
End If
Go to Object[ObjectName: "InvoicePort"]
Go to Portal Row
[Select First]
Loop
Set Variable [$Quant;Value:LINEITMS::Quantity]
If[not isEmpty(GwtField(LINEITEMS::Quantity))]
End If
Set Variable [$Amt;value:LINEITEMS::Amount]
If[not isEmpty(GetField(LINEITEMS::Amount))]
End If
Set Variable [$Desc;value:LINEITEMS::Description]
If[not isEmpty(getField(LINEITEMS::Description))]
End If

#Dump information into ROA table
Go to Layout["ROA"(ROA)]
Go to Object[Object Name: "ROAPortal"]
New Record/Request
Set Field[ROA:ClientID_fk; $ClientID]
Set Field[ROA:Quantity; $Quant]
Set Field[ROA:Amount; $Amt]
Set Field[ROA:Description; $Desc]
Commit Records/Requests
[No dialog]
Go to Portal Row
[Select;Next;Exit after last]
End Loop

-------
DWAYNE RESPONDS
The Commit Records script step is the problem. When you commit a record, you exit the record as well. This takes you out of the portal completely, so the Go To Next means that since you are not in the portal anymore (due to exiting the record), you are going to the first row again.

Before you exit the record, set a variable equal to the portal row number. Commit the record, go back to the Portal Row number by using the variable information and then use your Go To Next Portal Row [exit after last]. You could also go to the portal row variable plus 1 and then tweak your exit loop conditional.
=
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.

Wednesday
Apr092014

A READER ASKS: Date Picker For Imported Records

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 or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).

A READER ASKS
How can I incorporate a "date picker" to an import? This way I could attach a billing date to the "Billing_Data" so I could import files from many months.

-------
DWAYNE RESPONDS

Well, you would want to add a field to the Billing Data table called import date. Then you would want to create a global field to hold that data temporarily. When the script does the import, use the Show Custom Dialog script step to give you the ability to type in the date you want included on those records. After the import takes place, you would use a Replace command to put the data from the global field into the new import date 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.

Wednesday
Apr092014

FileMaker Security Levels By Function

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

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

Generally speaking, security levels can be broken down into the following segments. This can be a handy way to look at security as you reflect how it pertains to the FileMaker solution you want to protect.

NO ACCESS - Some people should have absolutely no access to anything in a FileMaker file. If possible, they should not be able to find out the file exists. There are ways to hide a FileMaker file on a network to casual browsing.

NO ACCESS BUT INFORMATION LINKING - Some users should not be able to see anything in a FileMaker table. The user will need to have some access to the file because other resources they use do need access.

OBSERVATION & SEARCHING OF INFORMATION - A user needs to view or search for information within a FileMaker solution. The access level can be controlled to include some or all fields and layout screens. This can be used by individuals that check status such as reservations or as part of a web page that does the same thing. This security level cannot add, edit or delete data in the file. This level may or may not need printing privileges.

THE CREATION OF INFORMATION - This security level can open the table, click a new record button, enter in information and repeat the last two steps. They normally don’t need access to a lot of layouts, scripts or other files. This access may be used by temporary employees, new hires or users from other office departments.

THE CREATION & SEARCHING OF INFORMATION - A user needs to create new information and possibly perform searches. The access level can be controlled to include some or all fields and layout screens. These users may need to search the database to make sure that a record does not already exist before they type it in. They may need access to a data entry screen, a search screen and a search results screen.

RECORD ENTRY OR EDITING - This includes creating new records, searching for records and editing existing records. This is normally includes the folks on the administrative side of the fence. You know them, they are the ones with the keys to the office supply cabinet.

VIEWING OF DATA AND OCCASIONAL RECORD ENTRY - This can include users that can see information in some tables and add data to some tables. My online FMP 7 article database for example allows some accounts to view articles but does not allow new articles or editing of existing articles ... except for ... the feedback table. Here a user can add new records that supply feedback information about a particular article.

THOSE THAT BLESS - These are those that bless a record so it can go further up the administrative chain. An example would be the admin department blessing an order to go to shipping. These folks may need to perform searches and they may not. Many times you can rig up a portal that allows them to batch bless records. Each portal item may have a button that allows them to see the specifics of a record in order to make a blessing based decision.

ALL ACCESS / NO DESIGN - These are the individuals that need access to all areas of the database but do not have or need designer level access. This is normally the upper level managers or department supervisors. You may still want to hide a thing or two from a design perspective. Giving any user too much access can cause design problems. Can you seriously think you can design a FileMaker file to anticipate what all users will do under all the diverse business situations that may arise?

DESIGNER ACCESS - Full access to everything. This goes beyond the master password. You also need access to the machine the files reside on both in the office and remotely from home.

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

Wednesday
Apr092014

What Can Go Wrong With Designing A FileMaker Solution On The Fly

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

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

You have probably heard the quote that some folks spend more time planning a vacation than they do planning for their retirement. You might even be saying to yourself, “I do that!” Well, I’m not going to publish a book or an entire blog site about retirement planning but I will tell you a little about the woes you can get into when a FileMaker solution is designed wholly on the fly.

Can you imagine building a house on the fly? Forget about those blueprints, they are simply a waste of time! You have almost completed building the house, when you realize that you forgot that you needed a basement. You notice that somehow the pipes for the hot water are running into the den and the bathroom has no electricity.

In the database world, you may realize a data structure design problem well into the project. You have to do major deconstruction and reconstruction to get it to work. Since it was all on the fly, you might or might not be able to remember exactly what each field, calculation, script, relationship and layout do exactly what. You are paralyzed with fear that if you delete something, it may very well break another aspect of the database solution.

And what about those FileMaker database solutions that have been worked on the fly by multiple developers, over many years and with literally no documentation? These are commonly called Winchester Mansion databases. The Winchester Mansion was built by the widow of the original maker of Winchester rifles. She built the mansion with fake doors, stairways that went nowhere and the like. The idea was to confuse the vengeful ghosts of the native Americans killed with Winchester rifles. From what I understand, additions to the mansion are ongoing and they still are designing it to the original specification of confusing the ghosts. So you can see how we can make the connection of a staircase that doesn't go anywhere to a FileMaker script or relationship that is never called upon.

In many cases, a FileMaker solution that has significant ... under the hood ... goo ... may need to be started over from scratch. If you have one of these "confused" databases ... and ... if it is a FileMaker 6 or earlier solution ... and ... you are going to be taking the plunge to the current version of FileMaker, I would recommend rebuilding the database solution from scratch. This is an excellent opportunity to do it right the second time and implement the best of the current versions FileMaker features.

In full disclosure, I will say that I’m a full time FileMaker Developer that works exclusively with customer database systems for a living. That being said, if you are in house developer, I would recommend get some consulting time from a professional FileMaker developer. The great majority of the time spent with a professional, pays great dividends whether or not you end up doing 100% of the design and programming.
=
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.