Local variable from an event

Applications Forms
#1

I have this PHP function running during an onBeforeInsert evet

$v_subjectid = {subject};
$v_contactID = {contactID};

$sql_insert_ticket = “INSERT INTO tickets(subject
,contactID
,customerID )
VALUES( ‘$v_subjectid’,
‘$v_contactID’,
(select customerID from asinvdb.customerContacts where contactID = ‘$v_contactID’)
)”;

sc_exec_sql($sql_insert_ticket);

This runs fine and adds the data to the tickets table.

I am then trying to query the tickets table for the ticketID for the recently created record and pass that value back to a local variable/field called {idticket}.
I am running into foreign key issues as the form is using a the ticketDetail table and the idticket field has a foreign key setup to the idTicket from the tickets table. If I run the below command on teh database manually (outside of scriptcase) it runs fine.

INSERT INTO asinvdb.ticketDetail (idticket, staffID, contactID, ticketDetailDate, ticketContent, ticketAssignStatus)
Values ((select idTicket from asinvdb.tickets where subject = ‘radio’),2, 98, null, ‘broken’, 1)

Where and how is the best place to set the value for this forms local {idticket} variable?

Thanks,

#2

Hello,
is this exemple is correct ?
i see
quote ++

INSERT INTO asinvdb.ticketDetail (idticket, staffID, contactID, ticketDetailDate, ticketContent, ticketAssignStatus)
Values ((select idTicket from asinvdb.tickets where subject = ‘radio’),2, 98, null, ‘broken’, 1)
unquote ++
but the point is maybe not good,
try
INSERT INTO asinvdb(…,…) VALUES (…,…)

#3

Good morning,

The SQL commands and code work fine. I am trying to figure out how set the value of the foreign key field {idticket} so that it will properly update the detail table with the newly created ticket ID

The form currently gives me this error: Error inserting:

Cannot add or update a child row: a foreign key constraint fails (asinvdb.ticketdetail, CONSTRAINT mainTicketID FOREIGN KEY (idticket) REFERENCES tickets (idTicket) ON DELETE CASCADE ON UPDATE CASCADE)

The SQL behind it is this:
INSERT INTO ticketDetail (idticket, staffID, contactID, ticketDetailDate, ticketContent, ticketAssignStatus) VALUES (0, 1, 89, null, ‘wednesday’, 1)

As you can see the first value for idticket is zero. I need to set the field in this form {idticket} so that it can properly be inserted but I am not sure where/how best to accomplish this.

Thanks,

Keith

#4

I ended up put this in the pho method and it seems to work but I am not sure that this is the best way to accomplish it.

$check_sql = “SELECT idTicket”
. " FROM tickets"
. " WHERE subject = ‘" . {subject} . "’";
sc_lookup(rs, $check_sql);

if (isset({rs[0][0]})) // Row found
{
{idticket} = {rs[0][0]};
}