Skip to Content
author's profile photo Former Member
Former Member

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

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Nov 30, 2007 at 02:02 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2007 at 05:26 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 01, 2007 at 11:51 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.