Skip to Content
0
Former Member
Oct 14, 2005 at 06:50 PM

how to check customer reference no. not being use more than once

590 Views

I have the following situation and appreciate if some one could show me a simple solution.

“On the Sales order window, when user enter a value for Customer Ref. No”, check if the entered value has already been used on any existing Sales Order, and give message eg. “this Ref. No already been used in SO. 257”

What is the easiest way of implementing the validation?

Using Formatted Search?

Triggers or Stored Procedure?

Or one must go about doing this simple task using SDK, DIAPI?

sample code illustrating solution would be much appreciated.

I tried to create a query and used it with Formatted search on the Field, but failed on an error.

the code using temporary hard coded value instead of $[$x.0.0] looks like this

-


if (SELECT COUNT(*)

FROM dbo.ORDR

WHERE (NumAtCard = 'AAA' and CardCode ='1234')

) =0

Begin

select 'AAA' --redisplay value

end

else

begin

SELECT TOP 1 'already used in SO '+ Convert(Char(3),DocEntry)

FROM dbo.ORDR

WHERE (NumAtCard = 'AAA')

end

-


where 'AAA' is the reference no. to check for customer '1234', this code works when I run it in SQL query analyser, but failed when I run it as query in Business one. the idea was to redisplay the entered No. 'AAA' if it is not found in any existing SO for cust ='1234'

can anyone point out what the error is or show alternative code for the query.

thanks