Case script and calculation

[enquirerfm]

I want to produce a field which is based upon another dependent field and whether another field which defines a format is equal to various different capacities.

Example:

Record 1: Product A: Bottle price field contains $100

The format field contains "Bottle"

Thus the price per bottle (the new field I want to find - bottle_price_converted) is $100 (see calculation below).

Record 2: Product B: Bottle price field contains $50

The format field contains "Magnum"

Thus the price per bottle (the new field I want to find- bottle_price_converted) is $50 (see calculation below).

Case(Field::Bottle size = "Bottle";Field::bottle_price_converted = Field::Bottle price*1;Field::Bottle size ="Magnum"; Field::bottle_price_converted = Field::Bottle price/2) etc

However, When I run this script I only obtain a value of $0 in the Field::bottle_price_converted

What am I doing wrong? Thx.

[comment]

This is quite confusing.

Record 2: Product B: Bottle price field contains $50

The format field contains "Magnum"

Thus the price per bottle (the new field I want to find- bottle_price_converted) is $50 (see calculation below).

Isn't Magnum equal to 2 standard bottles? It's not clear what the given $50 price refers to; if it's the price of a Magnum bottle, then the result (price per standard bottle) should be $25. If it's the price of a standard bottle, then a Magnum should cost $100, shouldn't it?

[Cabinetman]

Case(Field::Bottle size = "Bottle";Field::bottle_price_converted = Field::Bottle price*1;Field::Bottle size ="Magnum"; Field::bottle_price_converted = [color:red]Field::Bottle price/2) etc

Maybe you already saw this already but it should be multiply and not divide ... ( divide 0 by 2

[enquirerfm]

Sorry for the confusion. No, the price of the magnum (different wine - Product is $50. Thus the price per 75cl is 50/2 = $25. Thus the formula is right I think. However, it doesn't really matter in that as I said the formula only seems to produce a value of $0, not $25. What am I doing wrong?

[Raybaudi]

Try this one:

Case(

Bottle size = "Bottle" ; Bottle price ;

Bottle size = "Magnum" ; Bottle price / 2

)

[comment]

it doesn't really matter in that as I said the formula only seems to produce a value of $0, not $25.

It matters to me, since I need to understand what you are trying to achieve.

What am I doing wrong?

This:

Case(Field::Bottle size = "Bottle";[color:red]Field::bottle_price_converted = Field::Bottle price*1;...

You should specify (only) the result that the calculation field should return. What you are actually doing is a comparison: does [color:blue]Field::bottle_price_converted equal [color:blue]Field::Bottle price*1?

Also, I don't really get the expression [color:blue]Field::Bottle size your table is named "Field"?? If the referenced fields come from the same table, there is no need to specify the table's name.

In any case, I would suggest an entirely different approach here:

WineBottles.zip

[LaRetta]

I guess I'm missing something here. How would this not be a standard product table where each size is an individual product with price attached and stock count would follow accordingly with price pulled into Lineitems according to selected item? I'm just curious, trying to grasp this thread.

Edited October 30, 2010 by Guest

[enquirerfm]

Many thanks for the zipped file - a few more formats than I really needed but it works like a charm. I have implemented this by effectively adding a table (like yours) to the main database - I have done this manually but imported the file of records to populate the records. is there an easier way to do this?

As for the question above, this is about trying to standardise values where a variety of formats exists in order to make some comparisons esp. to highlight anomolies. So, for example, are prices higher for magnum than one might think e.g. are they more than a simple x2 of the standard 75cl size?

[comment]

I have done this manually but imported the file of records to populate the records. is there an easier way to do this?

The way I did this was to create a tab-separated file (with field names) first. This was easy - I simply copied a table I found on the net. Then I imported this file into a NEW table.

[comment]

I didn't mean to ignore your question, but it's not up to me to answer.