How to manage data in a custom table added in SBO?

[Former Member]

Hello,

I'm developing a addon for one client that needs to control an aditional step, after each Invoice is posted.

Since SBO does not allow me to add additional fields to Invoice header table (OINV), I thought to add a custom table to SBO database to control the process for each Invoice (simple table with 2 columns: DocNum to hold the Invoice number, and Status to keep track if the Invoice was processed or not).

When I added a Custom Table in SBO and checked the table definition in SQL Manager, I found that SBO added automatically 2 new columns (Code and Name).

Since the PK is defined at Code column (and not the DocNum has I wanted), I found that it not very handy to manage data (insert, delete & update) with this table definition.

I tried to find a DI object that could manage easily the data of this table type but had no sucess.

Then I thought in adding the custom table directly in SQL Manager, and manage it directly with Recordet objects.

However this seems not a standard process to manage data since it interacts directly with the DBMS, which can be dangerous...

Is there any way or DI object to manage this custom tables added in SBO?

Thanks all!

Manuel Dias

[Former Member]

Hello Owen and Vishnu,

Thanks for your tips! I didn't know that I could manage custom data with the UserTable.

The object is quite smart since I've made some tests and, for instance, during an

UPDATE it only updates the columns that I set! Very nice!

However, I find a limitation of this user tables: during the creation of a user table in SBO,

the system automatically adds to columns, Code and Name. Code is the PK and

it only contains nvarchar(8), which is very small for some user objects.

For instance if I want to create a user table where the PK is a nvarchar(20), I cannot

due to Code PK limitations....

I was wondering: is it possible to change the Code column size after the table is

created, and then set the Code property of UserTable with any value I want?

Probably SBO checks for its default size (8) and returns a error if it exceeds the

8 chars....

Is there any workaround for this limitation?

Regards,

Manuel Dias

[Former Member]

Hi Manuel,

What do you mean by SBO does not allow me to add additional fields to Invoice header table (OINV).

Have you tried it add a UDF to OINV,if so what is the exception you got while adding.

If you Have not tried Use the following code to create.

Dim oUserFieldMD As SAPbobsCOM.UserFieldsMD

oUserFieldMD = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserFields)

If (Not isColumnExist(TableName, ColumnName)) Then

oUserFieldMD.TableName = "OINV"

oUserFieldMD.Name = ColumnName

oUserFieldMD.Description = Description

oUserFieldMD.Type = SAPbobsCOM.BoFieldTypes.db_Alpha

oUserFieldMD.EditSize = Size

(objUserFieldMD.Add() )

End If

if the field is added successfully.

Then open the Invoice screen and goto View menu and click on user defined fields then it will show you one form which contains the field you added,

You can access this field using DIAPI of Invoice object

regards

Vishnu

[former_member201110]

Hi Manuel,

What is the reason why you are unable to add additional fields to OINV? Have you run out of space on that table?

The DI API has objects that allow you to create user-defined tables and to manipulate the data in them (as long as you are creating non-object UDTs). The objects you would need are UserTableMD (for creating the table through code) and UserTable for accessing the data.

Kind Regards,

Owen