Beginner Q: IsNull in sum field

[ste4en]

I am trying to add up the data in a field (qty), which is mostly a text string but also has null values. I cannot change the field type in the database.

So I am trying to change the null values to a text zero, then converting the text to a number and summing.


Creating formula field RidNull:
if isnull(qty) then "0" else (qty)

and then sumqty:

sum( tonumber(qty))

But it is not working and how can I do this in one formula.

Thanks

 

[synapsevampire]

Create this instead:

if isnull({table.qty})
or
trim({table.qty}) = ""
then
0
else
val({table.qty})

Now use the field as a number, rather than converting later in other formulas/aggregates.

You can speed this up by creating a SQL Expression and performing the same thing there so that the database does the work, but this depends on your software version and the database/connectivity used.

If you post in any forum, about any software, please include basic environment information or people have to guess or supply numerous solutions.

-k