Calculation with related records and NULL values

Classic Servoy
1

Win XP / lastest Jave / 2.0RC7 / MySQL

Warning ! Complex problem :)

I have the following database “TABLE” :

Part Name price

10 huhuhuh 100,00
20 hihihihih 300,00
30 hahahah 200,00
..
N

On another form i have three fields:

Part_a
Part_b
Part_c

I made three relations Part_a / b / c to TABLE.part

I use this to display ( record view) the price of 3 products for built-to-order usage on a single page.

The form returns me, if i enter the part, the name and price of the part ( looked up from TABLE )

10 [entered] huhuhuh [looked up] 100,00 [looked up] //relation 1
20 [entered] hihihihih [looked up] 300,00 [looked up] //relation 2
30 [entered] hahahah [looked up] 200,00 [looked up] //relation 3

So far, anything is ok.

I now want to sum the looked up price:

price_total =
relation1.TABLE.price +
relation2.TABLE.price +
relation2.TABLE.price

To eliminate NULL is use:

…

if ( relation1.TABLE.price == null )
{var a = 0}
else { var a = relation1.TABLE.price }

if ( relation2.TABLE.price == null )
{var b = 0}
else { var b = relation2.TABLE.price }

if ( relation3.TABLE.price == null )
{var c = 0}
else { var c = relation3.TABLE.price }
…
total_price = a + b +c

If the looked up fields “price” are > 0 everthing calculates right.
If the looked up fields “price” are “0” everthing calculates right.
If the looked up fields “price” are “null” everthing calculates right.

If i now delete the Part_a field ( which is used in the relation to look up the name and price) the field “name” and “price” are cleared (ok..).

I thought that the field relation1.TABLE.price should be NULL now, since it is not looked up anymore. But my calculations fails “can not convert null object”.

It seems like the related field is not available for any calculation if it is not looked up. If it is shown on the form (empty field) why cant i calculate with it ? It just should be NULL.

PS: If i u do not use the looked up values and use “static” fields everthing works fine..

Rainer

2

what is that TABLE thing you are printing above?
because to get a dataprovider of a relation you only have to do:
relation_name.provider_name
like
relation1.price…

that in mind:

if ( relation1)
{var a = relation1.price}
else { var a = 0 }

if ( relation2)
{var b = relation2.price}
else { var b = 0 }

if ( relation3 )
{var c = relation3.price}
else { var c = 0}
…
total_price = a + b +c