Step away from the SQL and put your hands where I can see them!

1 minute read time.

Amongst Sage CRM developers there are two almost irresistible urges. The first is the itch to write code and try to solve all problems with "if, else" statements. Those of us who are experienced with Sage CRM will eventually learn that much coding can be avoided and we are gradually weaned of the habit of unnecessary scripting. But becoming familiar with Meta Data and its power within Sage CRM can then release the second dreadful urge and this is the desire to 'fiddle' with the database directly in SQL. But in many cases, this is simply not needed, and it does take some self-discipline to 'just say no', relax and realise the standard interface screens of Sage CRM will do the job for us.

For example, it may be that when defining a custom secondary entity, you have forgotten to fill in a few fields.

I recently covered how to create a secondary entity from scratch using the Create Table option in

Administration -> Advanced Customization -> Tables and Databases

Please see the article "How to Create a Secondary Entity for a new Custom Primary Entity".

But what if you leave a field like the "ID Field Name" blank?

If this field is left blank when the table is accessed by a screen block attempting to save a new record in an ASP page you may get the error message:

"eWare.eWareBlockContainer error '8000ffff' SetIdentityColumn failed"

At this point you might be tempted to carry out a whole set of investigations. For example, you could spot that in the Metadata table "custom_tables", that defines the entities in the system the definition for your new custom entity is missing the data for the ID field (bord_idfield).

Please note that the ID field will have been created:

Note: If you have left the "ID Field Name" blank when creating the table the name that is automatically given this field always follows the pattern XXXX_TableNameID, where XXXX is the prefix you provided for your table.

It is tempting to carry out an update of the data directly using a quick SQL statement.

BUT all you need to do is go back to the Admin screen and complete the missing data.

When this screen is resaved the information in custom_tables is updated and the screen blocks should then work happily.