Skip to Content
author's profile photo Former Member
Former Member

Placing multiple records on a single line (variables)

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 21, 2007 at 04:55 AM

    Post Author: GraemeG

    CA Forum: Formula

    I've whizzed you over an email, matey.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 25, 2007 at 01:31 PM

    Post Author: blu_b

    CA Forum: Formula

    Hi Guys,

    Come on we all want to see the solution!

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 26, 2007 at 05:34 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.