Link to home
Start Free TrialLog in
Avatar of Zkdog
ZkdogFlag for United States of America

asked on

Adding a Field Creates Repeating Fields

I have a Crystal Report tracking inventory as it flows through the plant.  The problem; when I add the due date to the report which is coming from only two tables so far, It repeats all of my fields over and over agian and takes a 20 page report up to a 200+ page report.

Any ideas on why this would be happening?

So far I have not even written a single formula due to this......
Avatar of 23637269
23637269
Flag of United States of America image

Hi Zkdog,

Is this a new problem?
When was the last time it worked?
Do you have a backup of when it worked?
Make sure the fields are trimmed and formated.
Can you recreate the report from scratch?

I have programmed in VB since 1990s' and do the reports within the app without using Crystal do to limitations that I would keep running into.

You a certainly welcome to post anything you like for any point value you like.  I try to read through what I can.  There are many many people here that are much more knowledgable than I am.  The thing that I am trying to let you know is that I have noticed that many of the experts set filters for a point range (ie 500 or more points), if a question dosen't fall into the filter(s), it will not bee seen.

Avatar of Mike McCracken
Mike McCracken

How are the tables linked?

If you delete the field from the report does the report return to the small size?

mlmcc
Avatar of Zkdog

ASKER

All the tables are linked by the same keyfield...Job Number.
If I delete the field it does go back to it's correct size.
Apparently the due date fields adds extra records.

Do you have a record in one of the tables that has multiples one for each due date for each step in the process?

You could group by the Job Number then sort by the DueDate field and put the details in either the Job Number group header or footer.  In that way only one record will be displayed for each Job Number.

mlmcc
To add to what mlmcc said, my guess is that due date is the only field that you're using from that table.  Without that field, CR doesn't actually use that table, but when you add that field, or anything else from that table, CR links in that table and, apparently, the JOIN is matching multiple rows, duplicating your data.

 It could be that the table actually has multiple matching records, but it may be that CR is simply linking on the wrong fields.  In CR 10, you can go to Database > "Database Expert" and click on the Links tab to see how CR is linking the tables.  You can also go to Database > "Show SQL Query" to see the query that CR is using.

 James
Avatar of Zkdog

ASKER

I see what your saying James, and I've attached the query below but it looks fine to me.  

mlmcc, I'll try that formatting trick in a few and see what happens.
 SELECT `jobhist`.`jobnum`, `jobhist`.`drname`, `jobhist`.`partnum`, `jobhist`.`totquant`, `jobhist`.`statcode`, `multdely`.`duedate`
 FROM   (`schedl` `schedl` INNER JOIN `multdely` `multdely` ON `schedl`.`jobnum`=`multdely`.`jobnum`) INNER JOIN `jobhist` `jobhist` ON `schedl`.`jobnum`=`jobhist`.`jobnum`
 WHERE   NOT (`jobhist`.`partnum` LIKE '2%' OR `jobhist`.`partnum` LIKE '3%' OR `jobhist`.`partnum` LIKE '6%')

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can use incluede Select Distinct jobnums so that you only get unique jobnums.
Avatar of Zkdog

ASKER

I haven't used the schdl table in the report yet, but it will be needed too for another field.

For now though, I'll take that table out and put it back in when I need it.  I understand what your saying.

If that doesn't work, I'll be trying yours next 23627269
Avatar of Zkdog

ASKER

Taking the schdl table out does remove the duplicates, and I'm giving out points but if someone could answer one last thing.  When I go to check a field in the schdl table and add it in to a formula in the report, it duplicates all the fields again...
Avatar of Zkdog

ASKER

I tried selecting distinct jobnum and while it cut out a good portion of the records, it is still repeating.
I tried unlinking schdl from muldely and linking through jobhist but it automatically links schdl to muldely instead of job hist and I cant get it to stick.
 SELECT DISTINCT `jobhist`.`jobnum`, `jobhist`.`drname`, `jobhist`.`partnum`, `jobhist`.`totquant`, `jobhist`.`statcode`, `multdely`.`duedate`, `schedl`.`class`, `schedl`.`complete`
 FROM   (`multdely` `multdely` INNER JOIN `jobhist` `jobhist` ON `multdely`.`jobnum`=`jobhist`.`jobnum`) INNER JOIN `schedl` `schedl` ON `multdely`.`jobnum`=`schedl`.`jobnum`
 WHERE   NOT (`jobhist`.`partnum` LIKE '2%' OR `jobhist`.`partnum` LIKE '3%' OR `jobhist`.`partnum` LIKE '6%')

Open in new window

What fields are you using from the schedl table?  Could there be more than one entry for a given job number?  For example, if there were a schedl.ID, could there be more than one ID value for a job number?  If so, then the "duplicates" would presumably be correct (one for each ID).

 I assume that you're trying to change the links in CR and it's changing them back.  I'm not sure why it would do that.  If you can't get the links in CR to give you what you need, you may have to change the report to use a stored procedure or CR "command", where you have control over the query

 James
Avatar of Zkdog

ASKER

Hmmm...Ok havent done anything like that before...  The main field I'm calling out of schdl is the complete field for each job process. It's a true/false field.  There isnt more than one jobnumber ever.  Our erm system's hard code doesn't allow for that.
It seems to make sense that there would only be one "complete" entry in schedl per job, but what about the other fields in schedl?  In particular, could there be more than one "class" per job?  That last SQL you posted included `schedl`.`class`.  If there is more that one "class" per job, you'd get multiple records, even with DISTINCT.  Have you tried it without `schedl`.`class`?

 BTW, where are you getting the query you posted?  From within CR, using Database > Show SQL Query, or somewhere else?  Just wondering.

 James
To get links you manually set to hold you have to turn SMART LINKING off.

Itis an option on the FILE --> OPTIONS (DATABASE Tab)

mlmcc
Avatar of Zkdog

ASKER

I'll try that mlmcc  

James:  Yes there are many classes per job in schdl, but I have another report almost identical to this one that doesn't do that.  I am getting the query from the show sql query option.
Avatar of Zkdog

ASKER

Ok with the supress duplicates on and linked correctly, I am still getting the repeat.  I understand now it is repeating once per class name and have tried supressing duplicates and a few other things to no avail.  I am thinking a sub-report might work and am going to try that but any more pointers would be extremely helpful.
mlmcc,

 Thanks for the smart linking thing.  I thought I remembered an option like that and looked for it, but it doesn't seem to exist in CR 10 (which is what I'm using).


 Zkdog,

 > I understand now it is repeating once per class name ...

 I suspect that I'm missing something, but if you're using class name on the report, isn't that what you'd want.  Otherwise, you wouldn't see every class name (Would you?).

 James
It looked like my suggestion of using DISTINCT was of use.
You also need to use the GROUP BY Clause for the rest of whay you are looking for.

Since mlmcc and James0628 were credited with solving and assisting they should have it for you.