Numbering records via self-join

[trevorg]

Hi,

I have a sub-summarized report that looks similar to this:

(Sub-Summary Part) Count of all (A) Body Items = (4)

(Body) (A) Body Item

(Body) (A) Body Item

(Body) (A) Body Item

(Body) (A) Body Item

(Repeats with next Sub Summary.....(,©, etc..)

I would like it to look like this:

(Sub-Summary Part) Count of all (A) Body Items = (4)

(Body) 1. (A) Body Item

(Body) 2. (A) Body Item

(Body) 3. (A) Body Item

(Body) 4. (A) Body Item

(Repeats with next Sub Summary.....(:,©, etc..)

The numbering would start over with the next item (. There might only be 1 ( item, or there might be hundreds, the numbering would need to be automatic.

Note: The 4 Body Parts represent the repeating effect shown when printed or in preview mode. (I don't actually have 4 body parts!)

Currently, the count of the Body Items is accomplished through a self-join. (Count(BodyItems::SNBodyItem))

My Question is: How do I get the numbering effect next to each body item? Maybe a relationship isn't the way to do this? I tried doing a running count summary on each, but it keeps on running and doesn't restart the number after each change in summarized item.

Any suggestions would be appreciated!

[RussBaker]

Trevor,

You can assign each record a new CategoryNumber on creation by using a self relationship based on the Category (A) and making the number = 1 + Max(Relationship::CategoryNumber).

The disadvantage of this is that if you delete a record, then you will get a gap in you category numbers. If this is not acceptable then you need a script to assign category numbers to the records. I will send you a demo file, but here is the guts of it. (This may not be the most elegant way to do it)...

You need the following extra fields:

Count of Items = summary (Count of Item Description)

Has this record been renumbered (text)

Item number in Category (number)

g_Copied Item Category (global, text)

You need a layout with a sub-summary sorted by Item Category.

# Script to assign all records a category number

Set Error Capture [ On ]

Allow User Abort [ Off ]

Freeze Window

Show All Records

Replace [ Has this record been renumbered, "No" ] [ No dialog ]

# Loop through the records

Loop

. # Find records which have not been re-numbered

. Enter Find Mode []

. Set Field [ Has this record been renumbered, "No" ]

. Perform Find []

. Exit Loop If [ Status(CurrentFoundCount) = 0 ]

. Go to Record/Request/Page[ First ]

. Set Field [ g_Copied Item Category, Item Category ]

. # Find records from that specific category

. Enter Find Mode []

. Set Field [ Item Category, g_Copied Item Category ]

. Perform Find []

. Perform Script [ "Sort by Category then Description"] [ Sub-scripts ]

. Replace [ Has this record been renumbered, "Yes" ] [ No dialog ]

. Go to Record/Request/Page [ First ]

. # Assign each record a category number

. Loop

.. Set Field [ Item Number in Category, Status(CurrentRecordNumber) ]

.. Go to Record/Request/Page [ Next, Exit after last ]

. End Loop

End Loop

# Show all records, sort and preview

Show All Records

Perform Script [ "Sort by Category then Description"] [ Sub-scripts ]

Go to Layout [ Preview ]

Enter Preview Mode [ Pause ]

Go to Layout [ original layout ]

Enter Browse Mode

[trevorg]

Wow.

Excellent! I will get to work implementing and testing this very soon. Is this something you have worked out previously?

If you could email the demo file to me that would be fantasic. My email address is listed in my profile.

Thanks again!

Trevor

[RussBaker]

Trevor,

This is a modification of what I use to calculate category placings in race timing systems... By the way, I get messages back that your email is full so I can't send a 28K file to you.