Skip to Content
Author's profile photo Former Member

How to: Add a user field to an existing table via SQL

Here is “How to Add a user field to an existing table”

Sometimes it can be useful to add a userfield to a table without going through DIAPI or the SBO Client.
Both require all users to be disconnected from the database in the meantime.
While the following SQL provides an alternative, keep in mind that SAP created the logic for a reason.

Attention: The following statements will bypass SAP Business One Logic on purpose.
Please be sure that you know what you are doing.

The following code adds the userdefined field U_SAMPLE of SAP boolean type (Y/N) to the BusinessPartner Form

See for yourself and please try this against a sample database first

Lutz Morrien

/* Add the field to the table */
Alter table OCRD add U_SAMPLE varchar(1) null

/* add the userfield to the tracking table (FieldId might be a problem) */
/* field id is usually highest existing field id in a table increased by one */
/* choose an unlikely value like 200 */
INSERT INTO CUFD (“TableID”,”FieldID”,”AliasID”,”Descr”,”TypeID”,”EditType”,”SizeID”,”EditSize”,”NotNull”,”IndexID”)
VALUES (‘OCRD’, 200,’SAMPLE’,’Beispiel’,’A’,’ ‘,1,1,’N’,’N’)

/* create list of values */
INSERT INTO UFD1 (“TableID”,”FieldID”,”IndexID”,”FldValue”,”Descr”)
Values (‘OCRD’,200,1,’Y’,’Ja’) GO

/*Set default value for User Field to ‘Y’ */
Update OCRD set U_SAMPLE = ‘Y’ WHERE U_SAMPLE is null

Assigned tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      As a SAP Business One consultant, I've heard over and over that it is only allowed to *read* the SBO database directly with ODBC/SQL, but that it is not allowed to write directly to the database. Otherwise - they say - the installation which has been stabbed with SQL is no longer supported by SAP.

      However, it seems that even SAP does not follow this rule consistently. This article is but one of several articles SAP which suggest stabbing the database directly with SQL. Other examples include instructions for copying reports and document templates from one database to another etc.

      It would be really nice to get some kind of a statement about what is and what isn't allowed regarding SBO databases and SQL.

      Henry Nordstrom

      Author's profile photo Former Member
      Former Member
      Using manual update statements via Query Analyzer or otherwise is not allowed as this would violate the integrity of the database. The only update statements that are allowed are ones that have been specifically released for customers in a note with strict instructions (e.g. backup and running the query. )

      Other update queries are of course as a result of an internal case-by-case basis where the customer's individual database will have been analyzed firstly by support consultants and subsequently by Development support, and the updates are ran in a WebEx with liason from SAP side. These queries are first written by Development Support and tested in a backup of the customer database.

      It is important to note that a customer will lose support by updating the database tables himself, as entries into the tables can have deep repurcussions into the behaviour of the system and any anomalies introduced into the system would not be supported by the Development team in Israel.

      It is equally important to note that Triggers containing statements such as SET and UPDATE are also not supported. 

      This may also include partner add-ons that are not SAP certified containing these type of statements - any anomalies introduced as a result of these triggers would also put the onus to solve this type of coding problem on the partner add-on side.

      Kind Regards,
      Maria Marchal
      Business One Support

      Author's profile photo Former Member
      Former Member
      Blog Post Author
      Note that at the time this was written, SBO developers were pretty much struggling to get anything to work.

      This is a workaround which, at the time given, got it's job done.

      Sometimes, there was not the time left to wait for official SAP support.

      Lutz Morrien