THis is easyest done by ExecuteSQL:
EXecuteSQL ( "
SELECT \"Drop Down Field A\", COUNT(*)
WHERE \"Drop Down Field A\" IS NOT NULL
GROUP BY \"Drop Down Field A\"
" ; Char(13) ; "" )
do the same for B, C, D......, fill global fields or $$vars with the result, place them on your layout...
I don't think dgreensp_1 really needs a VL, It might be sufficient to just place the results as text on the layout. But of course, he could use VL-technic to parse the results into a reporting table aka Virtual List.
if you name your dropdown fields like Dropdown1, Dropdown2, Dropdown3..... you can put the calculation in a repeating calculation field with 8 repetitions, result as text, formula:
Let ( [
_field = "Dropdown" & Get ( CalculationRepetitionNumber )
; _table = "YourTable" //replace with your table name
; _query = "SELECT <field>, COUNT(*)
WHERE <field> IS NOT NULL
GROUP BY <field>"
ExecuteSQL ( Substitute ( _query ; ["<field>" ; Quote ( _field )] ; ["<table>" ; Quote ( _table )] ) ; ": " ; "" )
This is the basic method.
In your table, add a calculation field, lets call it "boolean_true". the calculation is simply the value of one (1).
Create summary fields for each of the eight data fields. The summary may be either Count of boolean_true or Total of boolean_true, because the value of the field is always one, these both produce the same result.
Create a sub-summary report layout that uses the data fields as sub-summary break fields. Now sort by any of the fields to see a summary of each.
If you only want to report on one of these fields at a time your problem is solved.
Thank you Okramis and Malcolm for all of your help.
I'm not too familiar with SQL but I'm going to give this a try based on your ideas
Malcolm - I need a summary report showing all 8 drop down summaries so your idea is perfect if I needed for just one. I appreciate your help.
Will see how this goes.
Your report sounds like a cross tab report, look at the lesson 38 in the
v14 FileMaker Training Series for a full discussion of methods to handle
Where do I find lesson 38?
The basic modules show lessons 1-19
The advance modules show 1-9
Filemaker Training Series v14 FileMaker Advanced, p540