Insert Record in other table when select from "select field"

[Fakiro82]

Hi,
how can I do to insert record in other table after that I select value in "select field" ?

Thank you,
Andrea.

[kirondedshem]

If you mean you have a table which has a field for which you had used a select controll in feild settings, and you want after a record is inserted into the table using add form, you would like to get the value that was put the the feild which has you select controll.

Then you can get the values of the row as picked from the form before and after they are inserted into the database table using the following events
Row_Inserting($rsold, &$rsnew) "Before the row is inserted into database table"
Row_Inserted($rsold, &$rsnew) "After the row has been inserted into the table"

where $rsnew is an array of the row that was inserted into the table. forexample, to get the values of age field from that table after it has inserted.
function Row_Inserted($rsold, &$rsnew) {
//access the age value from the array
$my_age_value = &$rsnew["age"];
}

NOTE:Read up on "Server Events and Client Scripts" to understand the dynamic of the events

[Fakiro82]

Hi kirondedshem,
I need to generate other record in other table when select value from "select field" (the select field is in another table).
Example:

1st Table
Id
Select Field --> when i select value

2nd Table
New record --> I need to generate record with some value

I hope to be explained,
sorry for my little english.

Thanks,
Andrea.

[kirondedshem]

what am getting is that when a user is on like the add form of table 1, which has a slect filed,
when they select a value in that filed(even if they have not submitted the form yet) you wan t automatically insert a record in another table somewhere.
If so then you need to attach an ajax call onto the onchange event of the field such taht it will call the php code that inserts a new record evrytime a user selects a value in it.
NOTE: am assuming your select field is aclled select_field, otherwise correct accordingly

1. go to field settings of select field in table one, go to cient side scripts, click eleipse button and paste somehing like this.
   { // keys = event types, values = handler functions
   "change": function(e) {
   // Your code
   
   

    var $row = $(this).fields();
    //get selected value

var filed_value = $row["select_field"].value();
console.log(filed_value);
//post to php code to perform an action
$.post(ew_CurrentPage(), { "myajax": 1, "token": EW_TOKEN, "select_field": filed_value }, function(result) { // Post back your custom data (with the synchronizer token)
//do soemthing after it has finished
console.log(result);
});

	
}

}

2.You need to have php code which will perform and operation and return immediately.
go to page_load of add,edit & view of table one depending on which form the select fild will appear on and paste the php code to be called by ajax call, for example

if (@$POST["myajax"] == 1 && @$POST["select_field"] != "" ) { // Check if it is your custom Ajax and if the query value is present
//do your insert now
$return_value = ew_Execute("insert into table_2(id,name) VALUE(1,'my name')"); // Get the desired value (assume ProductID is integer so no need to quote the value)
if($return_value == null)
{echo "Failed to insert";}
else
{echo "inserted successfully";}
$this->Page_Terminate(); // Terminate the page such that this return is all that matters
}

NOTE:Incase the user needs to first submit the form after selecting a value in select field before you can insert into table two, then you dont have to do it like this, you can just put your insert into row_inserted event.

[Fakiro82]

