ExecuteSQL and charting

[V8S]

tbl.Products has 3 fields: Ref, Agent and Tagged.

A number of Products are tagged for an export.

From another layout, I want to chart, using ExecuteSQL, the Count of Ref for each Agent who has Products that are Tagged.

So at present I have:

X AXIS: SELECT Agent from tbl.Products WHERE Tagged is not null ORDER BY Agent ASC

Y AXIS: SELECT Count(Ref) FROM tbl.Products WHERE Tagged is not null GROUP BY Agent ORDER BY Agent ASC

So my chart shows an ordered.

But what I want to do is ORDER BY the count, which means in the SQL I need to output two fields for each SELECT:

SELECT Agent,Count(Ref) AS CountRef FROM tbl.Products WHERE Tagged is not null GROUP BY Agent ORDER BY CountRef DESC

I there a way to get Filemaker to extract the Agent for the X axis, and CountRef for the Y axis?

 

 

[comment]

If you're doing this within a script (as I believe you should), you can tell the same script to loop over the ExecuteSQL result and separate it into 2 variables. 

Please update your profile to reflect your version and OS, so that we know what you can use.

 

[V8S]

Yes, that's what I've ended up doing actually. I just wondered if I was missing something.

[comment]

1 hour ago, V8S said:

wondered if I was missing something.

In version 18, you can also use the While() function to do the same thing.

And, of course, in any version you can use "native" methods (find, sort, summarize) to gather the chart data instead of SQL.