ExecuteSQL Summaries

[Tim Anderson]

Hi All,

I have posting this on Technet, but adding here in case someone can help me here who doesn't use TechNet.

I have been playing with fql for a while, using plugins before FM12, but now have come up with something that, from my reading, may not be possible! before I spend (waste?) any more time can anyone tell me if the below is possible.

In the table I am looking at there are 3 fields I am interested in, ImageID, imagesize and quantity.

I want to summarise the data such that I get something like below. I want to use the results in a webviewer so cannot take into another table and process

Image 1

10x8 3

6x4 1

Image 2

10x8 1

6x4 2

Image 3

6x4 2

I think I am going to have to find some way of processing the results of the ExecuteSQL to get this, unless someone with a great deal more knowledge of SQL than me (Ok, that will not be difficult!) can tell me otherwise

Thanks

Tim

[Ocean West]

i think this is doable Tim - here is a great guide that shows the things you can do with the SQL function

http://www.filemakerhacks.com/?p=6406

[Tim Anderson]

Thanks,

have looked through that and, with some input from Technet, have come to the conclusion that I am going to have to post-process the results from SQL with a custom function

[Tim Anderson]

Solution found, posted on technet

https://fmdev.filemaker.com/message/98648#98648%2398648

[beverly]

Tim, not everyone on this forum has access to TechNet. If you would, can you post any updated information here, too?

[john renfrew]

Now it's free to join though..... So should be easier to find.

[Tim Anderson]

Hi Beverly,

Anyone on this forum should also join Technet if they hanen't already - as John points out it costs nothing and is a great resource.

Below is a copy of my last post there

I have exactly the result I was looking for thanks to the genius of Agnès Barouh and her amazing CustomList custom function (

http://www.tictac.fr/CoinFileMaker/PageCustomList.html ).

I now have

which scrolls beautifully as a webviewer

To get this I took the SQL result ( sorted in the SELECT statement) and ran it through a custom function I modifed from her UniqueList cf as below.

Fairly easy thanks to the genius of Agnès!

Hope this helps some others

Tim

//-------------------------------------------------------------------*/

// Based on the UniqueList functon by Agnès Barouh - Juillet 2007 -

//-------------------------------------------------------------------*/

// ParseHTMLForSummaryHeaders( TheList )

Let(

$_TheList = TheList ;

CustomList( 1 ; ValueCount( $_TheList ) ;

"Let( [

TheValue = GetValue( $_TheList ; Num );

headerEnd=position(TheValue;"</B>";1;1);

lVal=length(TheValue);

thePrefix=left(TheValue;headerEnd);

theRest=right(TheValue;(lVal-headerEnd)-3)

] ;

Case(

PatternCount( $_Value ; thePrefix ) < 1 ;

Let ( $_Value = $_Value & " " & TheValue ; TheValue );

Let ( $_Value = $_Value & " " & TheValue ; theRest )

))"

)

)

& Let( [ $_Value = "" ; $_TheList = "" ] ; "" )