SQL instead of sub-summary reports?

[Rich S]

Howdy, all:

 

I'm trying to aggregate totals--which would normally be a simple affair using a scripted sub-summary report--but of course nothing is ever simple around here.  

 

Let's say I have field named Color and the found record values are:

 

Record 1: Blue

Record 2: Blue

Record 3: Red

Record 4: Green

Record 5: Green

 

With the necessary count/summary calcs and running a scripted sub-summary report, the desired result in Preview mode would end up being:

 

Blue: 2

Red : 1

Green: 2

 

Fine, except that for their reasons, users want to stay in Browse mode so there goes the scripted sub-summary report. Isn't there a way using SQL that will perform the calc in the background so aggregates "live" in Browse mode? I took a look at some SQL code but it's hieroglyphics to be at this point since I've had no training in it. 

 

TIA for your guidance/help!

[eos]

Blue: 2

Red : 1

Green: 2

 

You can get that via

ExecuteSQL ( "
  SELECT colour, COUNT ( * ) 
  FROM anyTONameOfThatTable 
  GROUP BY colour
  " ; ": " ; "" 
)

Try it out, and take it from there. 

 

Note that this statement has no WHERE section (the Find part) – this is where you would recreate the criteria of a Find, or the predicates of a relationship. These are ignored by eSQL – a TO is just an entry point into the table, but doesn't provide any context within the SQL statement (except when you pass values into it by using the optional parameters of the function – these are FileMaker expressions resolved in the calculation's context.)

 

If you plan on using ExecuteSQL(), be sure to read all the many things that have been said about what to watch out for, and what speaks for and against it, and which I don't want to reiterate.

 

I took a look at some SQL code but it's hieroglyphics to be at this point since I've had no training in it. 

 

Beverly Voth's Missing Manual (no affiliation) PDF, and Kevin Frank's filemakerhacks web site are good entry points into eSQL().

[comment]

users want to stay in Browse mode so there goes the scripted sub-summary report.

 

Why is that? You can view a sub-summary report in Browse mode since version ... 7, I think? Or was it 10?

 

With the necessary count/summary calcs

I see only one summary field, that's all.

[Rich S]

Thanks, guys! 

 

>Why is that? You can view a sub-summary report in Browse mode since version ... 7, I think? Or was it 10?<

 

Of course you're right, but due to other fields and elements in the layout it needs to stay in Form view; List or Table view isn't an option.