Lookup Values

[kross]

I have 3 DB files. A) Order, Contract Rate Table, and C) Color Rate Table.

I've built a relationship between A to B using a calculated field built from 3 unique fields in each database to pull a Contract Rate from DB B into a Contract Rate field (Looked-up Value field) in DB A.

I did the same between A to C to pull a Color Rate from DB C into a Color Rate field (Looked-up Value field) in DB A.

I'm using a looked-up value solution so that the user can override the lookup rate and input a quoted rate.

The solution works perfectly in the A to B relationship. However, the A to C relationship will not pull in without some hassle. I've checked the relationship calculation field, which looks fine and is complete, but the color rate will not pull in. I think my relationship is working because I can pull in the color rate if I define the field from the relational database.

I found if I duplicated the record (w/ the blank color rate field), the color rate then is pulled in and works good.

Can anyone help me?

[LiveOak]

We'll need to see the field definitions for the calculation fields used as keys (don't forget their result types) and the fields used in the calculation. It would also be useful to know what "without some hassle" means.

Ultimately, it may be necessary to look at the files.

-bd

[kross]

Ok, specifically what I have is a database system for our newspaper sales reps to input their ad information and print out a completed insertion order with all rates calculated and totaled.

This consists of many different related databases but 4 that is in concern with this particular problem.

1) Order Input file (to input the general information for the ad including client #, size, color(s), proofing information, etc.).

2) Pub/Pub Date file (to schedule specific publications (we have around two dozen publications) and the run date(s).

3) Contract Rate Table file (the pub/pub date file looks at the clients contract status and retrieves the inch count from the order input file and then calculates and retrieves the contract rate from the table).

4) Color Rate Table file (the pub/pub date file retrieves the color code (number of colors defined in the order input file) and matches it to a calculation in the color rate table to supply the color rate.

The contract rate lookup works perfectly. The pub/pub date file has a field calculation defined as [Publication (defined from a popup list in pub/pub date, each publication has a different rate) & " " & Day (day of week again defined from a popup list in pub/pub date, we've got Wed. and Sat. editions which have different rates) & " " & PubDate To Adv.DB::Contract Description (this is a relational database lookup, there are 8 different contract descriptions, this lookup defines which description the client is currently signed up for)]. This calculation field is then matched with a field in the contract rate file which supplies the rate through the lookup value in pub/pub date.

The color rate lookup is the problem. The pub/pub date file has a field calculation defined as [Color Code (defined by the ink colors ordered in the order input file, i.e. 1 color = 1, 2 color = 2, 3 color = 3, process = 4, etc.) & " " & Publication Code (some publications charge more for their color pages, the pub. code field is defined as a case calculation and returns a number 1-3, i.e. Case, Publication = Today's News, 1) & " " & Portal Record # (you can add up to 10 publications per order, the more publications you run in, the cheaper the color ink cost, the portal record # counts the number of publications defined by the portal record count from a relationship to the order input file that brings the pub/pub date information in the general ad information from the order input file). This calculation field is then matched with a field in the color rate file which is designed to supply the rate through the lookup value in pub/pub date.

The color rate will not calculate for the first publication. If a second is added, it then does calculate for it and all future additional pubs to the order, but never calculates for the first pub.

LiveOak, I hope this information ads some light to my problem.

[kross]

quote:

---

Originally posted by LiveOak:

If you use separators, these would be 11-1 and 1-11, clearly not the same. (If you use a "-", make sure the indexing is set to ASCII or it will be ignored). As an alternative you can pad the components of the key to the same length using leading zeros, 011001 and 001011.

---

I had originally had a hyphen between the resulting numbers. This did not help and I removed them as a test (hoping that for some reason that would be the problem), but it did not help.

Plus, the relationship is working as is evident with records 2-__. I can also get the rate to appear relating it directly to the relational database. However, if I do this, I can't overwrite the rate.

quote:

---

Could the problem with your first record be a lookup that needs to be retriggered? Also make sure that your keys can be indexed. A relationship key cannot be based upon a portal count that is based upon a related field!

---

I think the answer is yes, it must be retriggered. If I take that first record and duplicate it, the color rate appears. I failed to addres the "without a hassle" comment from my original post, the duplicate record workaround was what I was referring to.

Duplicating records works, but will cause problems during the workflow with the average user. Do you know of any other way to retrigger the lookup field? Somebody had once mentioned a script step???

Thanks for your suggestions.

[kross]

I found a fix...and a new related problem.

First the fix. I found a script set called relookup. I defined a script as - Show All Records ---> Copy [Color Code] ---> Paste [Color Code] ---> Relookup [Color Calc]. I assigned the script to the end of a pre-defined script button that ads a run date from a pop-up list into to the dates field. So each run date the user ads, the script will relookup the color calculation.

I earlier stated the the color calculation contained a portal row #. The reason for this is that there's a discount if the ad runs in more than one publication. (i.e. if the ad runs in one publication they pay $150/color, if it runs in two publications they pay $150/color for the first publication and $135/color for the second).

The new problem is that if two publications are scheduled, the color rate for each is $135/color. Does anyone know of any way for it to hold that $150/color color rate on the first publication, and then figure $135/color for each additional? Could something with indexing or storing fix the problem?

[LiveOak]

Just a little too complex to diagnose without seeing the patient. One thing I would watch out for in your key fields is concatenating numbers without separators or padding the components of the key to the same length. Make sure you can tell the difference between:

11 & 1 = 111

and 1 & 11 = 111

If you use separators, these would be 11-1 and 1-11, clearly not the same. (If you use a "-", make sure the indexing is set to ASCII or it will be ignored). As an alternative you can pad the components of the key to the same length using leading zeros, 011001 and 001011.

Could the problem with your first record be a lookup that needs to be retriggered? Also make sure that your keys can be indexed. A relationship key cannot be based upon a portal count that is based upon a related field!

-bd