How to Sort on Just Part of the Field 3

[JohnBates]

Hi,

I want so sort my table on a julian date that is in the middle of each record's OrderNumber. The secondary sort field will be ModelNumber.

Here is an example of the data:

OrderNumber ModelNumber QtyOrdered QtyFilled

RD031268714 AAA 100 99
RD031268721 AAA 200 50

RD031268714 BBB 100 0

RD031348714 AAA 50 25

----------------------
The report should be a summary containing model totals within date:

03126 AAA 300 149
BBB 100 0

03134 AAA 50 25
----------------------

So I guess my first question is - How can I select and sort
on the middle portion of the OrderNumber - characters 3 thru 7 ?

I'm fairly new to CR, using CR 6.

Thanks, John

 

[mbarron]

Create the following formula then use it as your sort/group field:
mid({your.field},3,4)

Mike

 

[lbass]

Create a formula {@date}:

mid({OrderNumber},3,5)

Then group on @date to create Group 1, and group on ModelNumber for Group 2. The groupings will create the sort order automatically. Then just insert summaries on {QtyOrder} and {QtyFilled}. You can drag these to the Group 2 header, and then copy the Group 1 name (Date) into the Group 2 header and suppress the Group 1 header.

-LB

 

[synapsevampire]

Rather than sort on this, create a formula (Insert->Field Object->Right click Formula Fields and select New) which will be used to group on, the formula is something like the following:

mid({YourTable.Order},3,5)+{YourTable.model}

Now select Insert->Group and select the formula you createed in the above.

Place your table fields that you wish to sum in the details section, right click them and select insert summary->Sum

The summaries will be created for you in the group footer.

Now suppress (Right click the section and select Suppress (no drill down)) the Details and Group Header sections and place the actual {Table.Order} and the {table.modelnumber} field in the group footer alongside the summaries and you're done.

-k

 

[JohnBates]

wow thanks everyone !

Your ideas worked perfectly.
A star to each - I'm feeling generous today

John