-1

I have 2 tables linked in Crystal (Jobplan & Jobtask). There are 3719 job plans. Each has multiple tasks but every one has a task numbered 1. I deliberately deleted one task numbered 1 from one job plan. How do I write in crystal to show me the one job plan that does not have a task numbered '1'?

gz3s36
  • 1
  • 1

2 Answers2

0

Without knowing anymore than you have given here, my best thought would be to group on Jobplan, create a formula for minimum task number/ID, and place this in the group header. Sort by minimum task number/ID descending. Again, the answer is a work around because I’m not exactly sure of all the details of your issue/question.

SMcHugh
  • 36
  • 8
  • What info would help you help me? – gz3s36 Nov 09 '17 at 21:24
  • Can you tell me what you are ultimately trying to do? What is the expected output? – SMcHugh Nov 09 '17 at 21:42
  • JOBPLAN.JPNUM JOBTASK.JPTASK 135 1 854 10 9845 20 45 30 480 40 3338 50 2965 60 247 70 – gz3s36 Nov 10 '17 at 11:24
  • I have two tables linked together (jobplan & jobtask) My jobplans all have tasks to perform. Expexted output is to find all jobplan.jpnum that do NOT have jobtask.jtask 1 tied to it – gz3s36 Nov 10 '17 at 11:53
  • Another way to put it is show me all jpnum that don't start with jptask #1...This report is reporting on MAXIMO tables. – gz3s36 Nov 10 '17 at 12:08
  • Okay. I’m on a plane now and will provide a solution later. – SMcHugh Nov 10 '17 at 13:58
0

Set up your Crystal Report like this:

  1. Link JOBPLAN and JOBTASK tables using a left outer join so that you get all the job plans even if you have null job tasks (an inner join will exclude job plans with null job tasks).
  2. Create a formula to handle the null JOBTASK scenario (let's call it HandleNulls) IF ISNULL{JOBTASK.JPTASK} THEN 0 ELSE {JOBTASK.JPTASK}
  3. Set up the details section with column 1 as JOBPLAN.JPNUM, and column 2 as formula created in #2; HandleNulls.
  4. Group your details on JOBPLAN.JPNUM.
  5. Add a running total field to the group header MINIMUM{HandleNulls}, evaluate on change of group, reset on change of group.
  6. Suppress details and group footer.
  7. Create a suppression formula for group header {HandleNulls} = 1

This will give you a report that lists JOBPLAN.JPNUM and the minimum JOBTASK.JPTASK if it is not 1. And, if there is no JOBTASK.JPTASK set up for the JOBPLAN.JPNUM, then it lists the minimum JOBTASK.JPTASK as zero indicating that there are no Job Tasks for that Job Plan.

Finally, I would like to add that Crystal Reports, while a good tool that can handle this type of report, it is much better to create the needed information in SQL as a command and display the results in Crystal Reports for your user.

There are a few ways to structure this query and I'm not completely sure of the structure of your tables, but this query should get you on the way to a command.

SELECT
    JOBPLAN.JPNUM,
    MIN(ISNULL(JOBTASK.JPTASK,0))
FROM
    JOBPLAN
LEFT OUTER JOIN
    JOBTASK
    ON JOBPLAN.JPNUM = JOBTASK.JPNUM
GROUP BY
    JOBPLAN.JPNUM
HAVING
    MIN(ISNULL(JOBTASK.JPTASK,0)) <> 1
SMcHugh
  • 36
  • 8
  • Thank you so much for the help. It is greatly appreciated. I did try SQL by adding command but I couldn't the code right. Too bad you can't just copy Maximo SQL and paste it in the add command. – gz3s36 Nov 12 '17 at 23:20