Hi all!

I need a little help coding a symbolic field in an order_details edit table in a Customer_Orders form.

My Products table collects multivalued customer_ids to store the different UPC numbers used by different customers for the same product. For example, Product record V100 is likely to have the following live data in an edit table:

Cust_ID UPC Cost Retail

ABC 4001222222222 $1.41 $6.50

DEF 4923333333333 $1.51 $6.50

GHI 4123111111111 $1.41 $6.50

In Customer_Orders, UPCs need to be Xlated into the record for various reasons. The Customer_Order has an Order_Details edit table that collects the Product_ID and Quantity as live data, the rest of the edit table Xlates the UPC, Cost & Retail fields from the Product record and does the necessary math on Extended_Cost (@ANS={COST}*{QTY}). However, the Order_Details UPC symbolic correctly reads the first row but nothing else, ie: (Cust_ID=DEF) Product_ID UPC Cost V100 4923333333333 $1.51 V101 (does not read correctly) V102 "" V103 "" The UPC symbolic in Customer_Orders is coded thusly: POS=' * Read the list of cust_ids from the current product record ANS=XLATE("PRODUCTS", {PRODUCT_ID}, "CUST_ID", "X" ) * Find the customer_id in the returned list of IDs and set POS, then * read the stored UPCs and return the UPC value found at the same * position (row) LOCATE {CUST_ID} IN ANS USING @VM SETTING POS THEN ANS=XLATE( "PRODUCTS", {PRODUCT_ID}, "UPC", "X" ) END @ANS=ANS ANS=' POS='

I tried Get_Property( objectname, "SELPOS"), but it didn't work correctly (more likely I didn't pass the SELPOS row value to the XLATE correctly). If push comes to shove, I suppose I could use READV, but since I'm really trying to xlate 3 fields (UPC, Cost & Retail), I'd rather avoid the extra overhead processing.

I must say that everything in Customer_Orders has worked beautifully until I changed Cust_ID, UPC, Cost & Retail to be multivalued fields. Previously, each product item was associated with one customer but that has changed.

Thanks in advance for your help.

Mike

Mike, I think the whole problem is in the statement…

   @ANS={COST}***{QTY}

To be able to use the "***", you must have both multi-values have the same exact number of multi values. This is what I mean.

example 1; if COST=1:@vm:2:@vm:3" and QTY=2" then the result is "2:@vm:0:@vm:0". Because it is litterally doing this…

 1 * 2=2, 2 * 0=0, 3 * 0=0

See how it multiplies value 1 with value 1, and value 2 with value 2, and value 3 with value 3.

example 2; if COST=1:@vm:2:@vm:3" and QTY=2:@vm:2:@vm:2" then the result is "2:@vm:4:@vm:6". Because it is litterally doing this…

 1 * 2=2, 2 * 2=4, 3 * 2=6

Hope that helps you.

Hey Richard,

Thanks for the response. My problem, though, is not with the multivalued arithmatic but in correctly Xlating a single multivalued UPC data associated with a Customer_ID from the Products record to the Customer_Order record. Remember, in my products record, I have multivalued Cust_ID, the UPC each customer has assigned to an individual product item, the customer cost and the retail price at which the customer sells the item. Multiple customers can order the same item and each customer has a unique UPC for that item. The symbolic code for Customer_Order.UPC that I included does retrieve the data correctly for the first item in the Order_Details table, but not for any subsequent item. Most customers order more than one item.

Mike

Oops I was confused. Hmmmm consider this…

@ANS='

RECORD=XLATE('PRODUCTS',{PRODUCT_ID},'','X')

CUST_IDS=RECORD

UPCS=RECORD

LOCATE {CUST_ID} IN CUST_IDS USING @VM SETTING VALUE THEN

 @ANS=UPCS

END

Richard,

Thanks again. I had originally considered XLATEing the whole product record (as you suggested by the null value for the 3rd parameter) but was concerned that when a customer orders a whole mess of items, all those reads to the products file plus the overhead might crash the system or breach the 64K limit. I do, though, like the single read as opposed to multiple XLATEs. I'll give it a try.

Mike

View this thread on the forum...