removing duplicates in formula workshop

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

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

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.

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

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

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.