Sample Query for using in Tranaction Notification SP

[Former Member]

Hi,

Does anybody know how to write a query in Transaction Notification SP to control the value of a field based on a dynamic list of data?

This request is different from using linked table in UDFs definition.

For example, I need to control the "account code" field based on a formatted query list that I set in "Goods Issue" document and I want to block adding document how users can not enter other accounts out of the list.

Please consider that the list is based on a query and it is dynamic and when "IN" function is not usable.

Thank you

[kothandaraman_nagarajan]

Hi,

Try this:

• IF @object_type = '60' and @transaction_type in  ('A')

• Begin

• If exists

• (Select  t0.docentry FROM OIGN T0  INNER JOIN IGN1 T1 ON
  T0.DocEntry = T1.DocEntry

• where t0.docentry = @list_of_cols_val_tab_del

• and T1.[AcctCode] <> '10001-000' )

• Select @error =1,

• @error_message =  'Check account Code '

• End

• End

Note : Add your account code in above SP.

Thanks & Regards,

Nagarajan

[former_member662620]

Hi,

You can use below script

declare @QueryStr nvarchar(max)
select @QueryStr=QString from OUQR where QName = 'name of your defined query'
create table #tempAct(accountCode nvarchar(60))
insert into #tempAct
exec sp_executesql @QueryStr

select * from #tempAct

Then #tempAct contains all account code which is valid. You can do any check based on it.

But your defined query can't any contain parameter , such as [%0] or value from form such as $[$4.0.0]