Hi

Im trying to create a report that uses multiple tables that makes up a invoiced job.
Each line has a unique job number with fields such as Customer, Subject, Machine, Job category and Invoice price.

The Machine and Job Category data is stored in a different table along with hundreds of different items in MySQL but it seems
when I run my report all of these items display each in there own row.

Ive created two Formula fields as below:

Job_Category Formula Field =   If {Jobinfos1.Infold} = Job_Category   then {Jobinfos1.Val}   else """"

Machine        Formula Field =   If {Jobinfos1.Infold} = Machine   then {Jobinfos1.Val}   else """"


Job No      Subject      Machine      Job_Category      Invoice price.
700001     Mars                                                        £2020.44
700001     Mars                                                        £2020.44
700001     Mars                                New                  £2020.44
700001     Mars                                                        £2020.44
700001     Mars                                                        £2020.44
700001     Mars          Bobst                                      £2020.44
700001     Mars                                                        £2020.44

It should look like this:

Job No      Subject      Machine      Job_Category      Invoice price.
700001     Mars          Bobst              New                  £2020.44
700002     B&Q           Martin             New                  £781.23
700003     Mars          Bobst               New                  £658.34
700004     Tesco        Gopfert            New                  £1010.00
700005     Tesco        Gopfert        Replacement          £452.00

does anyone have any idea how I can get this to work as I only need one line to appear per job no with the machine data and job category in the same line.

Is there no way that crystal reports can just add the result of my IF statement and ignore all the other records in the table without creating a group.
Maybe a SQL field to select the matching data.

I believe they are joined correctly it's just I'm asking to display the value field if infold is equal to job_category and what I need it to do is only display the matching field and ignore the other records.
My If statement ends with. Else """" which creates the multiple blank rows for the values that don't equal job_category. Is there a formula that displays the correct value and ignores the wrong ones.

Your IF-THEN statements really look like join criteria.

under Database there is a "show SQL query" option.