Validation on UDF

[Former Member]

Hello All,

I have a question that can there be any validation available for a UDF, So that once we enter some value in UDF and add the document. after document is added, no user can update UDF not even manager user. Please suggest me.

I had UDF on A/P Invoice UDF is U_InvoicType.

Or

Can there be any other option to block UDF for updation....

Regards,

Bhushan Verma

[former_member203899 · Accepted solution]

Hi,

If you want to give an validation on created UDF then you need to write code in sp_transactionnotification.

it will help you to validate users to modify data.

Thanks & Regards,

Nishit Makadia

[Former Member · Accepted solution]

Hi Nishit,

I had created One TN but not working so i modified and got the solution

Correct TN is

IF @transaction_type IN (N'U') AND  (@Object_type = N'18')

begin

if exists(select T0.docentry from OPCH T0  INNER JOIN OUSR T1 ON T0.UserSign = T1.USERID 

where T0.docentry = @list_of_cols_val_tab_del and

T0.[U_InvoicType] IS NOT NULL )

begin

SET @error = 14

SET @error_message = N'Cannot Update UDF Invoice Type'

end

end

Regards,

Bhushan Verma

[KennedyT21 · Accepted solution]

Hi Bhusan..

I think the above TNSP will allow the user to change the value in the UDF..

Try this

IF @transaction_type IN (N'U') AND  (@Object_type = N'18')

begin

if exists(select T0.docentry from OPCH T0  INNER JOIN OUSR T1 ON T0.UserSign = T1.USERID

where T0.docentry = @list_of_cols_val_tab_del and

T0.[U_InvoicType] <> ( select [U_InvoicType] from opch where docentry=t0.docentry )  )

begin

SET @error = 14

SET @error_message = N'Cannot Update UDF Invoice Type'

end

end

Regards

Kennedy

[Former Member · Accepted solution]

Hi Bhushan,

It might be simplified as:

IF @transaction_type IN (N'U') AND  (@Object_type = N'18')

begin

if exists(select T0.docentry from OPCH T0

where T0.docentry = @list_of_cols_val_tab_del and

T0.[U_InvoicType] IS NOT NULL )

begin

SET @error = 18

SET @error_message = N'UDF Invoice Type Update is not allowed'

end

end

Thanks,

Gordon

[former_member297889]

Hi Bhushan Verma,

I modify it with the table and field that i want to block but my problem is even if I update the other field it also blocks. Below is the script.

IF @transaction_type IN (N'U') AND  (@Object_type = N'15')

begin

if exists(select T0.docentry from ODLN T0

where T0.docentry = @list_of_cols_val_tab_del and

T0.[U_DR_No] IS NOT NULL )

begin

SET @error = 15

SET @error_message = N'UDF Update is not allowed'

end

end

Thanks

Darryl

[Former Member]

Hi,

Try by this:

IF @transaction_type IN (N'A') AND  (@Object_type = N'15')

begin

if exists(select T0.docentry from ODLN T0

where T0.docentry = @list_of_cols_val_tab_del and

T0.[U_DR_No] IS NOT NULL )

begin

SET @error = 15

SET @error_message = N'UDF Update is not allowed'

end

end

I had changed the Transaction type form U to A, U is been used for Update transaction,  and A is been used for Addition of Document

Regards,

Bhushan Verma

[former_member297889]

Hi All,

Does this work for specific field? On my case I have 2 UDF in Delivery and I want to block updating when the specific field's value is change from its original or null.

Thanks

Darryl Gamilong

[Former Member]

Hi Darryl,

Do u want to block both of your UDF to modify. If yes then the above correct answer by Gordon ill work , you need to modify a lil but, just by adding your UDF field which u need to block

Regards,

Bhushan Verma

[kothandaraman_nagarajan]

Hi,

Did you check my query?

Thanks & Regards,

Nagarajan

[Former Member]

Hi Gordon

Thank you for query i had applied that and it works that was quite helpful for me.

Regards,

Bhushan Verma

[kothandaraman_nagarajan]

Hi,

Yes possible by Transaction notification.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan,

I had tried it by using TN i had created the below notification (but not working)

Please correct me if i am wrong

IF @transaction_type IN (N'U') AND  (@Object_type = N'18')

begin

if exists(select T0.docentry from OPCH T0  INNER JOIN OUSR T1 ON T0.UserSign = T1.USERID

where T0.docentry = @list_of_cols_val_tab_del and

T0.[U_InvoicType] = 'F' )

begin

SET @error = 14

SET @error_message = N'Cannot Update UDF Invoice Type'

end

end

Regards

Bhushan Verma

[kothandaraman_nagarajan]

Hi,

Try this:

IF @transaction_type IN (N'U') AND  (@Object_type = N'18')

begin

if exists(SELECT t0.docentry FROM OPCH T0

where T0.docentry = @list_of_cols_val_tab_del and T0.[U_InvoicType] <> ' ' or T0.[U_InvoicType] <> 0 and

T0.[U_InvoicType] in (select T0.[U_InvoicType] from OPCH))

begin

SET @error = 14

SET @error_message = N'Cannot Update UDF Invoice Type'

end

end

Thanks & Regards,

Nagarajan