saf01
February 27th, 2006, 09:22 AM
Hello,
I am using CR11.
I have six fields in my sql database from which I will only show three that are required. These are {SellPrice}, {SortCode} and {ProductCode}.
The {ProductCode} contains "A" codes like A1010001 to A1010020
and "C" codes like C100021001 to C100021035 (different string lengths).
The {SellPrice} has different pricing for "A" codes and also for "C" codes.
The {SortCode} contains only "A" codes for certain prices in the {SellPrice} field.
My question is: how do I write a formula that will first find an "A" code in the {SortCode} field. If "A" code is not found then give {SellPrice} for {ProductCode} for "C" code only. If "A" code is found in {SortCode} then match it with the "A" code in the {ProductCode} and give a different price in the {SellPrice} field. The table is as follows:
{SellPrice} {SortCode} {ProductCode}
14.99 A1010001 C100021003
19.99 C100021001
12.50 C100021002
19.99 A1010001
e.g: As shown above, {SortCode} and {ProductCode} contain "A" codes so price is 14.99. All prices are in {SellPrice} column and all codes are in the {ProductCode} table. I am so frustrated as to how I should go about creating this report. I have tried to make formulas that matches the {ProductCode} with {SortCode} if a match of "A" codes is found, but I get no results and the wrong price.
Please, please help. Thanks.
I am using CR11.
I have six fields in my sql database from which I will only show three that are required. These are {SellPrice}, {SortCode} and {ProductCode}.
The {ProductCode} contains "A" codes like A1010001 to A1010020
and "C" codes like C100021001 to C100021035 (different string lengths).
The {SellPrice} has different pricing for "A" codes and also for "C" codes.
The {SortCode} contains only "A" codes for certain prices in the {SellPrice} field.
My question is: how do I write a formula that will first find an "A" code in the {SortCode} field. If "A" code is not found then give {SellPrice} for {ProductCode} for "C" code only. If "A" code is found in {SortCode} then match it with the "A" code in the {ProductCode} and give a different price in the {SellPrice} field. The table is as follows:
{SellPrice} {SortCode} {ProductCode}
14.99 A1010001 C100021003
19.99 C100021001
12.50 C100021002
19.99 A1010001
e.g: As shown above, {SortCode} and {ProductCode} contain "A" codes so price is 14.99. All prices are in {SellPrice} column and all codes are in the {ProductCode} table. I am so frustrated as to how I should go about creating this report. I have tried to make formulas that matches the {ProductCode} with {SortCode} if a match of "A" codes is found, but I get no results and the wrong price.
Please, please help. Thanks.