How to match and group by a field 1

[esings2him]

Hi! I'm trying to match all jobs that begin with the same first four characters then group by them and also, add or duplicate the sales order number to the jobs that have a zero value. Here's an example of what I have:
Job No. Sales Order
1043 97
1043 97
1043-PM102 0
1043-PM102 0
1043-PM102 0
1043-PM102 0
1043-TM111 0
1043-TM111 0

Here's what I want:

Job No. Sales Order
1043 97
1043 97
1043-PM102 97
1043-PM102 97
1043-PM102 97
1043-PM102 97
1043-TM111 97
1043-TM111 97

There are some jobs that have a six digit prefix that may have a sales order attached that would need the same matching and grouping.

001015-EM206 0
001015-TM207 0
001015-TM207 0
001015-TM207 0
001015-TM207 0
001015-TM207 0
001015-TM207 0
001015-TM207 0
001015-TM207 0
001015-TM207 0
001015-TM207 0

How would I go about this? Thanks for all help!

 

[IanWaterman]

Group on this formula and the sort by jobno to bring thos wothout - to top of list.

@group
If instr({JobNo}, "-") =0 then {JobNo} else
split({JobNo}, "-")[1]

Replace sales order field with this formula

@salesOrder

whileprintingrecords;

global numbervar salesorder;

If {salesorderfield} <> 0 then salesorder:= {salesorderfield};

Ian

 

[esings2him]

Ian,
Thank you! Your group by formula is awesome! However, your sales order formula is still giving me the same results as I displayed before.

Job Number Sales Order
1043 (Grouping)
1043 1043 97.00
1043 1043 97.00
1043-PM102 1043 0.00
1043-PM102 1043 0.00
1043-PM102 1043 0.00
1043-PM102 1043 0.00
1043-TM111 1043 0.00
1043-TM111 1043 0.00

I would like the Sales Order number to duplicate for the corresponding group or header job.

Job Number Sales Order
1043 (Grouping)
1043 1043 97.00
1043 1043 97.00
1043-PM102 1043 97.00
1043-PM102 1043 97.00
1043-PM102 1043 97.00
1043-PM102 1043 97.00
1043-TM111 1043 97.00
1043-TM111 1043 97.00

 

[esings2him]

Ian,
Thanks for your help! I figured out the rest by adding a summary on the Sales Order field and taking the maximum. I inserted it into your formula and added an else statement and it works perfect!

If {JobProd.OrderNum} <> 0
then {JobProd.OrderNum}
else salesorder:= Maximum ({JobProd.OrderNum}, {@Job Number});