Find duplicates within found set

[Oyseka]

Hi All I am trying to find if there are any duplicate serials within a quote but I just find ever line item and not the expected error 401. Any help appreciated.

This is the find criteria. QuoteId = $QuoteId AND QitemSerial = !

The second screen is the Find result

[comment]

QitemSerial = ! does not find duplicates in the found set. It finds duplicates within the entire table. If you have enough quotes, then eventually all your items will be duplicates using this criterion (at least II think so; I don't really understand what a QitemSerial is).

If you want to know if a quote has duplicate items, you could compare - from the context of the quote:

Count ( QouteItems::QuoteID ) = ValueCount ( UniqueValues ( List ( QouteItems::QitemSerial ) ) )

If you want to identify them in some way, then there is more work to be done - and I would start by asking for what purpose.

 

[Oyseka]

2 hours ago, comment said:

If you want to identify them in some way, then there is more work to be done - and I would start by asking for what purpose.

Hi comment, yes I do want to identify them to either delete or replace the duplicates, it depends on circumstances that led to their creation. There are not supposed to be any duplicates in an individual quote but as the quotes are duplicated for each monthly service the QItemSerial is also duplicated. The QitemSerial is used to produce the barcode that is read into the handheld device during the service. An error on my part last year led to them being able to be duplicated so I now have to identify and rectify. With about half a million Quote Lines I am trying to script my identifying which Quotes have duplicates and isolating those duplicates.

[comment]

Is this a one-time operation to correct legacy data? If so, I would suggest you define a calculation field in the QuoteItems table = 

QuoteID & "|" & QitemSerial

Then find duplicates in this field. If you sort the results by QuoteID, you may process each group separately. Or, if you prefer, you can go the way you have started, just using the calculation field ito identify duplicates nstead of QitemSerial.

Of course, if there are not supposed to be any duplicates then the best way is to prevent them. This could be done by changing the calculation field to a Text field  auto-entering the calculated value and validating it as Unique. 

 

[Oyseka]

5 minutes ago, comment said:

Is this a one-time operation to correct legacy data? If so, I would suggest you define a calculation field in the QuoteItems table

Yes, it is a one time legacy data correction.Thank you for the the lesson, this will do it.

The issue with validating the field as unique is that every month a new record is created with the same QItemSerial, it is only unique in that quote.

[comment]

5 minutes ago, Oyseka said:

The issue with validating the field as unique is that every month a new record is created with the same QItemSerial, it is only unique in that quote.

I am not sure I understand this. The validation will fail only when the QItemSerial value already exists in the same quote. Note that I am talking about validating the new field, not the existing QItemSerial field.

[Oyseka]

Really ! I thought that if a field is specified as unique that no matter where in the table a new record is created, validation would fail if that field held the same data as another record.

[comment]

5 minutes ago, Oyseka said:

if a field is specified as unique that no matter where in the table a new record is created, validation would fail if that field held the same data as another record.

That is correct. But the field is constructed in such way that if a duplicate is detected, it must be in the same quote - because the value is concatenation of QuoteID and QItemSerial. If the duplicate belongs to another quote, then you have a duplicate QuoteID - and that would mean a much bigger problem than the one we're dealing with here.

 

[Oyseka]

6 hours ago, comment said:

because the value is concatenation of QuoteID and QItemSerial

My apologies, I thought that you meant to make the existing QItemSerial validated as unique.

[bcooney]

I’m curious, what is qitemserial? Is it the line number of a quote line?

[Oyseka]

4 minutes ago, bcooney said:

I’m curious, what is qitemserial? Is it the line number of a quote line?

Hi, I have seen your handle on very many posts.

No. It is an identifier of a complex product against which parts are used during a service and is part of the barcode that is scanned by the operator when servicing.This allows the operator to pull a BoM in the service and only supply parts that fit the product. The reason why I specify complex is that a product may be sold on a quote which accept no additional parts and therefore are not allocated a QItemSerial as no service can be carried out on it.

[bcooney]

Thank you for the explanation.