Repeat Field Value

[esings2him]

Hi! I've been having trouble getting a field value to repeat so I can group on it and use it as a parameter. What I would like to see is the sales order number repeated as shown below.

SO# Sales Job Parent/Child Jobs TargetJobNum
0000093 WXYZ-0000093_1 001012 WXYZ-0000093_1
0000093 WXYZ-0000093_1 001012-XM502 001012
0000093 WXYZ-0000093_1 001012-RM208 001012
0000093 WXYZ-0000093_1 001012-WM207 001012
0000093 WXYZ-0000093_1 001012-YM210 001012

Currently, I'm getting these results:
SO# Sales Job Parent/Child Jobs TargetJobNum
0000093WXYZ-0000093_1 001012 WXYZ-0000093_1
WXYZ-0000093_1 001012-XM502 001012
WXYZ-0000093_1 001012-RM208 001012
WXYZ-0000093_1 001012-WM207 001012
WXYZ-0000093_1 001012-YM210 001012

Here's my formula for the SO#:
If Length ({Table.TargetJobNum}) > 6 then
Mid ({Table.TargetJobNum}, 6, 7)

I've created a RT and it gives me the results I want, but I can't group on it or use it as a parameter. What else would give the results I'm looking for?

 

[IanWaterman]

Looks like you have set formatted the SO# field to suppress if duplicated. Right click field-> format field -> common tab -> uncheck suppress if duplicated

Ian

 

[lbass]

Why not use:

If Length ({Table.SalesJob}) > 6 then
Mid ({Table.SalesJob}, 6, 7)

-LB

 

[esings2him]

Ian- That is incorrect; I don't have the suppress if duplicated checked; the data doesn't duplicate because the values aren't true for the If statement.

lbass-Your formula worked perfect, exactly like I wanted, but I can't group on it or add it as a parameter because the Table.SalesJob is a formula and not a Table.Field. Is there another way around this?

 

[lbass]

What is the content of {@SalesJob}?

-LB

 

[esings2him]

@SalesJob contents:

If maximum({Table.TargetJobNum},{@Job Nums 2}) < '6'
then ""
else maximum({Table.TargetJobNum},{@Job Nums 2})

@Job Nums 2 contents:
If instr({Table.JobNum}, "-") =0 then {Table.JobNum} else
split({Table.JobNum}, "-")[1]

@JobNums2 formula is what I'm grouping on to get all of the jobs together.

 

[lbass]

The solution is the same as in your last posted thread
thread767-1643737--to use a SQL expression. What happened when you tried that?

-LB

 

[esings2him]

I didn't try that; I figured out a different solution. I will try the sql expression and let you know how that goes. Thanks for all your help!