I have a question about using match fields in portals. After searching the forums here I can't see than anyone has talked about this before but I could have missed something. Its really a simple problem but I just can't see any way to accomplish this so any help would be greatly appreicated. I'm somewhat new to FM but am fairly comfortable with its complexities, so bring it on!

I have 3 tables in different files, one called Items, the other called Orders, and another called Customers. In the Customers file I have a a portal showing all of the orders for that customer. In the portal I have several fields showing the items and those items are actually fields from the orders table. The Orders table has fields that are then lookups into the Items table.

The idea is this, display a value list in the product code field in Customers portal. When an product code is picked from the pop-up value list all of the other data from that item is filled into the portal row (price, description, size, etc). Price, description, size, code are all fields in the orders table and they are all lookups into the items table. The produce code field is the match field. All this works very well.

My question is this .... is there a way to have multiple match fields in the portal? How can I create another match field, say for the description, which if filled in will cause the lookup to happen?

The documentation states that each lookup field is only triggered by one match field. Is there a calculation scheme that could be used or another method by which I can use multiple independant match fields?

Thanks,

Brian

Hi, Brian.

Is there a calculation scheme that could be used or another method by which I can use multiple independant match fields?

This is exactly what you want to do. FMP6 is a little annoying in that multiple-field match criteria per se are not possible. You will want to make a calculation field on each side of the relationship that is equal to a concatentation of whatever key fields you want to use.

You might also find it helpful to multi-key the right-hand side of your relationship. That is: Say you want to create a concatenated match field as i just described, and you want it to be based on Name & Color. So you make the concatenated fields on both sides. But what happens if the user wants to select all colors for one particular name? Simply selecting a Name (on the left side) will not produce any matches, because all the right-side fields have a name and a color. So, for your right-hand side, you might INSTEAD have a calculation like:

Name & "" & Name & Color

Which would make the result something like:

Toyota

Toyota Silver

This type of multi-key will allow the left-hand side to match when its concat. field is set to either Toyota or Toyota Silver.

HTH,

Jerry