Formatted Search - Duplicate

[bbranco]

Hi,
I have a customer who wants to check for duplicate NumAtCard after the reference number is put in and not wait for the order to be saved.

I added a UDF and this formatted search, but when I click on the magnifying glass, it puts the word Duplicate in there.

I am not sure what I am doing wrong, can someone help?

SELECT 'Duplicate'

FROM ORDR T0 WHERE T0."NumAtCard"

IN (SELECT T1."NumAtCard" from ORDR T1

WHERE T1."NumAtCard" = $[$14.0.1] AND T0."CardCode" = $[$4.0.1])

[kothandaraman_nagarajan]

Hi,

Have you tried with standard function for duplicated customer reference under Document Settings--Per Document--Sales order?

Regards,

Nagarajan

[bbranco]

Thanks,

Thats when they save it after everything is entered. They wanted something to come up after entering in the BP and reference number.

I got it worked out

[bbranco]

This is what I came up with and seemed to work...

SELECT 'Duplicate'

FROM ORDR T0 WHERE T0."NumAtCard"

IN (SELECT T1."NumAtCard" from ORDR T1

WHERE T1."NumAtCard" = $[$14.0.1] AND T0."CardCode" = $[$4.0.1] AND T0."DocNum" <> $[$8.0.1] )

[SonTran]

Hi,

Try this query

SELECT 'Duplicate'
FROM ORDR T0 WHERE IFNULL(T0."NumAtCard",'')<>'' 
AND T0."NumAtCard" IN (SELECT T1."NumAtCard" FROM ORDR T1 
			WHERE T1."NumAtCard" = $[$14.0.0] AND T1."CardCode" = $[$4.0.0]
			)

Hope this helps,

Son Tran

[bbranco]

Johan,

The problem actually seems if I edit an order, it fills in duplicate because its seeing the data in the table already.

Do you know how I can exclude the current order in the formatted search?

SELECT 'Duplicate'

FROM ORDR T0
WHERE (select COUNT(*) from ORDR T1
       where T1."NumAtCard" = $[$14.0.1] and T1."CardCode" = $[$4.0.1]) > 1

Maybe something like T1.DocNum <> $[$8.0.1]

Brian

[Johan_Hakkesteegt]

Hi Brian,

You get a false positive because the document that you are creating is also in the database temporarily. It is made permanent when you add the document, and it is discarded if you do not add the document, and close the form.

So your query will find the entered NumAtCard as soon as you write it.

Please try this query:

SELECT 'Duplicate'
FROM ORDR T0
WHERE (select COUNT(*) from ORDR T1
       where T1."NumAtCard" = $[$14.0.1] and T1."CardCode" = $[$4.0.1]) > 1

Remember to add both the NumAtCard and CardCode fields as triggers.

Regards,

Johan

[Johan_Hakkesteegt]

Hi bbranco,

"The problem actually seems if I edit an order,"

By using COUNT(*) > 1 you should catch both adding and editing existing documents. Adding adds a temporary record, but to my knowledge, editing an existing document does not. Do you mean that when you test with my query, you get 'Duplicate' when you edit a document?

At any rate, adding "T1.DocNum <> $[$8.0.1]" should also work.

Regards,

Johan

[bbranco]

Hi,

I just want the word Duplicate, whats happening is its showing the word Duplicate even though its in ORDR only once.

[gonzalogomez]

SELECT 'Duplicate'

If in the select it is duplicate, then that returns... Duplicate...

if you want the number... t0.NumAtCard.... but has no control

maybe concatenate 'Duplicate'+T0.NumAtCard