Sort Column & Toggle

# Sort Field <- field name
If [ ]
Else If [ Get ( ScriptParameter ) = "Seq" ]
    Sort Records [ Restore; No dialog ]

Else If [ Get ( ScriptParameter ) = "Project" ]
    Sort Records [ Restore; No dialog ]

Else If [ Get ( ScriptParameter ) = "Title" ]
    Sort Records [ Restore; No dialog ]

Else If [ Get ( ScriptParameter ) = "URL" ]
    Sort Records [ Restore; No dialog ]

End If

# TOGGLE TOP/BOTTOM
If [ Get ( RecordNumber ) = 1 ]
    Go to Record/Request/Page [ Last ]
Else
    Go to Record/Request/Page [ First ]
End If

Format the column title as a button, setup to perform the script which takes the field name as parameter and sorts by it, then goes to either the first record or last record alternately.

Date Arithmetic

Here is one way to get a new date from the date field d of table t, for instance, the date which is 1 year and 180 days later (in this simplified example the field to be set is unspecified):

Set Field [ Date ( Month ( t::d ) ; Day ( t::d ) + 180; Year ( t::d ) + 1) ]

Also, FYI:

Tests of entries into three different fields of type Date, Time, and TimeStamp, respectively:

command-dash in a Date field enters the current date.

command-dash in a Time field sounds a beep.

command-dash in a TimeStamp field enters the current date, then a warning message on commit.

Insert Current Date enters the current date into a Date field. Into a Time or TimeStamp field, however, Insert Current Date enters the current date, then a warning message on commit.

Set Field [Get (CurrentDate)] enters a valid result, albeit midnight, into the TimeStamp field.  Into a Time field, however, the result is a strange entry (e.g., 17608092:00 AM).

Set Field [Get (CurrentTime)] enters the wrong date into the Date field. (In this test case it was yesterday’s date that was entered.)

Set Field [Get (CurrentTime)] entered the correct time but wrong date (e.g., 1/1/0001 11:05:09 AM) into the TimeStamp field.

Set Field [Get (CurrentTimeStamp)] enters valid results into all three field types.

In a calculation field for a date result, adding or subtracting whole numbers from a date field returns a date so many days ahead or behind, respectively.  Likewise, in a calculation field for a Time or TimeStamp result, adding or subtracting whole numbers returns a time so many seconds ahead or behind.  

Using Set Field to add or subtract whole numbers to change days in Date fields or seconds in Time or TimeStamp fields works as well.

Relief from ARSe pain

Version 10 has a new feature that can make it a pain to edit records that you’ve put into a certain order for editing. Because, if you edit any of the fields in the sort order, the records will likely change position surreptitiously.  I call this the AUTOMATIC RECORD SORT event.

Here is one workaround (using an extra field and a script):

1. Create a extra number field to hold the previous position, call it, for example, prepH, for previous position Holder.
2. Create a script called prepH which replaces the field contents (Replace Field Contents) of the prepH field, and sorts it.
3. Before touching any of the records you want to edit,  execute the prepH script.

Now, you are free to edit the other fields in those records without ARSe discomfort.

Get ( LastMessageChoice )

So, if this function gets the value of LastMessageChoice why is there no Get ( MessageChoice ) function?

Wouldn’t a Get ( DialogChoice ) function make more sense, and be easier to find in that list of functions?

Automatic Record Sort Event

Where’d that record go? I was just editing it!

Surprise! Version 10 now changes record order before your very eyes, without even asking.

I hesitate to call this new feature the dumbest thing ever, but, it is. If I wanted to sort automatically, I’d use those highly touted script triggers.

The Automatic Record Sort event pulls the rug out from under Filemaker’s claim of intuitive performance, and signals a downturn in the efficacy of its new releases. Useability testing of this new version has obviously been short changed.

Here is one example of its inconvenience:

You have a list of records to edit.  You edit one record and go to the next using the “Go to next record” button.  A couple more clicks later, the same record appears!  ”Didn’t I just edit that record?” you ask. The ARSe put your already edited record further down the list!

DIGFM – April Meeting

Database Interest Group for Filemaker – 4/30/2009

Rick Aguirre is a senior certified FileMaker developer at Beezwax. He presented his advanced development techniques in FM10 using a template he created called PiVOT.

Find

I have a URL database with lots of urls listed. If I search in the url field for yahoo or yahoo*, the result is three records:

• google.com/yahoo
• yahoo.com
• yahoo.com/profile

If the search field is *yahoo* then the result is:

• billing.yahoo.com
• google.com/yahoo
• yahoo.com
• yahoo.com/profile

But, if the search field is *yahoo then only this is returned.

• google.com/yahoo

Make sense?

Allow Toolbars – Script Step

The Allow Toolbars[off] script step will grey-out (deselect) the menu option, and remove the formatting toolbar if it had been selected. Allow Toolbars[on] will show the toolbar ONLY if it had been previously selected.

Files always open with Formating Bar enabled and selected/showing.

If the Formating Bar is unselected from the menu, then Allow Toolbars[On] will enable Formating Bar, but not select it. Allow Toolbars[On] will show/select the Formatting Bar only if Allow Toolbars[Off] had previously disabled a selected/showing Formating Bar. In other words, Allow Toolbars[On] will enable but not show a Formatting Bar that had been deselected from the menu.

In Layout Mode, running any script takes you into Browse Mode, so the formatting bar is not scriptable in Layout Mode, but the menu option works as expected.

AGE field calculation

Calculate elapsed time in years between two dates.  For instance, what is the age of a person today when the date of birth is in the BIRTH field.   Also, consider the possibility that there might be a date value in the DEATH field.

If ( IsEmpty ( DEATH ) ; Year ( Get ( CurrentDate ) – BIRTH + 1 ) – 1 ; Year ( DEATH – BIRTH + 1 ) – 1 )

Power of 10 Tour

 

Filemaker Auditorium, Santa Clara, California

9 AM, Tuesday, March 10 … more to come.