Formula producing correct and incorrect data

[esings2him]

Hi all! I have a report that has parent and child jobs that need to be linked to the top most job. The parent job is linked to the top most job; however, the children are not. I have a formula that groups all of the parent and child jobs together and I, also, have a formula that groups parent and child jobs to the top most job. For some reason, the top most job is not linking on the correct child jobs on some of the jobs. Here are my formulas and data below:

Formulas:
@Job Number
If instr({PartTran.JobNum}, "-") =0 then {PartTran.JobNum} else
split({PartTran.JobNum}, "-")[1]

@GP Job
WhileReadingRecords;

global stringvar GPJob;

If {@Job Number} <> {JobProd.TargetJobNum} then GPJob:= {JobProd.TargetJobNum}
else
If {@Job Number} = {JobProd.TargetJobNum} then GPJob:= GPJob

Incorrect Data:

1058
SO# JobNum TargetJobNum @GPJob @JobNum
0 1058 TVDB-0000103-1 TVDB-0000103-1 1058
0 1058-EM208 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
1059
0 1059-PM205 1059 TVDB-0000103-1 1059
0 1059-EM201 1059 TVDB-0000103-1 1059
0 1059-BM503 1059 TVDB-0000103-1 1059
0 1059-TM208 1059 TVDB-0000103-1 1059
0 1059-BM503 1059 TVDB-0000103-1 1059
0 1059 TVDB-0000106-1 TVDB-0000106-1 1059
0 1059 TVDB-0000106-1 TVDB-0000106-1 1059

How it should appear:

1058
SO# JobNum TargetJobNum @GPJob @JobNum
0 1058 TVDB-0000103-1 TVDB-0000103-1 1058
0 1058-EM208 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058
0 1058-TM211 1058 TVDB-0000103-1 1058

1059
0 1059-PM205 1059 TVDB-0000106-1 1059
0 1059-EM201 1059 TVDB-0000106-1 1059
0 1059-BM503 1059 TVDB-0000106-1 1059
0 1059-TM208 1059 TVDB-0000106-1 1059
0 1059-BM503 1059 TVDB-0000106-1 1059
0 1059 TVDB-0000106-1 TVDB-0000106-1 1059
0 1059 TVDB-0000106-1 TVDB-0000106-1 1059

How to correct this problem? Thanks!

 

[lbass]

It looks to me like you need to sort by job number ascending, and then change your formula to "whileprintingrecords".

Another method might be to add a reset formula in the group header where you set the value gpjob (again, whileprintingrecords);

WhilePrintingRecords;
global stringvar GPJob := maximum({JobProd.TargetJobNum},{table.groupfield});

Not sure the name of the groupfield.

-LB

 

[esings2him]

Lbass,
Thanks for your help! It always appreciated!

Job number is currently sorted by ascending transaction date that is why it is displayed the way it is and that's how the user is wanting it.

I used "whilereadingrecords" because I couldn't group on the formula using "whileprintingrecords".

When I, previously, tested my own reset, it didn't make a difference; same errors. When I added your formula in the group header as a reset, it still didn't give me the correct information. However, when I placed it in details, it worked wonderfully, but I still can't group on that formula. How can I accomplish this?

 

[lbass]

Please identify exactly what you are grouping on by field. It looks to me like you are grouping on {@JobNumber}. And your desired format does not indicate a second group on your whilereadingrecords formula--it wouldn't display that way.

-LB

 

[lbass]

Sorry, I guess I was wrong about that last statement. If there is only one appropriate value for {@GPJob}, and it always starts with a letter, I think you could just change your GPJob formula to the following:

maximum({JobProd.TargetJobNum},{@Job Number})

...and don't reference any variables.

-LB

 

[esings2him]

Lbass,
I used your formula and it works properly, but I still can't group on the @GPJob formula. It doesn't show up as one of the options in group expert.

 

[lbass]

Why are you trying to group on it if it has the same value throughout the {@Job Number} group?

-LB

 

[esings2him]

I'm wanting to group on it so I can see all of the related jobs associated with it then I will add Sales Order field that will link to the @GPJob field results. Here's a sample look of what I'm wanting in the end.

SO# GPJob Jobs Parts Ext. Cost
98 TVDB-0000098-1 1046 Part # $$
1046-PM211 Part # $$
1046-EM205 Part # $$
1046-TM206 Part # $$
1057 Part # $$
1057-EM014 Part # $$
1062 Part # $$
1062-PM109 Part # $$

 

[lbass]

What do you mean by linking? Are you adding subreports?

-LB

 

[esings2him]

No, I'm not adding subreports. I mean correspond when I say link. SO# corresponds with TVDB-0000098-1 job number.

 

[lbass]

You can try creating a SQL expression like this:

(
select max(`TargetJobNum`)
from JobProd A, PartTran B
where A.`linkingfield` = B.`linkingfield` and
substr(B.`JobNum`,1,4) = substr(PartTran.`JobNum`,1,4)
)

Not sure what your database is. Functions available and punctuation would be specific to that. Also your version of CR is important. This assumes that the first 4 digits are always what you are after for the job number. Also, since SQL expressions access the database directly, you may need to build in parts of your record selection formula.

If you can successfully compile the SQL expression, you can group on it.

-LB

 

[esings2him]

Lbass,
It's a Progress DB and I'm working with CR 11 R2. I'll try that or some variation of that and let you know how it goes. Hopefully, I'll get the syntax correct! Thanks for your help!