1 of 1 people found this helpful
What you are requesting is called a cross tab report. You can research that term here and find detailed descriptions of several different ways to set that up. It's not as simple a process as we might like, but it can be done.
Some methods use a repeating field. Others use one row filtered portals. ExecuteSQL can pull up data for this and some people load this data into an HTML tagged table in a web viewer. So you have a number of approaches that you can research.
thanks! I'll start studying...........................
To expand on what philmodjunk suggested I would use ExecuteSQL and with this size of array I might still try unstored calculations in repeating fields if you need continuous live data. If you just need reports maybe not.
You might use something like:
ExecuteSQL( "SELECT COUNT(PrimaryKey) FROM \"Table\" WHERE instrument = ? AND block = ?"; ""; "", $instrument; $block)
Having this in a web viewer with HTML or JS makes for a great readable report.
can't I do this the "old-fashioned" way of creating different fields for each block, and then do summary count fields across the page???
Always more than one way to do things. Are you having trouble with ExecuteSQL? You would have something more like this in actual execution.
block = your block field or value;
instrument = your instrument field or value;
result = ValueCount(ExecuteSQL( "SELECT \"PrimaryKey\" FROM \"Table\" WHERE instrument = ? AND block = ?"; ""; "", instrument; block))
It works, but Blocks calculation may be usually like
Block = Middle ( "ABCDEFG" ; Get ( CalculationRepetitionNumber ) ; 1 )
I did a bit odd thing seeing very long Get flag, to balance length of both side of = ...