Hi kirondedshem,
I insert the code as indicated by you (I changed the name of the fields as follows:

In CLIENT SCRIPT --> TABLE SPECIFIC --> EDIT PAGE --> STARTUP SCRIPT i insert this:

// Write your table-specific startup script here
// document.write("page loaded");

// keys = event types, values = handler functions
{
"change": function(e) {
// Your code

var $row = $(this).fields();
//get selected value
var ValoreSelectSituazione = $row["int_IDSituazione"].value();
console.log(ValoreSelectSituazione);
//post to php code to perform an action
$.post(ew_CurrentPage(), {"myajax": 1, "token": EW_TOKEN, "int_IDSituazione": ValoreSelectSituazione }, function(result) { // Post back your custom data (with the synchronizer token)
//do soemthing after it has finished
console.log(result);
});

}
}

In SERVER EVENTS --> TABLE-SPECIFIC --> EDIT PAGE --> PAGE_LOAD I Insert this:

// Page Load event
function Page_Load() {

if (@$POST["myajax"] == 1 && @$POST["int_IDSituazione"] != "" ) { // Check if it is your custom Ajax and if the query value is present
//do your insert now
$return_value = ew_Execute("insert into tbl_Note(des_RiferimentoNota) VALUE('Test')"); // Get the desired value (assume ProductID is integer so no need to quote the value)
if($return_value == null)
{echo "Errore durante l'inserimento in Storico Note";}
else
{echo "La nota in storico note è stata inserita con successo";}
$this->Page_Terminate(); // Terminate the page such that this return is all that matters
}
//echo "Page Load";
}

But it not work. Can you help me?

Thanks,
Andrea.

[kirondedshem]

Remember the point to make this work is that the field that the user has to change so as to trigger this has to have the ajax call working well.

what I meant is we need to attach an onchange event handler on the select field in field settings, such that it will trigger the ajax call to insert your record in table 2 everytime a user selects a value in it.
Phpmaker allows to bind event handlers easily in field settings, otherwise you can still bind the same event using startup script.
you can read about "Field settings" for more, plus it also has screenshots of where each property is located.

In CLIENT SCRIPT --> TABLE SPECIFIC --> EDIT PAGE --> STARTUP SCRIPT i insert this:
NO no, I dint mean there although if you are a fmiliar with jquery you can attach an the on change event handler here as well. But let me direct you again on where I meant.
In phpmaker.

1. click on the table for which you are designing the forms for.
2. you see the three tabs(filed settings,table settings, code server events,client scripts). CLICK on field settings.
   Now you should see a list of all fileds in your table, you are now in field settinsg where you can set up each field individually, like select which controll to use on it, change caption etc.
   3.click select the field which you want the user to trigger the insert into table 2 when the user changes a value in it, ie(what you are refering to as the select field).
   Now you are focusing on only that field, so we want to attach an event handler on that field.
   4.look at the bottom right section called "Edit tag" ie (where you usually specify if you want a cotroll to be a text, password, drop down, check box etc).
   You should see properties like custom attributes, size,max length, client side events, use lookup etc, click inside "Client side events".
   You should see an elipse or browse button at the far right of Client side events property, click that button, it should ope a popup dialog with the title "Client side events (javascript)".
   Now that is where you can attach event handlers direct onto a given field.
3. paste the first code inside there.

NOTE: Assuming the user is on the edit page when you want this to happen then its ok to paste teh second code where you put it otherwise you might need to relocate it to correct page.

With this done, if you inspect the page in the browser and go to console log, you should see it printing the selected values everytime you change them, and if your php code is correct you should see the result here as well.

[Fakiro82]

Hi,
I tried but not work.
But what is "myajax": 1 ?
Thanks so much,
Andrea.

[kirondedshem]

Assuming you have put the code into the right place. You need to understand the logic behind what you are trying to do as Ive been explaining earlier,

But what is "myajax": 1 ?
What we are doing is when a user selects avlue in your field we use ajax to do a php post request to the add page as this line says
this line here $.post(ew_CurrentPage(), {"myajax": 1, "token": EW_TOKEN, "int_IDSituazione": ValoreSelectSituazione }.
We post two value with the names myajax ,token and int_IDSituazione.
I fyou check the php code recieveing this request at this line.
if (@$POST["myajax"] == 1 && @$POST["int_IDSituazione"] != "" ) { // Check if it is your custom Ajax and if the query value is present
The php code is meant to process or ajax request so it must check if an incoming post request contins all myajax and int_IDSituazione, if yes, then its an ajax call
and the php code will just do the insert and return after taht ie terminates page on this line $this->Page_Terminate();

what does myajax and probably int_IDSituazione mean.
we use setting myajax = 1 to indicate that we are doing an ajax call(thi si not a phpmker thing, its justbasic programing logic to help us know when a post request is an ajax or not since this same page obviously recieves post request from else where we have to isolate ours from the rest).
the int_IDSituazione is obviously the value you have selected from table one, just incase you need to use it during you insert into table 2.

I tried but not work.

Fisrt you need to verify if the ajax call is being executed without a problem. Go to browser and right click, and select inspect page, Then select the console log tab.
Check 1:
If the ajax call is working, THAT IS IF YOU PASTED THE FIRST CODE INTO THE RIGHT PLACE you should see the results from the following console.log lines.
console.log(ValoreSelectSituazione);
OTHERWISE, it should show a javascript error or some sort in console window.
Check 2:
And if the php execution is fine you shouls see the result of the console.log of
console.log(result);

IF you dont see anything in the console window then you did not paste the code in th right field settings for the right field that should trigger it.

So are you passing the check 1 or check 2, what is happening exactly.

[Fakiro82]

Hi kirondedshem,
I think that:
If I want insert the value of the table1 into table2 after I add new record my code is:

// Page Load event
function Page_Load() {

if (@$POST["myajax"] == 1 && @$POST["int_IDSituazione"] != "" ) { // Check if it is your custom Ajax and if the query value is present
//do your insert now
$return_value = ew_Execute("INSERT INTO tbl_note( CodiceDocumento, int_IDSituazione) VALUES( '" . $rsnew["CodiceDocumento"] . "', '" . $rsnew["int_IDSituazione"] ."'");
// Get the desired value (assume ProductID is integer so no need to quote the value)
if($return_value == null)
{echo "Errore durante l'inserimento in Storico Note";}
else
{echo "La nota in storico note è stata inserita con successo";}
$this->Page_Terminate(); // Terminate the page such that this return is all that matters
}
//echo "Page Load";

But not work, Do you know why?

i try to explain better:

I have 2 table:
1st Table named tbl_Documenti
2nd Table named tbl_Note

When I modify record (change "select field") in tbl_Documenti I want to insert in 2nd Table one record with some value from 1st Table.

Previously kirondedshem had suggested the correct code but I can not enter the values ​​from Table 1.

How can i do this?

Many thanks,
Andrea.

[kirondedshem]

$return_value = ew_Execute("INSERT INTO tbl_note( CodiceDocumento, int_IDSituazione) VALUES( '" . $rsnew["CodiceDocumento"] . "', '" . $rsnew["int_IDSituazione"] ."'");
The above will not work where youve placed it earlier coz $rsnew does not exist in page_load event.

This is what I told you from the start. This mean you dint know what you wanted earlier.

What youve been saying is. You want when a user is on the edit form of table 1 and he changes the value in the select filed(int_IDSituazione) wihtout submiting the form to actually save the chages of table one into the database, You want to go and immediately insert into table 2, This means say a user is on the edit form and he chages the value in the select filed 3 times(wihtout even submiting) you will insert three records into table 2

When I modify record (change "select field") in tbl_Documenti I want to insert in 2nd Table one record with some value from 1st Table.
From this I feel what you want is what I had suggested in the begining.
Just to be clear. What I think you want now is, when a user goes to edit a table one record AND SUBMITS THE FORM(clicks the save button). After the new changes to table 1 are saved you want to insert a record into table 2 based on the new value of tbl_Documenti of table 1 record.

If so then you can do it in row_updated event of table 1, this is when we are sure that a table one record has been submitted and saved into the database.

1. go to table one, row_updated and put.
   NOTE:$sold and $rsnew are arrays containing the old and new table 1 record beofre and after saving respectively.
   function Row_Updated($rsold, &$rsnew) {
   //access any value in the new table 1 record.
   //$the_old_int_IDSituazione = $rsold["int_IDSituazione"];
   //$the_new_int_IDSituazione = $rsnew["int_IDSituazione"];
   //you can access any values as shown above if you need to do some work before you insert into table 2
   //I assume CodiceDocumento and int_IDSituazione are actual fields in table 1
   ew_Execute("INSERT INTO tbl_note( CodiceDocumento, int_IDSituazione) VALUES( '" . $rsnew["CodiceDocumento"] . "', '" . $rsnew["int_IDSituazione"] ."'");

}

HINT: Read about this event in help menu"server events and client scripts" and many others, the help menu clearly indicated what each event does and when its called, so always refer to it to find out where to put your logic

[Fakiro82]

Hi kirondedshem,
your code work fine.
But, exist a way to insert record in table2 with value from table1 directly onchange event without function Row_Updated($rsold, &$rsnew)?

Thanks so much.
Andrea.

[kirondedshem]

I think it better if a person first submits the form and you use row_updated when you are sure he has made his final selection. coz if inserting a record in table 2 means that you are sure that table 1 record will have the selected value as used when inserting record in table 2.
Because a user can decide to alter his selection a number of times before he submits the changes and since you are not sure which selection he will finally go with, if you are creating a new table 2 record everytime he changes his selection even withtout submiting the form you will have very many new table 2 records which depending on the desired result, It might not be a good idea.

But, exist a way to insert record in table2 with value from table1 directly onchange event without function Row_Updated($rsold, &$rsnew)?
But if that what you want, then just go through this post again to use ajax call attcahed on the oncahnge event, coz its what we covered first and you said you wanted to change it to use row_updated.

[Fakiro82]

Hi kirondedshem,
your idea is good.
Instead of passing the fixed value, I would like to pass the values ​​from Table 1
But exist a way to do that directly onchnge event?

Thank you so much for your help.

Andrea.

[sangnandar]

Try this:

• take form-value with jQuery .val()
• fire the values along with ajax

Please note that user can change form-value after ajax call. This means the value fired to tbl2 through ajax might be different from $rsnew value of which stored in tbl1.