How do you display a portal based on a relationship (Quote Number > Quote Number), but only display the quotes based upon another field which says "To Order"?

More Info:

I have a quote database that generates a quote number. The list of items for that quote are written to a Bridge file and carries the qutoe number for each item. There's a status field that is set to "Quote" at this stage. If I go back to the quote and wish to order a selection of the quote only, then I change the status of each of the items I wish to order to become status "To Order".

Then I go into my Orders database and it generates a serial number (Order Number) and asks me for the original quote number which then displays all of the items that correspond to that number. However, I want to display all the items under that quote number but only the ones with status "To Order".

This means that the portal link to the Bridge file should be based upon two fields ie: Quote number and Status fields. How do I set-up a multi field relationship to allow my selection of things displayed to be narrowed down?

I've tried:

Creating an external script in the bridge file which does a find of all Status "To Order" hoping that when I enter the quote number it would display only from the shown records in the other file but this does not seem to work.

All of your ideas will be deeply appreciated as this problem would solve another million other problems with finding precise results using portals.

Thanks for reading - look forward to your response!

Your relationship can't be based upon more than one field matching one field. Quote Number just isn't the field you need to use. To do what you wish you need the concept of a compound key, a calculated field which combines two fields. This new field might be:

RightCompoundKey (calculation, text, indexed, ASCII) =

To Order & "-" & Quote Number

This field would be most useful on the right side of the relationship. For the left side, you could define a fixed selection for "To Order" or make it selectable based upon a global.

LeftCompoundKey (calculation, text, indexed, ASCII) =

"1" & "-" & Quote Number <-- for a fixed "To Order"

or for a selectable option:

LeftCompoundKey (calculation, text, indexed, ASCII) = gSelect & "-" & Quote Number <-- Set the global field "gSelect" in a script

This technique along with the use of MultiKeys is really the power of relational FM.

-bd

Thank you for your answer, I'll try it when I get back on the case.

Matt