Multiple relationship from single field?

[acantho]

Ok this may have a really simple solution cause so far the only thing I've come up with involves a ton of coding.

What I have is one database with a particular 6 fields. 3 of those fields are identifiers. and the other 3 are data.

For example, let's assume it's a Quoting database.

Product equals X amount of dollars.

In my database I have field HH#, VS#, XR# which are all unique codes to this product but identifying different clients. They each then have a different cost related to this product marked as fields HH$, VS$, XR$.

Now in a simple lookup I would just look up the HH# and get HH$. What I need to do is be able to lookup each of those different # and get the corresponding $ from one field doing the lookup? (or perhaps a calculation if need be)

Any thoughts?

[-Queue-]

How do you determine which field to use after looking all of them up?

[acantho]

Well that's the thing. Each entry into the corresponding fields HH#, VR# and XR# would have some sort of character associated with them. For instance HH, VR, XR.

What I need to be able to do is that if I type "HH 8475" it pulls from HH$ for the corresponding item "8475" and if I type in "VR 8475" it pulls from VR$ but using only one field for entry.

It's basically trying to make a lookup that checks 3 different fields (or 3 different lookups) and returns the corresponding information.

We used to run this off another database but we're trying to remove that database cause we're expanding dramatically and don't have the time to technically double enter.

[-Queue-]

You could either use an auto-enter calculation that checks the first two letters and returns the appropriate result, though the field would have to be cleared if you changed your mind and wanted to enter a different code, or you could use three lookup fields and a calculation field that checks the first two letters and returns the appropriate looked-up field.

The first case would be a calc like

Case( Left( code, 2 ) = "HH", Relationship::HH$, Left( code, 2 ) = "VR", Relationship::VR$, Left( code, 2 ) = "XR", Relationship::XR$ )

The second,

Case( Left( code, 2 ) = "HH", LookedupHH$, Left( code, 2 ) = "VR", LookedupVR$, Left( code, 2 ) = "XR", LookedupXR$ )

[acantho]

I think we tried that. I'll look into it more tomorrow but the problem with that is though it identifies the relationship... it doesn't identify which field to pull. (as far as I can tell) cause the relationship is based off the idea that HH# is in both databases and HH$ is what we're trying to pull when we enter HH# in the second database.

Maybe I'm reading what you wrote wrong but it looks a lot like something we already looked into.

But thanks... it's a start

[-Queue-]

Well, the relationship would be the same for all three results, since it's based on the numeric portion of the code field. The field to retrieve is different for each result in the first calc: HH$, VR$, or XR$. The looked-up fields would merely use the same related fields for their results.

[acantho]

Now how would you put that into a repeating field with say 3 repeats?:

LOL

It works fine for single fields but I'm working with repeaters here

[-Queue-]

You mean you have three different codes for each record or you're storing the looked-up values in three reps?

[acantho]

LOL

no

The fields being used are repetition fields. There's actually 10 (we don't normally need more then 10) repetitions in each field.

So though HH# and HH$ are single fields in one database, the other database that's looking up to those fields is using repetition fields to do so.

for example imagine this is the database with the repeating fields (that used to be regular lookups and obviously worked great), now how would your calculation above need to be changed in order to be used in a repeating field.

• Item # <------------------> Item $
• HH 565 <-----------------> $560
• HH 230 <-----------------> $540
• VR 230 <-----------------> $500
• XR 230 <----------------- > $490
  

Where (Item #) is one field in the main databse, (Item$) is another field in the main database, (HH 565) is an entry on the first repetition of Item #, ($560) is the value returned when HH 565 is entered, (VR 230) is an entry on the 3rd repetition of Item #, ($500) is the value returned when VR 230 is entered. etc.

We had almost the same calculation you have above. Right now we're having the problem of creating a loop cause GetRepetition() can't get from itself apparently :P

[acantho]

Well I think my question in another section of the forums has been solved but this one is still stumping me.

I thought we were making progress but it looks as though we've stalled.

Nobody have any other ideas?