Lookup with more than one match

[geoffm33]

Newbie here...

I have a main database with a list of products for advertising. There can be more than one of a product because there may be different versions of the way the product copy was advertised.

I have an advertising event database that is a replica of the main and has a relationship with the main based on the sku field. When you enter a product sku into the sku field, all relevant advertising data is pulled into the other fields in the event db.

Since I am allowing for duplicate skus as mentioned above, how can I prompt the user that duplicate skus exist and they need to choose one? The relationship/lookup I am using obviously will pull in the data for the first match.

Is this possible?

Thank you!

[BobWeaver]

If you use a portal instead of just a related field, then the portal will show all of the related records having that sku. Check out the portal section in the manual, or have a look through the topics in this forum in the portals section.

[geoffm33]

Thanks for the info...I'll check it out!

[flora]

I have a similar problem.

I have two db's: db1 displays data retrieved from db2. Db1 has 10 fields per record; db2 has 15 fields per record. Each field in both db's can relate by unique keys comprised of date&(text)&(number); ex., 20030602XYZ2. Db1 must search each of the 15 fields in db2 for each db1 record, and, finding a matching key, return the looked up datum to the defined field in db1. Db1 must do the same for field 1, field 2, and so on, for each of the 10 fields in db1.

I have been able to retrieve data from the lookup field in db2, but only for one lookup at a time. Db1 must do 150 lookups: 15 lookups for each of its 10 fields. Each lookup has a unique relationship based on the unique keys.

However, when I change the lookup for the first field in db1 to search the 2nd field in db2, the previous lookup is lost. If field 1 in db1 in the first record previously matched field 1 in db2 , when I then search field 2, no data from the search of field 1 is retained. I need db1 to remember the lookup parameters and to display the retrieved data in the previously matched fields.

I have a working database in DOS using Symantec's Q&A that does exactly this operation flawlessly. However, DOS is going the way of the dinosaur, so I am trying to use FMP as a way of upgrading.

Thank you for your consideration of this problem.

Flora

[Ugo DI LUCA]

flora seems to have said:

Db1 has 10 fields per record; db2 has 15 fields per record. Each field in both db's can relate by unique keys. Db1 must search each of the 15 fields in db2 and return the looked up unique keys in db1. Db2 must do for each of the 10 fields in db1.

I'm turning your post in any sense, but cannot find any answer. IMO, this will end to circular problem, so as your post is...

May be a "multikey" would help having each 15 fields concanated into a calculation indexed =

field1&" "&field2&" "&field3&" "&field4&" "&field5&" "&field6&" "&field7&" "&field8&" "&field9&" "&field10

Please, go into detail or better explanation of your structure. May be what's in DB1 and BD2 may help undersatnding your situation.

[Fenton]

If there's more than 1 match, then a Lookup is not the tool (unless you sort the relationship so the first match is always the correct one).

There are several methods, mostly due to different layout appearance (and space). All involve showing records matching the relationship and allowing a choice from them, setting that into the current master record.

1. Portal, with button on row (or attached to fields) to set the choice.

2. Value list based on filtered relationship

3. Small window list view of matching records in the child file

[flora]

What I have tried, and seems to work so far, is to use a calculation formula that concatenates all the relationships. I previously named the relationships to reflect the first field in db1 and all the fields in db2 (HKR11, HKR12, HKR13, etc.) The first lookup field is called C1, so the calcualtion looks like this:

HKR11::C1&HKR12::C2&HKR13::C3... etc.

I know this looks like sanskrit, but I thought I owed you a reply for your help.

[Fenton]

Perhaps it's best to "leave sleeping dogs lie." There is some confusion about what you're actually trying to do, from your explanation.

>Each field in both db's can relate by unique keys comprised of date&(text)&(number); ex., 20030602XYZ2.

"Each field" is not related, so much as "each record is related, by a key field" comprised of date&(text)&(number). I'm not clear here what the Key is being used for.

It's a slight difference perhaps. I guess the main confusion I have is "what are you searching for?" The value of the key? The value in the fields, ie. C1, C2, etc., in DB1?

It makes no sense to search for what's in C1 in DB1, in all the fields in DB2, then put the same value in it. Or are you entering a value into a variable, a global field, and searching for that? Or are you searching for records matching C1, and capturing their keys (above) into a multi-line field?

Basically that's how I'd do this. Put a value into a global, then either do a multi-request "Or" Find for it in the 15 fields (is that too many requests? I suppose you could break it into 2 Finds, or concatenate the fields, but that would bloat the file.). Or do a Loop through all the records, with an inner Loop through the 15 fields (this could be done using "Go to Field" if you have the correct Tab Order).

Any setting of fields in DB1 would be scripted, so you could set the field to itself, plus a carriage return, then add the new value; in order to set multiple lines of values.

Loops can be fairly fast if you remember to Freeze the window, and go to a simple Form view layout; don't run them on a list view.

You may also want to question the relational structure. Whenever I see field1, field2, etc.. all being used in a common operation, I think "why isn't it just Field, with a separate record for each, and some other label field to handle the difference?" It's more records, but it makes what you're trying to do a lot easier.