counting records in a leading sub summary part

[John Marzano 0]

Hi all,

Novice here.  I have a simple two table database for inventory tracking.  Parent table "Cases" and child table "Items".  The cases can be filtered by finding all cases with the field  "Status" set to 'Yes' or 'No'

I am able to create a report Layout with the found set with the field 'Case Name' in a Leading Sub Summary part and the content field of each case (Items) listed in the body part.  What I am struggling with is a way of having a field in the Leading Sub Summary part along with the 'Case Name' that sequentially numbers the Cases in the found set from 1 to xxx

Can anyone help me please?

[comment 1,777]

I am a bit confused by your description. I presume your report layout is based on the child Items table? Therefore, if you want the report to include only items in cases whose status is "yes", you must perform the find in the Items table too. IOW, instead of:

57 minutes ago, John Marzano said:

finding all cases with the field  "Status" set to 'Yes' or 'No'

you need to find all items with the related field "Status" set to 'Yes' or 'No'.

Now assuming that is so, to your question:

Unfortunately, numbering sorted groups is not easy. The simplest method I know of requires two fields (both defined in the child Items table):

• sListOfCases - a Summary field, defined as List of [ CaseID ];

• a calculation field (result is Number) =

Let ( [
cases = UniqueValues ( Items::sListOfCases ) ;
index = Position ( ¶ & cases & ¶ ; ¶ & CaseID & ¶ ; 1 ; 1 )
] ;
ValueCount ( Left ( cases ; index ) )
)

where CaseID is the match field to the parent Cases table and the break field for your report.

Place this calculation field in your sub-summary part.

 

[John Marzano 0]

Hi comment.

Your analysis of my amateurish explanation is correct.  The report is based on the childitems table.

In the screen grab below, the text in red should be where the sequential case number should go based on how many cases are found that have the status "Yes" (as in "Ship? Yes or No").. So Microwave TX would be case number 1 and mini Eclipse AKS would be case number 2 etc etc.

I'll try and implement your suggestion and thank you very much for your help. Greatly appreciated!

[John Marzano 0]

Hey comment,

Great work with your solution! It worked a treat.

That would have taken me forever and a great deal of studying to work out so thank you!