removing duplicates in formula workshop

I am trying to create a report that displays a group of suppliers and provide a record number from a field {DM_DOCUMENT.DMSN} if a second field {DM_DOCUMENT.DM_TYPE_CODE} is equal to a specific parameter (in this case "SOAD"). WHat is happening is that the report shows multiple instances of duplicate records. I created a formula in the formula workshop as follows:

(
if {DM_DOCUMENT.DM_TYPE_CODE}="SOAD" then totext({DM_DOCUMENT.DMSN},0,"") else " "
);
(
if {DM_DOCUMENT.DMSN}<>previous({DM_DOCUMENT.DMSN}) then totext({DM_DOCUMENT.DMSN},0,"")
)

However, I am still getting duplicate lines. I haven’t been able to determine any pattern or cause for the duplicate lines. I have looked at the following:
•      From the database expert I tried adjusting linking parameters
o      Supplier#
o      DMSN
o      Adjusting join type, enforce join.
•      From the Section Expert, Details, Suppress (no drill-down) added the following code:
o      {DM_DOCUMENT.DM_TYPE_CODE}<>"SOAD"
o      or
o      {@SOAD Determination} =previous({@SOAD Determination})
•      Selected the DM# for Assessment (SOAD Determination field), format field, common tab, suppress if duplicated, added code:
o      {@SOAD Determination} =previous({@SOAD Determination})

None of these changes seem to impact (remove) duplicate records.
Any other suggestions?
ThuillierdAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
The two If statements that you posted are in the same formula?  If so, what are you trying to do with the first one?  It will produce a string value, but that will simply be discarded as soon as CR moves on to the next line in the formula.  So the first If really does nothing at all.

 Did you really mean something like this?

if {DM_DOCUMENT.DM_TYPE_CODE}="SOAD" then
  if {DM_DOCUMENT.DMSN}<>previous({DM_DOCUMENT.DMSN}) then totext({DM_DOCUMENT.DMSN},0,"")


 Either way, if you're only interested in the "SOAD" records, and the report is not sorted by DM_TYPE_CODE, then a basic issue is that Previous just looks at the last record that was read.  It's not the previous "SOAD" record, or the previous unsuppressed record.

 If you only want to see DMSN on the "SOAD" records, the simplest answer would be to only include the "SOAD" records in the report (presumably not an option), or sort by DM_TYPE_CODE, so the "SOAD" records are all together.  Otherwise, you may have to use a variable to save the DMSN value from the last "SOAD" record.

 James
mlmccCommented:
I agree with James.

I assume there is other data on a record you need to see otherwise filter out anything except the SOAD records.

Also any time you use previous or next you need to ensure you aren't on the first(last) record.

Do you have the data grouped on DM_DOCUMENT.DMSN or at least sorted by that?

mlmcc
ThuillierdAuthor Commented:
The report provides a list of suppliers associated to a specific project. The data is grouped by supplier and provides data from multiple tables (rating info, open orders, project balance due, etc.). "DM_DOCUMENT" is a table containing different types of documents. Some suppliers have multiple "DM_DOCUMENT" records, while others may not have any records. Each Supplier may have zero, one, or multiple {DM_DOCUMENT.DM_TYPE_CODE} associated to that supplier. The  {DM_DOCUMENT.DM_TYPE_CODE} indicates the type of document retained in that record. I am only looking for the  {DM_DOCUMENT.DM_TYPE_CODE} that indicates the record contains a SOAD (Assessment report). Some suppliers have one (or more) SOAD, other suppliers do not have a SOAD but may have a different {DM_DOCUMENT.DM_TYPE_CODE}. Some suppliers may have multiple {DM_DOCUMENT.DM_TYPE_CODE}.
I created a formula field "SOAD Determination" using the following code to filter specifically for "SOAD".

  {DM_DOCUMENT.DM_TYPE_CODE}<>"SOAD"

The "SOAD Determination" formula field was inserted into the details section under the supplier Group. The filter works and only shows those records which contain SOAD providing the SOAD date and record number  {DM_DOCUMENT.DMSN}  (DMSN is the "exclusive" record number that contains the document). However I am seeing multiple lines in the report with the same record number (duplicate lines).


Did you really mean something like this?

if {DM_DOCUMENT.DM_TYPE_CODE}="SOAD" then
  if {DM_DOCUMENT.DMSN}<>previous({DM_DOCUMENT.DMSN}) then totext({DM_DOCUMENT.DMSN},0,"")


I tried this and it does not work. The report output did not change. I still see multiple occassions of the SOAD records.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

James0628Commented:
I created a formula field "SOAD Determination" using the following code to filter specifically for "SOAD".
Exactly where are you using that formula?  It's not in the record selection formula (which is what is normally meant by "filter"), since you're using <> in that formula.  Are you using it to suppress sections?  Suppressing isn't the same as filtering.  Whether or not that's an issue depends on exactly what you're trying to do (and it may just be me, but I'm still not clear on that).

 If you don't need anything but the "SOAD" records, you should include {DM_DOCUMENT.DM_TYPE_CODE} = "SOAD" in the record selection formula (Report > Selection Formulas > Record).

 "Duplicate" numbers probably means that you have multiple lines with the same number.  Maybe that's normal for your data (like how every line on an invoice will have the same invoice number), or maybe it has something to do with the tables you're using and how they're linked (some records are being duplicated because they have more than one matching record in another table, but you shouldn't be including all of those records).

 It might help if you could post the report.

 James
ThuillierdAuthor Commented:
It might help if you could post the report.
A .pdf of the report output is attached with some details trying to explain what is happening.
I also attached a copy of the actual report.

Exactly where are you using that formula?  
It is being used in a formula field which is inserted into the details section under group 2.

If you don't need anything but the "SOAD" records,
Data for the Project and supplier are the key elements. Some suppliers do not have a SOAD and should still be displayed.

"Duplicate" numbers probably means that you have multiple lines with the same number.  
Not in this case. Each record is a unique number. I am not sure what is causing the duplication.
SOAD.pdf
risk-summary-by-selected-prog-code-Rev-6
mlmccCommented:
Is this what you want?

I added a sort on the SOAD Determination field.  I suspect the suppress if duplicated wasn't working because that looks at only the previous record which in this case might have been already suppressed as NOT a SOAD record.  WHen I added the sort, your suppression made the values disappear.  I then added suppression of the other 2 fields which made the record disappear when you suppress blank sections.

Note - your file name is so long that the extension was dropped.

mlmcc
SOAD-Rev-6-R1.rpt

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ThuillierdAuthor Commented:
I added a sort on the SOAD Determination field.  I suspect the suppress if duplicated wasn't working because that looks at only the previous record which in this case might have been already suppressed as NOT a SOAD record.  WHen I added the sort, your suppression made the values disappear.  


After reviewing your report update, I see how you did this (Record Sort Expert) and it works wonderfully. Thank you.


I then added suppression of the other 2 fields which made the record disappear when you suppress blank sections.


Can you explain how you did this? I am still new to Crystal Reports and was unable to see what you did to suppress the duplicated dates and SOAD in each details section. When I try to "suppress if duplicated" in the Format Feld popup ALL the SOAD (except the first) disappear. In the report update you sent, if here is a second record, It also indicates SOAD, which is what I would want to display.

Again, thank you for the excellent help.
mlmccCommented:
When you format the field I used a formula on the SUPPRESS option.
Click the button to the right of SUPPRESS and you'll see the formula

mlmcc
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.