Can anyone help ? Cant calculate with empty related field..

Classic Servoy
1
  • repost -

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 do not use the looked up values and use “static” fields everthing works fine..

Rainer

DSP Memory Distribution GmbH
Lise-Meitner-Str.1
24941 Flensburg / Germany
www.dsp-memory.de

2

please check it this way: (what is exactly that TABLE thing you have in that example of yours? it shouldn’t it just be be relation1.price?

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

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

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

3

i just want to make a simple calculation based on a related record..

// code

if ( relation.myfield == null )
{ return 0;}
else { return relation.myfield }

If realtion.myfield is looked up by a primary/foreign key it returns the condition null, 0 or >0 and everything is ok.

The problem is, that if there are no related records found e.g. no matching keys , the calculation just fails with following error:

Exception executing relation.myfield: Cannot convert null to an object.

If there is no related record found it seems that the related.myfield does not exist for calculations ( and methods). Since the check for NULL does not work with empty related fields, i would need something like this:

if ( relation.myfield = [true])

{ return relation.myfield ;}

else { return 0; }

If needed i can create a sample form.

Thank you for you support..

Rainer

4

try validating only the relation:

if ( relation)
{ return relation.myfield ;}
else { return 0}