Dynamically Number Lists in Report

[SchoolTech]

How can I number my lists in reports that are created? I want to use Record Number Symbol, but when I have multiple lists on the same page, the second list continues with the next number rather than starting from 1 again.

[-Queue-]

I'm assuming you're referring to subsummaries when you write 'multiple lists'.

If so,

Create a summary field Count defined to be the Count of your serial field, a calculation number field InvCountByNum equal to 1 / GetSummary( Count; yourbreakfield ), another summary field TotInvCountByNum defined to be the running total of InvCountByNum, and an unstored calculation number equal to Let( T = TotInvCountByNum; GetSummary( Count; Num ) * Case( Position( T; "."; 0; 1 ); T - Int(T); 1 ) ). Put this final field on your layout in the Body part.

[SchoolTech]

Yes. I think I am talking about a subsummary report.

I'm trying to get a listing like this.

Math Students

1 David

2 John

3 Paul

Science Students

1 Mary

2 Steve

3 Victor

Then if I add a student Marsha to Math, it will add her and renumber in the report, and keep Science the same.

Math

1 David

2 John

3 Marsha

4 Paul

*********

If what your have suggested does this, I would love to try it. Unfortunately I'm not sure about what you wrote. Could you help clarify some things?

1) You said "Count of your serial field"...what is my "serial field"?

2) You also said "and an unstored calculation number equal to Let( T = TotInvCountByNum; GetSummary( Count; Num ) * Case( Position( T; "."; 0; 1 ); T - Int(T); 1 ) )." What is "Num"? Is this a particular field?

Thank you for your help.

[-Queue-]

Serial would be your unique id field. I used this field because it will always have a value. You can use whatever field you like, provided it will always have a value so that the summary Count will work correctly.

Sorry, Num should have been the same as yourbreakfield. I used a field called Num.

[SchoolTech]

Thank you. I will try this. I'm running into some problems. I don't get any value for the second field: InvCountByNum equal to 1 / GetSummary( Count; yourbreakfield ). It may be because I'm working with multiple tables. I'll try to move my data to one table for this summary. I'll let you know later if it works for me.

[SchoolTech]

This worked wonderfully. I'm still trying to figure out the logic on exactly how it works, but it works!

Thank Queue very much.

[-Queue-]

Okay, here's the logic.

GetSummary( Count; breakfield ) equals the number of records in the current subsummary set, based on breakfield. If the value for the breakfield is 2 on the current record and there are five records with a breakfield equal to 2, then this calculation will return the number 5 for all five records. When you divide this number by 1, as in the InvCountByNum field, each of the five records returns a value of 1/5 = 0.2. Creating a running total summary field based on this calculation produces

0.2 for the first record

0.4 for the second "

0.6 for the third "

0.8 for the fourth "

1.0 for the fifth "

if the value of 2 is the lowest value for breakfield in the current found set. If it is not the lowest value, then the first record in the set will contain 1.2 if it is in the second subsummary set, 2.2 if in the third, 3.2 if in the fourth, etc. Since the sum of InvCountByNum in each of set will add up to 1, in the final found record this field will equal the number of sets contained within the found set, based on breakfield, in other words, the number of unique breakfield values in the found set.

Let's consider the simple case where 2 is indeed the lowest value of breakfield in the found set.

GetSummary( Count; breakfield )     TotInvCountByNum      GetSummary( ) * Tot



                              5                                               0.2                                   1

                              5                                               0.4                                   2   

                              5                                               0.6                                   3   

                              5                                               0.8                                   4   

                              5                                               1.0                                   5

You can see that multiplying these two results gives the desired record number for that set. The only problem then is to ignore the TotInvCountByNum sum for previous summary sets when this set of records is not the first, i.e. 2 is not the lowest breakfield value. Since the sum of each set will equal 1, we simply have to remove the integer from the current sum to get the correct value for the current record in the current set. For example, the third record in this set would have a value of 1.6 if it were in the second subsummary set, 2.6 if in the third, etc. So the correct value will always be InvCountByNum - Int(TotInvCountByNum), e.g. 2.6 - 2 = 0.6, unless the current record is the last one in the current set. If it is the last record, subtracting the integer portion will return zero. To account for this, we multiply the GetSummary( Count; breakfield ) value by 1. In this case, it would be 5 * 1 = 5, giving us the fifth and last record in the current set. I used a decimal point test in my calculation. If it's the last record in the current set, then TotInvCountByNum will be a whole number; if it's not, then it will contain a decimal point. Putting it all together gives:

Let( T = TotInvCountByNum;

GetSummary( Count; Num ) * Case( Position( T; "."; 0; 1 ); T - Int(T); 1 )

)

which, in English, means the result of GetSummary (number of records in the current subsummary set) will be multiplied by the decimal portion of T, if there is a decimal point in T; otherwise it will be multiplied by 1.