Placing multiple records on a single line (variables)

[Former Member]

Post Author: AeroProg

CA Forum: Formula

Hi, I am new to Crystal Reports, but I know Basic and other programming. I have Crystal Reports XI and am pulling data from our ERP/MRP system, Epicor Vista (Progress DB).I've been asked to figure out a Crystal Reports for our company (I get thrown into these projects). I know what the report should look like and I know how I would go about some VB code in a macro in Excel if all the data was in worksheets(i.e. like tables).Below is the data. Any help would be SO appreciated. So far I'm loving Crystal Reports and I can't wait to get some reports our company can start using but I'm stuck on understanding the timing and connection of formulas with the records.Table1 "JobMtl"Field "JobComplete":StringField "JobNum":StringTable2 "JobOper"Field "OpComplete":BooleanField "OprSeq":Number{JobMtl.JobComplete}FalseTrue{JobMtl.JobNum}20102011{JobOper.Complete}FalseTrue{JobOper.OprSeq}10203040Let's say I dragged all 4 fields into a report. It would look like this.JobNum JobComplete OprSeq OpComplete2010 False 10 True2010 False 20 True2010 False 30 False2010 False 40 False2011 False 10 True2011 False 20 False2011 False 30 FalseI would it to read like thisJobNum JobComplete PrevOp CurrOp NextOp2010 False 20 30 402011 False 10 20 30**Note: {JobMtl.JobComplete} will be used so I am only reporting jobs that are "not complete". I guess it means nothing to you guys, but I put it here because I was not sure if this will be involved in a formula.Thanks,AnthonyMy email is ls1z282002_at_yahoo.com (replace "_at_" => "@") if you would a *.RPT with the data I've shown.

[Former Member]

Post Author: GraemeG

CA Forum: Formula

I don't know if you can attach files... if you can I'll attach the sample. In the mean time, here's the rub...

(By the way, I use basic syntax in my formulae... old habits and all that!)

I created two tables (actually they were two sheets in a spreadsheet) called JobMtl and JobOper and put some records in in the format AeroProg described above.

Create a formula called aResetVariables:

'Define shared variables

shared CurrOpNo as number

shared PrevOpNo as number

shared NextOpNo as number

'Set Variables to zero

CurrOpNo = 0

PrevOpNo = 0

NextOpNo = 0

'This next line is just to keep Crystal happy

formula = 0

Create one called bCurrentOp:

'Define shared variables

shared CurrOpNo as number

shared PrevOpNo as number

shared NextOpNo as number

if CurrOpNo = 0 and {JobOper_.JobComplete} = "false" then

CurrOpNo = {JobOper_.OprSeq}

end if

formula = CurrOpNo

Create one called cPreviousOp

'Define shared variables

shared CurrOpNo as number

shared PrevOpNo as number

shared NextOpNo as number

if {JobOper_.OprSeq} < CurrOpNo or CurrOpNo = 0 then

PrevOpNo = {JobOper_.OprSeq}

end if

formula = PrevOpNo

Create one called cNextOp

'Define shared variables

shared CurrOpNo as number

shared PrevOpNo as number

shared NextOpNo as number

if {JobOper_.OprSeq} > CurrOpNo and CurrOpNo <> 0 and NextOpNo = 0 then

NextOpNo = {JobOper_.OprSeq}

end if

formula = NextOpNo

In your Crystal report, group by the JobNum and put the aResetVariables formula into the group header. This will make sure that the variable are set back to zero every time the Job No changes. Suppress the group header.

Plonk what ever you want into the detail line but suppress that too.

Put the JobNum, JobComplete, cPreviousOp, bCurrentOp and dNextOp formulae into the footer. (Donu2019t suppress this section!)

Run the report and you should get a line for each job showing the previous, current and next operation. If you tinker with the status' it seems to work everything out okay.

[Former Member]

Post Author: blu_b

CA Forum: Formula

Hi Guys,

Come on we all want to see the solution!

[Former Member]

Post Author: GraemeG

CA Forum: Formula

I've whizzed you over an email, matey.