Update Hidden Field Before Saving

Applications Forms
,
#1

So I have a Single Record Edit/Save Form

And I’m trying to update a hidden field before it inserts into the database

I have a set of fields that, for each License #, has a Line Items #s field. These fields are created in the script case application as they’re not database tables. In the database, I have 2 fields one for license # and one for line#. So the issue I’m having is I’m trying to take the values from each Lic# and Line# and update the hidden field so when it saves in the database it will concat all lic# to one field and then concat all line# to the single field.

Form Example
Lic 1: _______________ Line 1:__________________
Lic 2: _______________ Line 2:__________________
Lic 3: _______________ Line 3:__________________

I’ve tried to put the change in onBeforeInsert, but my application just throws a 500.

I’m new to SciptCase as well. So greatly appreciation any insight or help.

#2

I think you need to do that OnAfterInsert.
Put your code here so we can see it… the 500 error is a weird one to get…

#3

Very simply nothing crazy just yet just trying
{IT_LIC_NUMBERS} = {IT_LIC_NUMBER1} + “,” + {IT_LIC_NUMBER2};
{IT_LINE_NUMBERS} = {IT_LIC_LINE1} + “,” + {IT_LICE_LINE2};

#4

So if you have FIELD1 and FIELD2 and you want “FIELD1,FIELD2” as your final you don’t use a +. It would be {NEWFIELD}={FIELD1}.",".{FIELD2}

There are other ways to do it depending what you want. You want this BEFORE inserting in database? It would have to be in a beforeinsert event. Or you could go in afterinsert and do an update on the database to set it.

3 Likes
#6

@mollyshark Thanks that worked everything is working as expected now.

2 Likes
#7

So there seems to be an issue, I have debugged turned on, and my values get written correctly to the hidden variable, as I can see when I debug turned on, but my database is not being updated. I even took the output of the update script and ran it manually, and it worked fine if run directly on the SQL server. But when I let the application handle it, nothing happens database is not updated and the onAfterUpdate script never fires either. No Errors are given either.

$LicenseNumbers = array();
$LineNumbers = array();

if ({IT_LIC_NUMBER1} != “”) {
array_push($LicenseNumbers, {IT_LIC_NUMBER1});
}

if ({IT_LIC_LINE1} != “”) {
array_push($LineNumbers, {IT_LIC_LINE1});
}

if ({IT_LIC_NUMBER2} != “”) {
array_push($LicenseNumbers, {IT_LIC_NUMBER2});
}

if ({IT_LIC_LINE2} != “”) {
array_push($LineNumbers, {IT_LIC_LINE2});
}

{IT_LIC_NUMBERS} = implode(’,’, $LicenseNumbers);
{IT_LINE_NUMBERS} = implode(’,’, $LineNumbers);

Here is the output of the application provide when I have to debug turned on:

UPDATE dbo.NIIC_BTI_ProjectOrderDetail SET IT_LIC_NUMBERS = ‘5555,5555’,IT_LINE_NUMBERS = ‘5555,5555’ WHERE Plan_id = 1374

#8

Are you doing an sc_redir after this code by any chance?
if so, you have to put sc_commit_trans() before the sc_redir();

#9

Assuming Innodb tables (in mysql). If using MyIsam there are no transactions.

#10

Is your update statement wrapped in sc_exec_sql(“update stmt”)? Are your it_lic_numbers character or numeric fields? If numeric, I’m concerned on those commas in ‘5555,5555’.

#11

Hi @mollyshark and @nwdbs, I’m not doing any commands after the code below. I got the script from the output I get when I turn on Debugging on the application and go to save. It shows me all the SQL statements that I believe should be running, and they pop up in an output window. I’m using MSSQL, not MySQL, and the column is Varchar.

So do I see to run the command after the last line of my code where I assign the values to the 2 fields? this is being done in the in beforeinsert and beforeupdate

I will also like to add removed all BeforeUpdate and just making the changes to the database fields that are no longer hidden and still doesn’t write to database but output a update statement correctly.

$LicenseNumbers = array();
$LineNumbers = array();

if ({IT_LIC_NUMBER1} != “”) {
array_push($LicenseNumbers, {IT_LIC_NUMBER1});
}

if ({IT_LIC_LINE1} != “”) {
array_push($LineNumbers, {IT_LIC_LINE1});
}

if ({IT_LIC_NUMBER2} != “”) {
array_push($LicenseNumbers, {IT_LIC_NUMBER2});
}

if ({IT_LIC_LINE2} != “”) {
array_push($LineNumbers, {IT_LIC_LINE2});
}

{IT_LIC_NUMBERS} = implode(’,’, $LicenseNumbers);
{IT_LINE_NUMBERS} = implode(’,’, $LineNumbers);

#12

I’ve resolved the issue, it was due to an AfterUpdate event that was already there that didn’t have sc_commit_trans()

1 Like