ISO FileMaker Magazine: FileMaker Video Tutorials, Templates, Help & More

For those of you who have recently tried to access the sample files posted through out the blog, I appologize but we recently swapped hosting providers and I forgot to transfer all the files for the domain on which they're hosted.

Anyway, I managed to locate them, upload and all links should be working once again.

Alright guys, been busy so pardon the greater than one month gap. This is more on the whole numeric addressing issue we have with repeating vars and fields... its really annoying and puts limits on what can be achieved.

So, we want to simulate text based key's that are available in every programming language known to man - one method I don't mind (though i haven't extensively run speed tests on it):

Custom Function: getValueFromArray( array ; key)
Let( [
newList = Substitute( array ; "\," ; "|||" ) ;
newList = ¶ & Substitute( newList ; "," ; "¶" ) & ¶;
newList = Substitute( newList ; "|||" ; "," );
keyStart = Position( newList ; ¶ & key & "=>" ; 1 ; 1 ) ;
valueStart = keyStart + Length(key)+3;
valueEnd = Position( newList ; "¶" ; valueStart ; 1 )];
If( PatternCount( newList ; ¶ & key & "=>" ) ; Middle( newList ; valueStart ; valueEnd - valueStart) ; "?" )
)

Method for setting the key / value array:
$array = "key=>value,sentance=>I am a value\, values are cool!!,key3=>\"value3\""

Now, so lets say we wanted to extract the value for sentance from that array:
getValueFromArray( $array ; "sentance" ) which would return: I am a value, values are cool!!
getValueFromArray( $array ; "key3" ) would return "value3".

I hope this has been a fun and eductational experience ;)

Cheers guys, will try build a bit on this and run some speed tests over the next few weeks, in the mean time, behave yourselves!!

So I haven't blogged in the while - been busy with something i'll tell "you's all" about later. As I was sitting there I thought, what better reason to blog than to try and reason out why Bruce Willis is as bald as he is. Well, I was going to but thought Matt might get upset... so instead this post will breifly discuss data types and how the IsValid() function mixes it all up (a bit of a let down i know :p ).

The thought to write about this came from an auto update plugin ... article type thing... at Six Fried Rice recently - love these guys and somehow, they get their posts to flow in more of a straight line than mine. Anywho, it's a bit of a weird function and while it may be obvious to some, its workings can often escape logical reasoning for no logical reason.

Now, the purpose of IsValid() is to determine whether the contents of a data container (i.e. a field or variable) match the data container's data type. With fields this is farily obvious:

How It Works with different Data Types:
1) Text - Essentially it will output valid for everything..
2) Number - Will output false if a text string alone is present in the field i.e. "abc" will return 0, while "abc1" will return true.
3) Date - Will output false for any non-conforming date (example in MM/DD/YYYY) 18/3/2007 will be rejected -- one of the more useful options
4) Boolean - Outputs valid pretty much always.
5) Container - Like text, not really useful... unless you skip validation somewhere.

Now with fields where the data type is already defined, it really has little use in most cases, so when do you use it?

One potential place is scripts. Think date inputs into a custom dialog, you don't want to manually have to validate this sort of stuff or worse yet let it slip past: IsValid( GetAsDate( input ) ) is your functional statement (*note to self: don't make up terms*). GetAsDate( input ) immediately tells IsValid() that you are checking for the validity of the contents of the input field using the date data type - IsValid( GetAsDate( "17/17" ) ) for example, returns false.

Now - here's where a lot of people slip up, data type conversion can happen relatively unoticeably in various places in FileMaker. The most common form though is concatenation - concatenate something and it immediately becomes a string.. i.e. text data type.

An interesting note about variables:
When assigned, they conform to the data type of the object to which they are being assigned. For example if number was a number field: Set Variable[$number ; Value: table::number ] would create a variable $number with the data type of a number. If $number was a variable that had been dynamically cast somewhere then Set Variable[$number2 ; Value: $number] would set $number2 to be a number type variable.

An interesting note about Script Parameters:
After a bit of experimentation, curiously enough, they are always passed as text data.. intriguing.

PS. Die Hard 4 = Awesome!!

By the way, feel free to leave comments if you want... seriously it'd be good to know if anyone's benefiting at all from my ramblings or how bad my speeling is ;) .

Alright so I like Harry Potter :)

Before we start, so that no one spends time reading something that won't be useful to them, the final purpose of this post is to achieve a report like this.

So, to the Order of the Portal Printing Taboo...ers: It seems that printing portals is about as Taboo as using repeating fields these days (talk to Soren if you want proof of the repeating fields part of that statment ;) – you’ll find him floating around on FM Forums).

I'll agree that in most cases printing portals isn’t appropriate (just like repeating fields) – the most common misuse for example is by new users who, blissfully unaware of the standard FileMaker reporting method (we'll refer to as "list view" reporting), use them purely because it’s the only way they have seen of listing related data.

On the other hand, what about those users who know what they’re doing? What about those users who long for FileMaker to integrate the sub-reporting functionality available in other Reporting / Database tools such as MS Access. Look inside your hearts lol – shouldn’t they at least be given a glimmer of hope?

When to use the technique

A common use might be a summary report about a person within your database: phone numbers, addresses, family member names, contact reminders, general notes, contact made or a million other things. Our example (downloadable at the bottom as well as an example report) uses a Company example.

How It works

Well, here’s the aim of the technique: Display related information about a record whilst hiding a) the portal and b) the column / section headers when they aren’t required due to a lack of related information.
Before I go into the details, Sliding: If you select an object in Layout Mode in FileMaker, go to Format -> Set Sliding / Printing. For the purposes of this exercise, select: Sliding up Based on All Above, Also reduce size of enclosing part.

