Auto Enter a Calculated Value

[JimVB]

I'm stumped. When I test each piece of the puzzle I get the correct result however when set up as a looked up value based on a calculation I only get the default answer.

I need to look up the shipping account number based on the preferred shipping method. UPS or FedEx

I'm a auto entered calculation from a related table named: Companies

Shipping preference is named: Ship_via_pref

I'm using the Text function (left,3) to parse the UPS Ground, FedEx P1 etc...

What am I missing? Have I explained myself properly?

Thanks in advance,

Jim

Account_number=

Case ( Left ( Companies::Ship_via_pref ; 3 = "UPS" ); Companies Customer::Ship_UPS_number;

Left ( Companies::Ship_via_pref ; 3 = "Fed" ); Companies Customer::Ship_FedEx_Number;

"use default account")

Edited November 29, 2008 by Guest
Corrected the terminology. Import changed to Auto Entered Calculation

[Kevin Frank]

A few thoughts come to mind...

1. Since you say that each piece of the puzzle works, it sounds like your relationships are correct. Normally, that's the first thing I'd wonder about.

2. Why are you using LeftValues instead of Left in the second test?

3. If you're creating a record via import, don't use a look up to populate your shipping account number. Instead attach an auto-enter calculation to the shipping account number field, and make sure that the "allow auto-enter" box is checked when you perform the import.

HTH,

Kevin

[JimVB]

A few thoughts come to mind...

1. Since you say that each piece of the puzzle works, it sounds like your relationships are correct. Normally, that's the first thing I'd wonder about.

2. Why are you using LeftValues instead of Left in the second test?

3. If you're creating a record via import, don't use a look up to populate your shipping account number. Instead attach an auto-enter calculation to the shipping account number field, and make sure that the "allow auto-enter" box is checked when you perform the import.

HTH,

Kevin

Thanks Kevin,

1. Yes, the relationship is correct. It stopped working when I added the case/test/results options.

2.LeftValue was my mistake. I changed to Left. I made the correction but it did not solve the problem. I have fixed it in my original post.

3. I actually have it set up as a auto-enter calculation. My description was incorrect.

[Kevin Frank]

Let's talk about exactly what it is you're doing.

Are you creating an "order"?

What are the tables involved?

How are they related?

And please post the script steps you're using.

[JimVB]

3 tables involved.

1. Jobs

2. Shipping address (Jobs can have 1 to 100's of shipping addresses)

3. Companies

They are related through a field named Company_key.

A new record is created in jobs.

The Company name is added which then looks up the Company_key.

Shipping addresses are in a portal. When the user designates the the type of shipment (in my case the choice is Production or Sample) it creates a new record triggering the auto entry through a calculation.

1st it looks up the "Ship_via" (FedEx First AM, FedEx P1, UPS Next Day, UPS ground etc...)

2nd it looks up the "Bill_to" (Sender, Receiver, or Third Party)

The last step is where the problem lies. The case function does not return account number for FedEx or UPS.

[T-Square]

The Company name is added which then looks up the Company_key.

That line sends up red flags for me. You should not be using the company name to retrieve the company key; it should be the other way around. I am not sure whether this would cause your problem, but it raises questions for which you'll need answers regarding the overall structure. Can you provide more specifics about the structure, paying close attention to the fields involved, their types, and the relationships among them?

David

[JimVB]

That line sends up red flags for me. You should not be using the company name to retrieve the company key; it should be the other way around. I am not sure whether this would cause your problem, but it raises questions for which you'll need answers regarding the overall structure. Can you provide more specifics about the structure, paying close attention to the fields involved, their types, and the relationships among them?

David

I believe the relationships are correct due to the fact that the default answer is "auto entered". Is the logic in my case statement correct?

[Kevin Frank]

What am I missing? Have I explained myself properly?

Account_number=

Case ( Left ( Companies::Ship_via_pref ; 3 = "UPS" ); Companies Customer::Ship_UPS_number;

Left ( Companies::Ship_via_pref ; 3 = "Fed" ); Companies Customer::Ship_FedEx_Number;

"use default account")

Just got off the phone with Jim. Turned out the problem was misplaced parentheses (and a redundant TO). Here's the corrected syntax.

Case (

Left ( Companies::Ship_via_pref ; 3 ) = "UPS" ; Companies::Ship_UPS_number;

Left ( Companies::Ship_via_pref ; 3 ) = "Fed" ; Companies::Ship_FedEx_Number;

"use default account"

)

P.S. I share T-Square's concerns as well, but that's a separate issue.

[JimVB]

Thank you Kevin. Everything is working perfectly.

I have also addressed the look up method used for the Company and Company_key.

I'm implementing the Anchor/Buoy RG as well.

Thanks again,

Jim