is it possible to return the total amount formatted with a comma and two decimal places?

 

 

ExecuteSQL ( "

SELECT

l.category, sum ( l.amount) AS total

FROM

lineitem l

WHERE

l.id_detail= ?

GROUP BY

l.category

ORDER BY total DESC

 

 

" ; " " ; "¶" ; detail::id )

 

I have tried

CONVERT(varchar, CAST(sum ( l.amount) AS money), 1)

 

There are a number of custom functions that do this on www.briandunning.com

 

if you use the one 'formatasprice' the query would look like this

 

 

ExecuteSQL ( "

SELECT

l.category, FormatAsPrice(sum ( l.amount); "." ; "$" ; 1 ) AS total

FROM

lineitem l.....

 

Don't forget to install the CF first  

You probably will also need to escape the quotes using a char before each one;

 FormatAsPrice(sum ( l.amount); "." ; "$" ; 1 ) AS total

 

Hope this helps

 

Jerry

Note that installed custom functions are not available within ExecuteSQL queries — you have to apply them to the query results. ExecuteSQL is a window into a query engine completely separate from the calculation engine.

Let ( [
	~sqlResult = ExecuteSQL ( ... ; ¶ ; "" ; Detail::id ) ;
	~total = GetValue ( ~sqlResult ; 2 )
] ;
	FormatAsPrice ( ~total ; "." ; "$" ; 1 )
)

 

Note that installed custom functions are not available within ExecuteSQL queries — you have to apply them to the query results. ExecuteSQL is a window into a query engine completely separate from the calculation engine.

 

Hi Jeremy,

 

I was going to use a cf within sql which I just found on line by Kevin Frank called GFN which is 'get field name'.  It shows wrapping the field name portion with the cf and in testing it seems to work fine.  By you and Kevin Frank's reputation, I figure I am simply misunderstanding your statement.  

 

Without hijacking this thread, Jeremy, could you explain this a bit more? I realise you are busy and if so, I certainly understand.

Thanks guys for the help, jbante  your right that I will have to process the result, It wont be tidy since it is an array, but if I use your concept I can see it is possible - Thanks

David, I use similar custom functions myself for all ExecuteSQL queries as a best practice. We have a long discussion about it (and related factors) going on FileMakerStandards.org. The trick with that is that the custom function is used in the calculation engine to generate the SQL query, but the resulting query passed as a parameter to and processed by the ExecuteSQL function does not contain any reference to the custom function.

"SELECT " & FieldName ( Table::field ) & ...

is not the same as

"SELECT FieldName ( Table::field ) ..."

 

Note that installed custom functions are not available within ExecuteSQL queries — you have to apply them to the query results. ExecuteSQL is a window into a query engine completely separate from the calculation engine.

Let ( [
	~sqlResult = ExecuteSQL ( ... ; ¶ ; "" ; Detail::id ) ;
	~total = GetValue ( ~sqlResult ; 2 )
] ;
	FormatAsPrice ( ~total ; "." ; "$" ; 1 )
)

oops, thanks for the correction. 

Finally revisited this. I modified this CF  http://www.briandunning.com/cf/928

which did the trick see line with the RT variable, which calls another custom function.

Let
(
	[
		text = text;
		SearchText1 = SearchText1 ; 
		SearchText2 = SearchText2 ; 
		//ReplacementText = ReplacementText ; 
		Len = Length ( text ) ;
		Stop1 = Position ( text ; SearchText1 ; 1 ; 1 ) ; 
		Stop2 = Position ( text ; SearchText2 ; 1 ; 1 ) + Length ( SearchText2 )  ; 

		LChunk = Left ( text ; stop1 - 1 ) ; 
		RChunk = Right ( text ; Len - Stop2 + 1 ) ;
		T = Middle ( text ; stop1;  Stop2 - Stop1 ) ;
		RT = FormatNumberAsText ( T ; "$" ; "," ; "." ; 2 ) ; 
		X = Replace ( T ; 1 ; Length ( T )  ; RT ) ; 
		CleanText = LChunk & X & Rchunk ; 
		checkSearchText1 = PatternCount ( RChunk ; SearchText1 ) ;
		checkSearchText2 = PatternCount ( RChunk ; SearchText2 ) ;
		Rmd = GetAsBoolean ( checkSearchText1 ) and GetAsBoolean ( checkSearchText2 ) 

	];

	LChunk & X &

	If ( Rmd ; RangeReplaceWithCalc ( RChunk ; SearchText1 ; SearchText2  ) ; RChunk )


)

my ExecuteSql returned the following array:

Creative «330»

Music «200»

Producer «»

Studio «4884»

Talent «»

 

and the above function converted it to:

Creative $330.00

Music $200.00

Producer $0.00

Studio $4,884.00

Talent $0.00

 

 

 

Ultimately it would be interesting if I could add a parameter to the function called 'function'

text.Replace.withCalc ( Text ; SearchText1 ; SearchText2 ; Function ) )

 

EG. text.Replace.withCalc ( array ; "«" ; "»"; "FormatNumberAsText ( T ; '$' ; ',' ; '.' ; 2 ) "  )

RT = Evaluate ( Substitute ( Function ; "'" ; """ )) ; 

but no luck

As you can't combine a aggregate function and cast in ExecuteSQL, a possibility is to populate a virtual list with ExecuteSQL and do a 2nd ExecuteSQL on the virtual list something like this:

 

ExecuteSQL ( "SELECT Field1, '$ ' + ROUND(Field2,2) FROM VirtualList WHERE Field1 IS NOT NULL" ; "" ; "" )

 

regards Otmar Kramis