a) Hiding The Portal
The method for this has been provided to us by FileMaker – It’s called Sliding (kind of like the 1990’s TV series Sliders but not really). The idea behind sliding is kind of like the idea behind shrink wrap: You make your container as high as you reasonably think it will ever have to be (you have a limit of 12 pages) and sliding will vertically compress the container holding the information to the size that allows the information to just fit within. Please note that I use the term container loosely to refer to all items that contain information including Merge Fields, Fields and Portals. If sliding is set on a portal where no related information exists, it will *pop* into non-existence as if it was never there.

b)Hiding The headers (the cool part)
Okay, so that’s great but you all knew that right? Now I’m sure this thought has occurred to some of you but let’s read that last part again “If sliding is set on a portal where no related information exists, it will *pop* into non-existence” – It stops existing, nada, zilch, gone, disappeared and any other dodgy synonyms you can think of.

So okay, I’ll ask you to consider this, what would happen if we displayed only the first row of a portal and shoved some information in there like … oh maybe column / section headers. What would happen to that portal row if no related information existed in the numbers table – It would like our other portals, simply pop out of existence. So there’s your solution: Put your column headers into a one row portal (from the same TO as the main portal below it)… Set sliding to on for both the header portal row and the main portal and voila – No related records, no portal rows!!

Samples:

The Report
The FileMaker File

The Pro's

Okay look there are 3 or 4 features that I would really call quite cool with this update:

1) SQL Connectivity
2) CWP packaged with Server Standard
3) ScriptMaker Overhaul
4) Conditional Formatting

1) SQL Connectivity
Well, this is one of the new features that MAY and I stress MAY, give FileMaker the right to charge people the cost of upgrading. With SQL Connectivity, FileMaker can finally trully connect to Enterprise class database systems and actually exchange data with MySQL, MSSQL and Oracle - Something that competition has been able to do for years. At least the IT guys might be a bit nicer now - then again they aren't that nice to Access either. Anyway, point being, great feature, finally possibly brings FileMaker to par with MS Access (though the lack of thin clients and the overall cost still leaves it lagging behind in the Windows world).

2) CWP Packaged with Server Standard
Well, this one is actually quite great - The FileMaker XSLT and XML engine is now pacakged with FMS Standard departing from the previous idea that a developer should have to fork out the cost of FMS twice again to bring their content to the web with FileMaker Server Advanced. This means that we may not see the demise of FileMaker's web publishing abilities (to be replaced by any of the SQL databases now advailable) for a while yet.

3) ScriptMaker Overhaul
Good feature for development I'll admit, though it really should've been there long long ago. This allows you to group your scripts into Folders for better organisation, and finally its non modal (i.e. you can have multiple scripts open at once, and work on the db at the same time) -- too bad they apparently didn't have the time to do the same thing for layouts

4) Conditional Formatting
Nice -- saves you adding unecessary calc fields all over the place, a simple feature but saves clutter in tables helping to make FileMaker more of a database by limiting the number of fields you use for "display purposes" - This may also reduce the reliance some have on the seperation model to keep their data and their display fields seperate (Then again you could just build the structure in MySQL and use FileMaker as a plain ol' front end.

The Cons

Frankly at this point, I think FileMaker is just trying to pull extra money in without really providing a new product. The lack of the rumoured Thin Client is also a shame, as are the event triggers, the dynamic set field, my progress bar (god i want one lol), copying custom functions, using custom functions over different files and finally the simple short cut keys for things like custom function definitions and all those other menu items that are missing keys etc. I would just like to express my opinion that the resizing which was apparently causing their developers problems is really little compensation for all the small features above that would make the product so much better and would likely only have taken a comparably short time to implement.

A conclusion

Oh well, I'm updating in either case just for the sake of it, but don't see any real reason to update existing clients sitting on FM 7 / 8 / 8.5 given that it's not really providing that much in the way of new.

Last thing -- this release should have been green!!!

For a decent look at the new features see:
http://sixfriedrice.com/wp/category/filemaker-9-tips/ (plus they have an API for integrating FM CWP with Ruby... not that i really use ruby, but the more technologies the better)

And for a look at the opinions of other developers:
www.fmforums.com

Well I hope my slightly random rant was informative.

Hi Guys,

I recently had the need (or more likely the desire) to set global variables dynamically..

As most of you will know, Set variable allows you to specify a dynamic value and repetition, but not a dynamic name for your variable. This often leads you to store info in a delimited list within one repetition when you need to store multiple peices of information per one subject.

This will soon make your scripts become messy and the logic harder to follow

Hence two small custom functions:

Custom Function: SetGlobalVariable( name ; value ; repetition)
Evaluate("Let( $$" & name & "[" & repetition & "] = \"" & value & "\" ; \"\" )" )

Example Usage: SetGlobalVariable( "record_" & table::primaryKey ; table::aField ; 1 )

If your primaryKey field contained "991" and your aField field contained "Alex!!" -
This would be the equivalent of using the script step: Set Variable( $$record_991[1] ; Value: "Alex!!" ) ..
or otherwise writing: Let( $$record_991[1] = "Alex!!" ; "" )

Which means that if you were to now retrieve the value of $$record_991 - you would get "Alex!!" as the value returned.

Call: GetGlobalVariable( name ; repetition)

Custom Function: GetGlobalVariable( name ; repetition)
Evaluate("$$" & name & "[" & repetition & "]" )

Example Usage: GetGlobalVariable( "record_" & table::primaryKey ; 1 )
Which would (where primaryKey = 991 and the variable was set as above) return "Alex!!"

These examples aren't great, but they certainly simplify my scripts and I hope they'll help someone else out.