Sub Summary Report in Natural Order

[Ocean West]

Been wrestling this for a bit - i have a sub-summary report that has no body part  and summarizing and sorting by group and lot numbers 

in the portal for data entry they appear in natural order (the order entered)

There are other fields that are entered or multiple records for one lot number however on the report we need it summarized but we would like the lot numbers to be in the natural order as they were entered in the portal.

I am trying to figure out a way to force the lot numbers in the natural order.

 

 

[comment]

Could you provide a clearer description? I got lost trying to figure it out. How many sub-summary parts do you have?

[Ocean West]

in short of a better option i had to generate the report before the fedex gal came to pickup the packages

what i ended up doing is this GTRR to all the items no sort on relationship so everything is in natural order.

then i serialized a number field starting at 1. then looped thru the set of records and setup a multi predicate relationship based on the lot number and invoice and then if the relationship matched set the local serial field to that of the related value. 

Then changed the last part to be sorted by their serial field 

 

it worked but means have to touch every record every time it's printed. 

[Aussie John]

Could you get the record number in a calc and then sort by that? Sounds too simple. 

[comment]

14 hours ago, Ocean West said:

we would like the lot numbers to be in the natural order as they were entered in the portal.

I still don't understand this. Do you want to order the lot groups by the order in which the lots themselves were created (presumably in their own table), or by the order in which records in this table were created - so that the groups are arranged by the creation order of the first member of the group?

If the latter, I believe you could define a summary field as Minimum of SerialID and use it to re-order the groups.

[Ocean West]

sorry haven't responded earlier been moving this weekend. 

the records in the portal are merging lots 

product | internal lot   |  external lot | qty

abc |  123   |  XYZ | 10
abc |  124   |  XYZ  | 15
abc |  125   |  XYZ  | 10

def |  712   |  MNO | 50
def |  725   |  MNO | 25
def |  732   |  MNO | 10

there could be several products purchased that are made up of different lot / production run numbers (internally )  yet the customer was sent  samples weeks months ago and they approved a lot number but the nature of production the quality purchased can never be produced in one "lot number" the lot number they select is more or less make the rest like this one. which we can determine the technician raw materials and machines used to produce the original samples. and we make more. 

the contents of boxes are packed and the merging of multiple lots in to one "lot" is done during this step and the desired Packer's Worksheet is the public facing report we put in the box.

the users that are packing the boxes are putting product in the order they collected them from inventory - this information above is entire into a portal 

the report should should be:

abc | XYZ | 35
def | MNO | 80

typically a sub summary report with no body the MNO would sort above XYZ

hence the issue 

[dwdata]

2 hours ago, Ocean West said:

sorry haven't responded earlier been moving this weekend. 

the records in the portal are merging lots 

product | internal lot   |  external lot | qty

abc |  123   |  XYZ | 10
abc |  124   |  XYZ  | 15
abc |  125   |  XYZ  | 10

def |  712   |  MNO | 50
def |  725   |  MNO | 25
def |  732   |  MNO | 10

there could be several products purchased that are made up of different lot / production run numbers (internally )  yet the customer was sent  samples weeks months ago and they approved a lot number but the nature of production the quality purchased can never be produced in one "lot number" the lot number they select is more or less make the rest like this one. which we can determine the technician raw materials and machines used to produce the original samples. and we make more. 

the contents of boxes are packed and the merging of multiple lots in to one "lot" is done during this step and the desired Packer's Worksheet is the public facing report we put in the box.

the users that are packing the boxes are putting product in the order they collected them from inventory - this information above is entire into a portal 

the report should should be:

abc | XYZ | 35
def | MNO | 80

typically a sub summary report with no body the MNO would sort above XYZ

hence the issue 

Define a new break field:

product_ext_lot_key = (Calculation, Text Result) = product & external_lot

then define the corresponding summary field if yu have not already:

sum_Qty = (Summary, Total of Qty)

then define your LAYOUT PARTS as follows:

PART (in caps)
    context (lower case)

HEADER
    stuff...
SUB-SUMMARY (by "product_ext_lot_key" - Above)
    product    external_lot          sum_Qty
TRAILING SUMMARY
    Total =  sum_Qty
FOOTER
    stuff....

Just sort by the new "product_ext_lot_key" field. Will that achieve what you need?

I hope this helps!

[comment]

Actually, the more you explain this, the more confused I get. For example, it seems that the "internal lot" plays no part in this - so why not remove it from the example? I also suspect that every product in a lot (external) is different, except by coincidence.

Anyway, have you tried what I suggested? Here is a simple demo of the method:

 

 

ReOrderGroups.fp7

Edited February 7, 2016 by comment