Using two relationships to find data.

[franklin1232]

I have two databases. One has a unique serial number field. The other has the serial number field, but it is not unique. Thus a one to many relationship. I then have a machine number field in the second database. The Serial number and the machine number combine to make a unique record. How can I use both fields to return only the single record I am looking for? I was thinking if the user finds the serial number first then the relationship portal could return say 5 records, but if I then prompt for the machine number I could filter out the 4 records I don't want. I just can't help thinking there is an easier way than to script all that. I do in other database all the time, but I am still wet behind the ears with Filemaker! I using 5.0. My upgrade is on the way.

Thanks

[LiveOak]

A very powerful technique in FM is learning to use relationships instead of finds. Display in portals and the "Go to Related Record" script steps are much faster than finds for large data sets. What you are asking for is a "filtered portal". To accomplish this you need to create a "compound key". That is a key made up of several fields. The key in your main file might be made up of a global field for machine number and the serial number field of the record you are on:

gMachine# (global, number)

Key (calculation, text, indexed, ASCII) = Serial# & "-" & gMachine#

In your related "many" file:

Key (calculation, text, indexed, ASCII) = Serial# & "-" & Machine#

If you base a portal on this relationship, it will show all the records with the serial number of the record you are on AND the Machine# you have entered into the global field.

The dash is included as a separator and the indexing set to "ASCII" instead of "English" to prevent a curious problem. If you just concatenate serial number 1, machine number 11 (111), you can't distinguish this from serial 11, machine number 1 (111). To prevent this type of error you can either pad out the numbers to fixed lenghts before concatenating or use a separator such as a dash (a space will also work and has some indexing length advantages).

-bd

[franklin1232]

Thanks a million. It's working like a charm. Only 20 more database to go.

[Anatoli]

RE: Display in portals and the "Go to Related Record" script steps are much faster than finds for large data sets.

I believe there is not so much difference, but everything depends on overall design and functionality.

Two my examples of usefulness and complete fiasco with portals:

1. Remote factory connected by only 128k lines. Classic FM will be slow. I put small FM database to factory and set portal to display order detail. As soon as user enters order ID, the portal is connected to the main office server and order details are displayed almost instantly in portal. Instead of sending the whole layout with 20-200k of data, we are just getting the data in size 1-5k.

2. Fiasco on web with portals. Instead of founding, sorting and displaying articles for each month, I set up portal, which is auto-sorted.

WebCompanion served that 10 or more times slower. It really depends on number of records in portal. When it was up to a 5-10, it worked. Above 50 it was painfully slow.