Jump to content

counting records in a leading sub summary part


Recommended Posts

  • Newbies

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?

Link to post
Share on other sites

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.

 

Link to post
Share on other sites
  • Newbies

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!

image.thumb.png.00411a653772469680dfa8559ab99207.png

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.