Atomicity while adding records on User Forms using UDO

[brunomilanez]

Dear SAP B1 Developers,

In many forms of our Add-on we have to add records in other user tables and/or system tables, in a trigger like manner.

To accomplish that, we currently use the OnBeforeFormDataAdd event of our user defined forms. However that are cases when we need the DocEntry of the record being added to update a record in another table. In those cases we use the OnAfterFormDataAdd event.

As a result, this way we can't guarantee atomicity. We have tried some solutions:

1. Starting a transaction on the OnBeforeFormDataAdd event and ending it on the OnAfterFormDataAdd event.
   • Issue: as part of the process on OnBeforeFormDataAdd event creates a record in some table, the ONNM table gets locked, so the record being added fails on insert phase (Between OnBeforeFormDataAdd and OnAfterFormDataAdd events)
2. Doing all the process on the OnBeforeFormDataAdd event and setting bubble event to false:
   • Issue: It works fine. We add the record of the form through DIAPI. The only thing is that SAP B1 returns a message indicating that the process has been canceled by the user, so we set another message (green one) signaling that the process has succeeded. Not good to see.
3. Using UDO DLL implementation:

• Issue: we have started our tests with this solution. Our idea is similar to the previous one, but with the proper message at the end of the process. For this, we thought about removing the line that does the insert in the OnAdd() method of the dll and only return 0 to indicate the process have completed successfully. Hence all the implementation would be done during the OnBeforeFormDataAdd event.

What do you guys think? Any suggestion?

Thanks!

[YatseaLi]

Hi Bruno,

If you would like to have UDO record and other operation in a transaction.

May you try this:

OnBeforeFormDataAdd

Company.StartTransaction()

'B1 Add UDO record

OnAfterFormDataAdd

'Firstly, how to obtain the UDO entry added just now

'Two ways to obtain the UDO entry added just now

'Option 1:

Company.GetNewObjectCode(udoDocEntry)

'Option 2: pVal as FormDataEvent.

' Important, this is a xml section rather than a real, you will need to parse the xml with Xml.XmlDocument to get the Key value just added

BusinessObjectInfo.ObjectKey

XmlDocument.LoadXml(BusinessObjectInfo.ObjectKey).SelectSingleNote("//DocEntry").InnerText

'Secondly, do other DI operation with the given udoDocEntry above.

'Finally, commit the transaction if all operation succeed.

If BusinessObjectInfo.ActionSuccess and Other DI operation success, then

     'Commit the transation

     Company.EndTransation(et_Commit)

End if

Kind Regards, Yatsea

[brunomilanez]

Thanks for replying Yatsea,

I did some tests based on your suggestions. Did not solve the issue. Here is a list of the tests:

Scenario 1: Starting a transaction at the beginning of the OnBeforeFormDataAdd and returning the method, setting BubbleEvent to true. During the OnAfterFormDataAdd using the DIAPI to add a record into another user table and finally committing the transaction.

    Result: The process gets stuck during B1 Add UDO Record part and not even reaches the OnAfterFormDataAdd event handler. User tables involved as well as system tables like ONNM and CHEN remains locked at database level.

Scenario 2: Starting a transaction at the beginning of the OnBeforeFormDataAdd, creating a record into another user table through the DIAPI and committing the transaction before returning the method.

    Result: Success. However it does not solve our problem since it does not guarantee the atomicity we are after.

Tests were made on SAP Business One 8.82 PL16 and SAP Business One 9.0 PL13, both running on SQL Server 2008 R2

Here is the Query we are using to monitor the locked tables

select dm_tran_locks.request_session_id,

      dm_tran_locks.resource_database_id,

      db_name(dm_tran_locks.resource_database_id) as dbname,

      case

          when resource_type = 'object' then object_name(dm_tran_locks.resource_associated_entity_id)

          else object_name(partitions.object_id)

      end as objectname,

      partitions.index_id,

      indexes.name as index_name,

      dm_tran_locks.resource_type,

      dm_tran_locks.resource_description,

      dm_tran_locks.resource_associated_entity_id,

      dm_tran_locks.request_mode,

      dm_tran_locks.request_status

from sys.dm_tran_locks

left join sys.partitions on partitions.hobt_id = dm_tran_locks.resource_associated_entity_id

join sys.indexes on indexes.object_id = partitions.object_id and indexes.index_id = partitions.index_id

where resource_associated_entity_id > 0

  and resource_database_id = db_id()

order by request_session_id, resource_associated_entity_id

About your tip on how to obtain the UDO entry just added (during OnAfterFormDataAdd), only the option 2 worked out.

Am I missing something?

P.S.: I could not attach my test project, unfortunately. I'll send you by e-mail.

Best regards,

Bruno