Globals + Multi-Keys = WOW!!

[LaRetta]

Well, now, this one woke me from a sound sleep! I had spent the evening reviewing my understanding of global fields and multi-keys and it really hit me, thanks to CobaltSky and Vaughan!

CobaltSky -- But that doesn't stop you from transferring a value from a record to a global field (where it will then be 'seen' from all other records) ... the records may not be related but you can use this technique to script a 'transaction' between them anyway.

Vaughan -- It gets even better when you realise that the multiple keys can be put into a global field.

And these two together can be used to create one- or many-to-many relationships, up-to-date value lists and lookups (even in unrelated dbs) ... and ... passed through ZIP FAST to anywhere! Almost unlimited power! Wow! It makes calcs look like slow resource-hogging (if stored), inflexible dogs! There isn't anything you can't do!

Pleeeese guys, say more about this! Can you give me some examples of the use of either/both/together? I know. I get pretty excited about this stuff!

[andygaunt]

OK, here is a use. Portal Filtering

Say you have an activity database tracking all your telephone calls, notes, emails etc. These each have an activity type, a date and a user associated with each of the activities.

Now, say you have a portal that displays these records.

With a multi key in the activity database you can have many combinations for showing the information.

For example:

Activity For & "|" & Activity Type & "|" & DateToText (Activity Date) & "PP" &

"ALL|" & Activity Type & "|" & DateToText (Activity Date) & "PP" &

"ALL|ALL|" & DateToText (Activity Date) & "PP" &

"Activity For & "|ALL|" & DateToText (Activity Date)

Now, this is not all the possible combinations, but it gives you an idea.

OK, now in your other database, you have three globals and a calculation that combines them.

gActivityFor, gActivityType and gActivityDate.

Now, combine these into a calculation.

gActivityFor & "|" & gActivityType & "|" & DateToText (gActivityDate)

Now, because you can use globals or unstored fields on the left of the relationship, you can relate the calc to your multikey and now you can filter your portal to display any information you want, simply by changing what is in the global fields. So you can see all the activities for a user, a particular type of activity for a user, even all activities for all users on a particular date, week, month.

Together they are a powerful tool in your filemaker arsenal.

Get to know them and use them.

[LaRetta]

This is great stuff Andy, thanks! I just realized I just discovered FileMaker 39 days ago, I'm working 2 full-time jobs plus designing this program and I just can't learn fast enough to suit me! FileMaker is incredible and so is this forum! Thanks for the ideas ... I can't wait to try (and master) them!

[CobaltSky]

Further to Andy's excellent advice, you might like to take a look at the inner workings of a demo available from my web site, at:

http://www.nightwing.com.au/FileMaker/demos/RangeFinder.zip

Significantly, the current version of the demo uses multi-key data in global fields. The file shows one of the simpler ways to bring up a range of records in a portal.

Whereas the technique Andy refers to 'filters' or 'narrows' the group of records which will be displayed in the portal, the range finding technique opens up or 'widens' the range.

By combining both techniques, a very high level of prescision and control over the display of related data can be achieved.

[LaRetta]

Thanks Ray! I've downloaded and perused RangeFinder but haven't had the time to 'peek under the hood.' You can bet I'll do that NOW! And, truthfully until this morning, I doubt I would have fully understood the power of what I was seeing anyway (since I'm still amazed on simpler things like TextToDate

So many ways to accomplish the same thing in FM ... finding the simplest and most elegant method is the fun part! Clearly visualizing the power and potential will probably take me a lifetime.

[Vaughan]

Just a quick rality check: remember that all text fields have a limit of 64,000 characters, so there *is* a limit to what you can do with multiple key fields.

It comes up mighty fast if you have long key values.

I recently hit the 64k limit in another project. It wasn't a multiple-key solution (I was using a global field to build up html code to export into a text file) but it made me realise that 64k isn't an awful lot nd it can sneak up on you.

[LaRetta]

One final question! So I can place the globals above a portal (for instance a To and From global date range as in CobaltSky's RangeFinder) for User entry --- but could I ALSO put buttons above the portals which activate scripts to populate other globals (even unseen globals), or run validation checks to populate the globals (like unbilled services)? I've just recently discovered that sorting is also possible (I think)!

[andygaunt]

Absolutely,

If you use those globals within the relationship for the portal. Of course they do not have to be linked to the portal, you can do whatever you like with them.

But yes to answer your question, you can use scripts to populate the globals (even unseen ones).

And yes, sorting a portal is possible (either from a single field or dynamic portal sorting from a range of fields)

[mse]

OK, here is a use. Portal Filtering

Say you have an activity database tracking all your telephone calls, notes, emails etc. These each have an activity type, a date and a user associated with each of the activities.

Now, say you have a portal that displays these records.

With a multi key in the activity database you can have many combinations for showing the information.

For example:

Activity For & "|" & Activity Type & "|" & DateToText (Activity Date) & "PP" &

"ALL|" & Activity Type & "|" & DateToText (Activity Date) & "PP" &

"ALL|ALL|" & DateToText (Activity Date) & "PP" &

"Activity For & "|ALL|" & DateToText (Activity Date)

Now, this is not all the possible combinations, but it gives you an idea.

OK, now in your other database, you have three globals and a calculation that combines them.

gActivityFor, gActivityType and gActivityDate.

Now, combine these into a calculation.

gActivityFor & "|" & gActivityType & "|" & DateToText (gActivityDate)

Now, because you can use globals or unstored fields on the left of the relationship, you can relate the calc to your multikey and now you can filter your portal to display any information you want, simply by changing what is in the global fields. So you can see all the activities for a user, a particular type of activity for a user, even all activities for all users on a particular date, week, month.

Together they are a powerful tool in your filemaker arsenal.

Get to know them and use them.

Still having problems with jargon. Could you please attach an example file.

Newbie burdened with a 15 file-size db