Master-detail insert/update

Use the Add Page, After Record Added custom event, I assume the field 'contactID1' is to be filled in the master table?

so, omit this field fromm the Add page, use the AfterAdd event to reference the record values (using $values["fieldname"] ) and write either a add record to master table sql string or and Update string for the master table..
If you are stuck, give me some more details of the tables and the query for the add page and I will play around with sql for you.

Hi Thesofa, you're so kind <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=34091&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
I think it's better if I try myself to code, it's the best way to progress, I guess <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=34091&image=2&table=forumreplies' class='bbc_emoticon' alt=':)' /> but your help is precious for me, regardind method to work with PHPR.
What I have :

• one table 'contact' : Name, adress, etc...
  
• One table 'appointment' (date, place, etc...)
  One contact can have several appointments. I want to be able to create simultaneously one new contact and it's first appointment.
  So I would like to have one sole form, in wich I input all informations I need, and have some of this informations inserted in 'contact' and others in 'appointment' :
  name adress of new contact.
  
  date and place of first meeting.
  So :
  

  so, omit this field fromm the Add page
  

what do you intend with 'omit' ? do not show the field in the add page form ? but I need this field to input datas. After, I understand the method, I guess, but 2 difficulties for me :
1 - how to avoid 'appointment fieds' to be taken in the sql insert query provided by PHPR.

2 - How to recover the id of the new record inserted by PHPR, to use it as foreign key in the the second query I have to build ?
The first point is the most dificult for me. But if you've got the soluce for the second, it's nice too <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=34091&image=3&table=forumreplies' class='bbc_emoticon' alt=':)' />
Many thanks for your help.

Hi,
PHPRunner doesn't support updating multiple tables on one page.

You can implement this manually using Before record updated/added events.
I.e. use something like this:

global $conn;

Hi Jane,
Well, if I understand : The complete set of values is in an array $vales(). $vales[recordID] contain the value of the field 'recordID' in the form to add.
So I've to build my insert (or update) query with these values, and, once done, to delete (unset) each of these values from the array, to avoid error message in the next insert query.
do I well understand ?
thanks for your help.

do I well understand ?

Yes.

Hi,
here is the complete soluce, if somebody is interested :
Event before add

And after record added event :

In this exemple, there are only to fields (nom-> alias and nom1, prenom-> no alias) to insert.
One thing to to, I believe, should be to have an php page (say myalias.php), in which we store all couple field->alias for each table. this page shoud be included in the event. So, when we add /update fields in the 'edit sql query' of the project, we should only have to update this page, to see all the events correctly working.
Is there at one place in PHPR any file about relationship fields->alias ?

Hi,

And after record added event :

In this exemple, there are only to fields (nom-> alias and nom1, prenom-> no alias) to insert.
One thing to to, I believe, should be to have an php page (say myalias.php), in which we store all couple field->alias for each table. this page shoud be included in the event. So, when we add /update fields in the 'edit sql query' of the project, we should only have to update this page, to see all the events correctly working.
Is there at one place in PHPR any file about relationship fields->alias ?

Hi frphilip
I am interested in doing exact same thing. Two tables "Client" & "Appointment". When a new Client is added an Appointment is added as well. However cannot get your example code to work. Fails on the line "$champs['Field'];"

Where should the $champs variable be declared? and how?
Many thanks

darkwa

Hi Darkwa,
Nice, it seems that we are working on the same kind of project, a contact management <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=34588&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
Well, what is your issue ? is there any error message ?
$champs is the array in which I stored all datas from the sql query SHOW COLUMNS FROM contact.
And 'Fields' is the column containing the names of all the fields of the 2nd table.
If you want, the solution in my post is built in a 'test' project, with only 3 tables, only for testing. I can send it to you if it may help.
++

Hi Darkwa,

Thanks for replying so quickly. My code below (champs = fields non?):

// Add page: Before process

function BeforeProcessAdd(&$conn)

{

//** Insert a record into another table ****

global $conn, $values_Appt;
// build an array with all the name of the fields or alias used in 'edit sql queries' and their real name in the mysql table

//$fields['idClientAppt']='field or alias in the form';

$fields_alias=array();

$fields_alias['idClientInfo']='idClientAppt';

//$champs_alias['prenom']='prenom';
// get all the fields of the table (here, contact)

$sql = 'SHOW COLUMNS FROM ClientAppt';

$res=mysql_query($sql);

while ($fields_alias=mysql_fetch_array($res))

{

$idClientInfo_TableField=$fields['idClientAppt']; // get the name of the field in the table $idClientInfo_FormField=$fields_alias[$idClientAppt_TableField];// get the name of the field in the form

if(!empty($values[$idClientAppt_FormField])) // control if this field is filled in the form (to avoid inserting an empty record in the child table

{

$values_Info[$idClientInfo_TableField]=$values[$idClientAppt_FormField];// update list of existing values to insert

}

// delete values in the values array, to avoid error messages.

unset($values[$idClientAppt_FormField]);

}
return true;
} // function BeforeProcessAdd
// After record added

function AfterAdd(&$values,&$keys,$inline)

{

// Parameters:

// $values - Array object.

// Each field on the Add form is represented as a 'Field name'-'Field value' pair

// $keys - Array object with added record key column values
//** Insert a record into another table ****

global $conn, $values_Appt;

$test=sizeof($values_Appt);

if ($test>1)// we only create record if there are datas to insert, to avoid empty records.

{

$strSQLInsert = "insert into ClientAppt (idClientAppt) values ('".$values_Info["idClientAppt"]."')";

db_exec($strSQLInsert,$conn);

}
} // function AfterAdd
The code fails at the line $idClientInfo_TableField=$fields['idClientAppt']; // get the name of the field in the table[ which is in bol above, and the erro message states that $fileds is not declared.
I would be grateful to receive the code for the test project. Every little helps. Many thanks once again. Grand merci a vous.
dark'wa

Hi,
try to edit your code in the following way (see my changes in Bold):

...

Hi,

Thanks Jane

I changed the code as you suggested - does not work. Debugging in PHPEd the $fields_alias variable fills with the table field values alright. But the "$idClientInfo_TableField" ($nom_champBd in frphilip's original code), "$idClientInfo_FormField" ($nom_champForm in frphilip's original code), and "$values_Info" ($values_FP in frphilip's code snippet) do not fiill with any values and remain NULL stepping through the code in the debugger. Anyway thanks for the prompt response. I do hope that frphilip will share his 'test' project so that I could find the right solution. I need to automaticallly add a new recordset to the Appointments table (detail) whenever a new client is added to the ClientTable (master) using ClientID as foreign key in the detail table. Many thanks, and any help welcome.
darkwa

Hi Darkwa
No problem to share my test project, but... how to do it ?
Last point. Seems you speak french, isn't it ? moi aussi <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=34671&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
Peut être plus simple de continuer en français, pour expliquer. Pas super pour le forum, mais bon. en anglais, je suis un peu juste.

darkwa,
It's difficult to tell you what's happening without seeing actual files.
Please publish your project on Demo Account and send to support@xlinesoft.com a URL to your pages along with instructions on reproducing this error.

I'll find what's wrong with your project inspecting it at Demo account site.