Stu412

Select full decimals using ExecuteSQL

Discussion created by Stu412 on Jul 5, 2016
Latest reply on Jul 6, 2016 by Stu412

Hi all

 

I'm trying to select from the following data set on a field called FeeValue.  I then pass this into a custom function to calculate the upper quartile - FileMaker Custom Function:ThirdQuartile ( valueList ) :  The data in my raw fields looks like this:

 

0.52

0.56

0.94

0.44

 

This uses the following ExecuteSQL:

 

ExecuteSQL ("

 

Select (FeeValue)from Fees where

 

Area = ? and

Category = ?  and

Description = ? and

Service = ? and

cYearEndedIn = ?

 

Order by FeeValue ASC

";"";"";

 

"North";

"Generic Category";

"Generic Description";

"Named Service";

2015

 

)

 

I noticed something was wrong when the result I was given was 75.00.  The result here should be 0.75.  (94+56/2)

 

I checked further and put the ExecuteSQL into a small script against a global variable to check what was actually being collected and the select gives me the following via the data checker:

 

.52

.56

.94

.44

 

This is despite the source data having a leading zero.

 

Two questions arise:

 

1) Does ExecuteSQL strip the leading zero, and if so, would having it there make any difference?

2) Has anybody else used the CF I'm working with and found there are similar results with numbers under 1.00?

 

Thanks

Outcomes