Buy Now
Sign in

In a DB Save, I'm unable to add the key of the record in the first save as a foreign key in the second.

farscape , January 31 at 02:01
Answered
F
farscape

I am able to save data to a table with the DB Save Action, and am trying to follow the instructions from this 2014 post. The primary response in the post that I am referencing is:

The value of the inserted record primary key will be added to the data array after the db save, which in your case I believe is 1, 
then you can use a new db save, but you should copy the value to a new field matching the foreign key's name in the 2nd table, and
if the 2nd table's pkey is also "id" then you must unset the id value in the data array:

<?php
$form->data["2nd_table_fkey"] = $form->data["id"]; //assuming pkey in 1st table is "id"
unset($form->data["id"]); //assuming pkey in 2nd table is also "id", and so we need to remove this value to avoid an update

The code above should be inside a "custom code" action before the 2nd db save.

Regards,
Max

In my case, the line is

<?php $form->data[fk_tid] = $form->data[tid]; ?>

tid is the primary key of table 1, and fk_tid is the foreign key in table 2. In the debugger,

In the second database save, I added the following to the Data Override on Insert

tid:{data:fk_tid}

I see in the debugger that tid in the primary table is being created, as expected.

I have placed the custom code action between the two Database Save actions, but the debugger shows that tid is blank, and that the insert is saving a null value to the second table.

    [save_data8] => Array
(
[data] => Array
(
[email] => a@bc.com
[created] => 2018-01-31 01:39:56
[user_id] => 204
[tid] =>
)

I have also tried placing the custom code action in the Success block of the first action, and at the top of the body of the second action, but the result is the same.

The post I am using is from CF5. Is there a different approach in CF6, or have I made a mistake?

Thanks!

Bill

P
Pigna

Hi Bill,

i've spent the last hours on the same problem (in a multiplier loop save). The solution, with your variables should is​

fk_tid:{var:save_dataN.tid}

"save_dataN" where (N is a number) is the name in the black label of the "Save Data" for the first table.

Pigna​

F
farscape

I've tried several things since the above post. No luck, but some clues:

Even when I save a literal in the php, as below, the fk_tid in the second database is still blank.

<?php $form->data[fk_tid] = 200; ?>

If I add some php to the custom code action to show the value of fk_tid, as follows, <?php $form->data[fk_tid] = 200; ?>

<?php $form->data[fk_tid]= 200; ?> <h3 class="ui header green">Check: <?php echo $form->data[fk_tid] ?></h3>

it does display the value 200 in the echo statement, but the debugger shows that in the second save action, fk_tid is still null.

AND, if in the second database action, I do this in the Data Override on Insert:

created:{date:Y-m-d H:i:s}
user_id:{user:id}
fk_tid:{data:field_a}

where field_a is a form field saved in the first database save, then fk_tid DOES capture the value of that field.

Does anyone know what I am doing wrong or leaving out?​
​​
​Thanks!​
GreyHead

Hi farscape,

I don't think that $form->data will work in the way you want in CFv6 - the code changed from earlier versions. Please see the foot of page 34 in the manual for the CFv6 code.

Bob

ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
F
farscape

Pigna - Apologies, I did not see your response until now. Evidentally, you sent it two minutes before I had added an update, so I missed it. Thanks! That worked! Whew.