Unable to add new record in an editable grid. Primary key missing.

Scriptcase 8 Discussion
#1

  • I click the “Add New” button and a new row is created at the bottom of the grid. = Correct behavior

  • I add my data in the fields and click the “Save” button. I get an error saying that a key field is missing.

  • Because this is an editable grid, I’m assuming I need to know the unique identifier of the newly added row, so I’m trying to add it myself.

  • I include the TEAM_GUID field (which is the same for every row in the grid, and OBJ_GUID field (which is the primary key field) in the grid, just so I can see what’s in them.

  • I try to populate the TEAM_GUID field (hard coded for now) and the OBJ_GUD by using a stored procedure (which is tested and works)

  • I put the following code in the onBeforeInsert event.

// SQL to retrieve a new OBJ_GUID

sc_lookup(my_data, “SELECT p.NEW_GUID FROM GET_NEW_GUID p;”);
if ({my_data} === false)
{
echo “Data Access Error: Message=”. {my_data_erro} ;
}
elseif (empty({my_data}))
{
echo “No data returned”;
}
else
{
{OBJ_GUID} = {my_data[0][0]};
}

// Use the global variable for team_guid
{TEAM_GUID} = ‘A4C58C92-62BF-4993-A134-D6FD88B52B80’;

  • Now, when I add a new record, the TEAM_GUID field and the OBJ_GUID field are both blank. This is probably why the DB is rejecting the INSERT, because of a blank TEAM_GUID and blank OBJ_GUID.

How do I populate primary key fields of a new record in an Editable Grid?

#2

Did you check the “DB Value (Insert)” setting?
It’s inside the “Edit Fields” settings.
I guess that if you want to handle it manually, the “DB value (Insert)” should be set to blank for the key field(s).

Just curious: what are the benefits of GUIDs? It seems a lot of work compared to just letting the DB autoincrement an integer ID.

#3

I found the setting you meant. It’s at blank. (It always was).

The code snippet I included is my attempt to assign a GUID to the newly created row. For some reason, the values I’m trying to assign to the newly created row are not being assigned.

Have you ever tried to merge 5 database, with 300 tables and multi-millions of rows in them, where every record was assigned an Integer primary key? I have. I’ve been doing it for decades. Integers are sequential, which means that if you ever want to merge two tables, you’re going to have to re-assign all the PKs of one of the tables. This leaves gaps in the numbering. Pretty soon you’re leaving gigantic gaps, because one of the tables you’re merging CANNOT be reassigned new integer keys because something else outside the database (like file names for example) cannot be changed because some other app depends upon those numbers not changing. It’s a huge mess, perfect for some one who likes to do nothing but renumber for months.

Because GUIDs are universally unique, not just Unique inside a single table, it means that any app on any platform that wants to insert a record, can generate it’s own GUID and in a single INSERT() can insert a new record, and it will always succeed (as long as the other field’s data meets requirements). The app can always know what the primary key is of the newly added record, because the app itself assigned it. This is great for follow-on processing that may need to be done to that record.

I set the OBJ_GUID field to “Defined Value (Insert)”, left the defined value field blank (in Edit Fields settings) and re-ran the app. The new row is not getting the values I’m trying to assign in the code I put in the onBeforeInsert.

I’m still stuck. Thanks in advance for any help you can provide.

#4

I think Defined Value (Insert) should be completely blank, i.e. no options selected.
I guess that setting “Defined Value” and leaving the value field blank would make SC try to write an empty ("" or null) value into the key field.

If no option still don’t work, I’d try either “Calculated by the database if empty” or one the Autoincrement options. E.g. “Auto increment (automatic)” lets you specify the sequence name. Maybe you can specify a DB trigger\procedure that will create\populate the GUID (if you have one)?

Also, did you try to move your code to the onLoadRecord event? I think you don’t need to wait till the onBeforeInsert event and you will be able to see the values before (trying to) saving the record. If you do this, you should first check that the fields you are setting via your code in the SC forms are empty. Otherwise in the existing records you will overwrite them. Fastest way is to just check “if empty({OBJ_GUID})”, as this will be true on new records only.

Another test, could be converting a copy of that form to a single record form and see if it works.
Just to understand if the issue is due to using multple records forms.

Thanks for the info on using GUIDs.

#5

About Integers versus GUIDs… I learnt…from getting burnt. hee hee.