Skip to Content
avatar image
Former Member

Missing records

Hello all,

I'm currently running a report where i get the totals of fields based on a date range, however it seems to be stopping at a certain point and not loading the data after it.
I've had a look and the data seems to be consistent, the only way i can explain it is that job number 1234 and 1235 have identical data but there are no records displayed after job number 1234, any ideas what could be causing that?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

10 Answers

  • Apr 20 at 08:08 AM

    Check in Report Options or General options and make sure Select Distinct records is not checked

    Ian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 21 at 07:50 PM

    That option is already unchecked

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 22 at 06:15 PM

    Ok so i re-created my report and took it back to basics and it's even more puzzling.
    i have a job table and a timesheet table, some jobs have timesheets and some do not, i have used an outter join to display the jobs even when a timesheet doesn't exist. Jobs 1234 and 1235 both have a time sheet with the same amount of hours, in the raw data everything is as it should be, when i run the report 1234 displays fine but 1235 does not.
    Can anyone think of a reason why one works and the other does not??
    Thanks in advance,
    Luke

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 23 at 08:22 AM

    Hi Luke,

    What is the record selection formula you've used?

    Do you have a group that is not "Job#"?

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 23 at 08:14 PM

    OK, so I've tried various selection formulas to rule out why this is happening, my original formula is this

    ({timesheet1.Time_Date} <={?timDateUJC} and {jobsheet1.Job_JobCompleted} = 0) 
    or 
    ({supplierinvoice1.SpInv_Date} <= {?supDateUJC} and {jobsheet1.Job_JobCompleted} = 0)
    
    
    

    This only displays uncompleted jobs and I have grouped the results by the Job number.
    I have created a fresh report with some basic fields, no groups and a simple formula - ({timesheet1.Time_Date} <={?timDateUJC} - this works fine on its own and displays the record, when I change the formula to ({timesheet1.Time_Date} <={?timDateUJC} and {jobsheet1.Job_JobCompleted} = 0) the record isn't displayed.
    I have tried changing links but nothing seems to be working

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 23 at 11:27 PM

    UPDATE:

    On the new crystal report I created for testing I added the job complete field to display the status for each record, for some reason i have some records that do not have a 1 (complete) or 0 (not complete), this would lead me to believe that its a data issue however when i inspect the database the missing records actually have a 0 in the cell

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 24 at 09:41 AM

    Can you show SQL

    Are you left joining to the table jobsheet1

    If yes then your filter/joins could be resulting in a Null for the field Job_JobCompleted

    Ian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 24 at 09:37 PM

    I'm a bit puzzled by this, i'm convinced its a joining issue but the report is displaying some records and not others, what i mean by this is this...
    job# hours amount complete

    1 2 20 1

    2 2 10

    3 5 40 0

    which is then not being reported when i run the proper report asking only for non complete jobs.

    The original SQL is below

    SELECT `customer1`.`Cust_Name`, `jobsheet1`.`Job_ID`, `jobsheet1`.`Job_StartDate`, `timesheet1`.`Time_Hours`, `supplierinvoice1`.`SpInv_Amount`, `timesheet1`.`Time_Date`, `supplierinvoice1`.`SpInv_Date`, `jobsheet1`.`Job_JobCompleted`, `timesheet1`.`Time_BillRate`, `jobsheet1`.`Job_Description`
     FROM   {oj ((`DATABASE`.`customer` `customer1` INNER JOIN `DATABASE`.`jobsheet` `jobsheet1` ON `customer1`.`Cust_ID`=`jobsheet1`.`Cust_ID`) LEFT OUTER JOIN `DATABASE`.`supplierinvoice` `supplierinvoice1` ON `jobsheet1`.`Job_ID`=`supplierinvoice1`.`Job_ID`) LEFT OUTER JOIN `downgarage_Software`.`timesheet` `timesheet1` ON (`jobsheet1`.`Eng_ID`=`timesheet1`.`Eng_ID`) AND (`jobsheet1`.`Job_ID`=`timesheet1`.`Job_ID`)}
     WHERE  (`timesheet1`.`Time_Date`<{ts '2018-04-23 08:26:55'} AND `jobsheet1`.`Job_JobCompleted`=0 OR `jobsheet1`.`Job_JobCompleted`=0)
     ORDER BY `jobsheet1`.`Job_ID`
    

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 25 at 07:27 AM

    I think you problem lies here

    WHERE (`timesheet1`.`Time_Date`<{ts '2018-04-23 08:26:55'}
    AND `jobsheet1`.`Job_JobCompleted`=0

    OR `jobsheet1`.`Job_JobCompleted`=0)

    Using OR without brackets can cause problem try

    WHERE (`timesheet1`.`Time_Date`<{ts '2018-04-23 08:26:55'}
    AND (`jobsheet1`.`Job_JobCompleted`=0 OR `jobsheet1`.`Job_JobCompleted`=0)

    )

    Ian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 25 at 08:52 AM

    Sorry Ian i may have confused things there, you are indeed correct but this is not where my problem lies (i must have copied the wrong selection formula) that formula does work while the brackets are in place but still doesn't return all records.

    What i have done is re-create a basic report to isolate my problem and i think the attached screenshot will help explain my problem.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Ian Waterman

      Ian, you were indeed correct, i ran the sql query outside of CR and i am getting the same problem so its probably not a CR issue.
      Again thank you for your assistance, you've been a great help