This may be a n00b question posted elsewhere, but I couldn't seem to find the answer here.

When I use the 'sum' function in the XSL files produced by the FM Site Assistant, there is some code that checks if any of the fields is not a number. If so, it reports "N/A". Here's the code:

Here's the problem: If a record has no data in the field that is being summed, or if there is non-numerical data, such as some dashes, the sum function returns "N/A".



I've tried substituting the following for records that don't have numerical data in that field:

but that only affects the output, which the sum function doesn't care about.

Is there a way to program for this, or do I have to scrub the data in the database to be existing and numerical?

TIA.

No, it's not trivial, and I had to look up the XSLT FAQ and to test it on an own example.

You can use these two lines only and forget everything else:

This looks very weird, but works. Here the explanation:

In XSLT, square brackets [] in an XPath define a test expression.

So the first square bracket pair just selects the field with field name (attribute name) stored in the variable $_summary-field-name-1 . Let's assume the field name is 'x'.

The second square bracket pair contains a number conversion of the value in the current XPath context (defined by the dot), i.e. the value just selected by the summing loop. The expression is true or 1 if the number conversion yields a number; if the number conversion yields 'NaN' it is false or 0 because NaN is the only number that isn't equal to itself.

Hence, if true, this evaluates to fmrs:field[@name = 'x'][1], the first element of field 'x' (and there is only one element!). If false, this evaluates to fmrs:field[@name = 'x'][0], which does not exist and hence is not considered by sum().

Edited April 26, 200718 yr by Guest

Martin:

That is a most excellent and well-explained response. I will certainly be using the NaN does not equal NaN trick in the future.

Thanks so much! :)