Store all detail records in another table

Are you adding this code to master or to details table event? The code itself doesn't look correct either.

This code is added to master table.
Example:

this is the master table
id -------- Request_Nr -------- Agent -------- Status

1 -------------- 1001 -------------- Steven ------- Created
tis is the connected detail table
hw_id -------- Request_Nr -------- Typ ---------------- Serial

123 -------------- 1001 ------------ iPhone 7 ------------ ABC987654

124 -------------- 1001 ------------ Samsung 7 -------- DEF654321

125 -------------- 1001 ------------ Huawei 8 ----------- GHI456789
what I need is: when Status (master) is changed to "Processed" (and each time by any Status change), three records should be created in the Table_Tracking as followed:
id -------- Request_Nr -------- Agent ---------- Status ---------- hw_id

1 -------------- 1001 -------------- Steve -------- Processed --------- 123

2 -------------- 1001 -------------- Steve -------- Processed --------- 124

3 -------------- 1001 -------------- Steve -------- Processed --------- 125
That means: three fields from master table (Request_Nr, Agent and Status) and one field from detail table (hw_id).
I hope this description is understandable.

Basic question is: is that possible in this way?

Can't you display the 3rd table in a custom view based on the first 2 tables?

No, because with your suggestion I will get only the last value of the "Status" per hw_id.

What I need is the whole history with all values saved per hw_id:
id -------- Request_Nr -------- Agent ---------- Status ------------ hw_id

1 -------------- 1001 -------------- Peter -------- Created ------------- 123

2 -------------- 1001 -------------- Peter -------- Created ------------- 124

3 -------------- 1001 -------------- Peter -------- Created ------------- 125

4 -------------- 1001 -------------- Steve -------- Ordered ------------- 123

5 -------------- 1001 -------------- Steve -------- Ordered ------------- 124

6 -------------- 1001 -------------- Steve -------- Ordered ------------- 125

7 -------------- 1001 -------------- Steve -------- Processed --------- 123

8 -------------- 1001 -------------- Steve -------- Processed --------- 124

9 -------------- 1001 -------------- Steve -------- Processed --------- 125
Thanks for reply.

I think this can be done by : an event to add Master table fields after edit to Tracking table wihtout hw_id Then make a Sql auery of custom view to add the field hw_id.

How is the hw_id generated ? If it is not in the master . How would it show up in the details ? What joins it or what generates it . Then we may be able to help?

"hw_id" is an auto-increment field of the Table_Assets. Assets will be manualy added to this table (for example logistic department) and then stored into Table_Hardware per Event "After record added". I have skipped this information because this step works fine.

Table_Order (master) and Table_Hardware (detail) are connected over "Request_Nr" field.
If this could be helpfull I have uploaded this structure into demo account for better understanding:
demoaccount cardman
As you can see in the Tracking table are 4 assets created (this is OK)

In the Table Order is one order created and 3 assets connected (this is OK), after editing data (Status changed to "Ordered" in the master table) should all three detail assets become the same new status (this works) and be stored in the Table Tracking (not OK). That's just happened only with the first one.
And of course, the most important thing: how can I get the hw_id to store it into Table Tracking.
Thanks in advance, that would be great.

I think I got a solution for you if i really understood what you need:

First In Order Table add page event, before record added add thew following code:

then in edit page also of table Order in the event before record update add the same code.

Second step: Create a customer view of the table tracking give it a name you want with the following SQL code:

That's all

Hi Alain,

first of all thank you for the assistance.

1. First suggested step is already implemented and works fine. All Status fields in detail table are updated after Status change in the Master table. That's ok and was not the problem.
   
2. With joining on "hardware.Request_Nr = tracking.Request_Nr" I get each records from hardware triple (when 3 records are in the detail table).
   Maybe simple explanation on this way:
   
3. Agent1 creates a new 5 records on Table_Asset and set status to "Created" for each. After records created all of them should be also stored in Table_Tracking (of course without Request_Nr)
   
4. Agent2 creates a new order on Table_Order with Request_Nr (nothing should be created in Table_Tracking
   
5. Agent2 add three assets to the Order on Details:table_hardware and changes the Status of the Order (master) to "Ordered". At this step I need all three assets with updated status also stored into Table_Tracking.
   
6. Agent3 set the status in the Table_Order to "Processed". All three assets should be stored again into Table_Tracking with this status.
   That means: at this moment I must have 11 records in the tracking table (5x "Created" without Request_Nr, 3x "Ordered" with Request_Nr, 3x "Processed" with Request_Nr) with this structure
   

• id (auto-increment)
  
• hw_id (data from Table_Assets)
  
• Request_Nr (data from Table_Order)
  
• Agent (data from Table_Order)
  
• Status (data from Table_Order)
  Until now I get:
  
• after creation in Table_Assets all records are also stored in Table_Tracking (with all needed fields: hw_id, agent, status)
  
• status of all detail records were updated when master status is changed (100% what I need)
  
• only first record from detail table will be stored into Tracking_Table after status changed in master (I need all three).
  I hope that this explanation can help to understand what I need.
  Thanks again in advance

1. First suggested step is already implemented and works fine. All Status fields in detail table are updated after Status change in the Master table. That's ok and was not the problem.
   
2. With joining on "hardware.Request_Nr = tracking.Request_Nr" I get each records from hardware triple (when 3 records are in the detail table).
   Maybe simple explanation on this way:
   
3. Agent1 creates a new 5 records on Table_Asset and set status to "Created" for each. After records created all of them should be also stored in Table_Tracking (of course without Request_Nr)
   
4. Agent2 creates a new order on Table_Order with Request_Nr (nothing should be created in Table_Tracking
   
5. Agent2 add three assets to the Order on Details:table_hardware and changes the Status of the Order (master) to "Ordered". At this step I need all three assets with updated status also stored into Table_Tracking.
   
6. Agent3 set the status in the Table_Order to "Processed". All three assets should be stored again into Table_Tracking with this status.
   That means: at this moment I must have 11 records in the tracking table (5x "Created" without Request_Nr, 3x "Ordered" with Request_Nr, 3x "Processed" with Request_Nr) with this structure
   

• id (auto-increment)
  
• hw_id (data from Table_Assets)
  
• Request_Nr (data from Table_Order)
  
• Agent (data from Table_Order)
  
• Status (data from Table_Order)
  Until now I get:
  
• after creation in Table_Assets all records are also stored in Table_Tracking (with all needed fields: hw_id, agent, status)
  
• status of all detail records were updated when master status is changed (100% what I need)
  
• only first record from detail table will be stored into Tracking_Table after status changed in master (I need all three).
  I hope that this explanation can help to understand what I need.
  Thanks again in advance
  

Hi,

In the question i did not notice if you talk about table assets, what i understood is that there are only 3 tables Order, Hardware and Tracking.

If you provide result desired in the 3 tables , I will try to , How will look the tables results in final

Alain you can see the demo at demoaccount
Sorry, that was my mistake and bad explanation. Table_Hardware is only custom view of Table_Assets.
OK, and that's what I need in the Tracking_Table:
id -------- Request_Nr -------- Agent ---------- Status ------------ hw_id

1 -------------- -------------- Andy -------- Created ------------- 123

2 -------------- -------------- Andy -------- Created ------------- 124

3 -------------- -------------- Andy -------- Created ------------- 125

4 -------------- -------------- Andy -------- Created ------------- 126

5 -------------- ____ -------------- Andy -------- Created ------------- 127

6 -------------- 1001 -------------- Steve -------- Ordered ------------- 123

7 -------------- 1001 -------------- Steve -------- Ordered ------------- 124

8 -------------- 1001 -------------- Steve -------- Ordered ------------- 125

9 -------------- 1001 -------------- Peter -------- Processed --------- 123

10 ------------ 1001 -------------- Peter -------- Processed --------- 124

11 ------------ 1001 -------------- Peter -------- Processed --------- 125

From The Master Table and Table_Assests or whatever table you want to add your records from and too . after record edited it may be an update statement or just an add statement. You need to decide based on your structure. You may also need and if statement query like if this field is changed update Table_Tracking or if Master_table field is changed add to tracking table Table_tracking. etc..... if not } end {...... or elseif statement .
//[size="1"][size="3"] Notice param instead of value - it is like where statement so master table will insert all records with the Status,Request_Nr number and Agent who is assigned to it of current record into Table[/size][/size]
global $dal;

$tblTable_Tracking = $dal->Table("Table_Tracking");

$tblTableTracking = $dal->Param_("Status");

$tblTableTracking = $dal->Param_("Request_Nr");

$tblTableTracking = $dal->Param_("Agent");

$tblTable_Tracking->Value["Request_Nr"]=$values["Request_Nr"];

$tblTable_Tracking->Value["Agent"]=$values["Agent"];

$tblTable_Tracking->Value["Status"]=$values["Status"];

$tblTable_Tracking->Add();
Never tried this in ADD statement , if this does not work use update statement like this just change ADD() to update but if record is not available then it won't update so maybe your whole structure needs to be changed to 2 main tables ,1 Order(Master) 2 Details, and (ArchiveTable)Table_Tracking . When status is changed in any of them use code above to either update or add. But once again Request_NR has to be available in in all 3 tables. in your demo it does not fill in all the time so you have a structure problem.
I am sure I can get this working I would need project and database to this. You may want to buy a support contract with an hour of support / coding . See Support . This post is very confusing. I even looked at demo and don't get it. You are using the hard way out. When based on your input can be done with different structure so much easier . If you still want support from this post . I would suggest showing each table seperately in one post with all fields and explain which each table does . Then ask what you need. Hope this helps a little. Maybe walkfly will chime in as he probably knows the easy way out !
I am going to post below my code in one of my projects which I don't usually do . It is similar to what you want you are welcome to use it and change fields

This checks different field values after record is edited. Once again notice the PARAM feature. This table is already setup to show details on add and edit page if any record exists that PARAM determines what field to update and if table link is setup correct with link on phprunner table links then this is connected too all details therefore you can do the same thing .
IF ($values["bmcustapproved"] <> "" AND $values["fldtype"] === 'Display') {
global $dal;
$tblBook_Display = $dal->Table("Book_Display");
$tblBook_Display->Param["fldavail"]=$values["Pos"];

$tblBook_Display->Param["fldreason"]<>"Approved";

$tblBook_Display->Param["custname"]=$values["bmcustapproved"];

$tblBook_Display->Value["bmapproval"]=$values["needbmapp"];

$tblBook_Display->Value["custname"]=$values["bmcustapproved"];
$tblBook_Display->Update();
}
// Test UPDATE POS WITH customer option ****Promotional POS ****
elseif ($values["bmcustapproved"] <> "" AND $values["fldtype"] === 'Promotional') {

global $dal;
$tblPromotions = $dal->Table("Promotions");
$tblPromotions->Param["fldavail"]=$values["Pos"];

$tblPromotions->Param["fldreason"]<>"Approved";

$tblPromotions->Param["custname"]=$values["bmcustapproved"];

$tblPromotions->Value["bmapproval"]=$values["needbmapp"];

$tblPromotions->Value["custname"]=$values["bmcustapproved"];
$tblPromotions->Update();
}
// TEST UPdate display WITHOUT customer option *Displays ***
elseIf ($values["fldtype"] === 'Display' AND $values["bmcustapproved"]=="") {
global $dal;
$tblBook_Display = $dal->Table("Book_Display");
$tblBook_Display->Param["fldavail"]=$values["Pos"];

$tblBook_Display->Param["fldreason"]<>"Approved";

$tblBook_Display->Value["bmapproval"]=$values["needbmapp"];
$tblBook_Display->Update();
// Test UPdate Promotions POS WITHOUT customer option ***Promotions **
}

elseif ($values["fldtype"] ==='Promotional' AND $values["bmcustapproved"]=="") {

global $dal;
$tblPromotions = $dal->Table("Promotions");
$tblPromotions->Param["fldavail"]=$values["Pos"];

$tblPromotions->Param["fldreason"]<>"Approved";

$tblPromotions->Value["bmapproval"]=$values["needbmapp"];
$tblPromotions->Update();
}
else {

}
IF ($values["bmcustapproved"]<>"") {
$sql = "update Pos_Inventory set needbmapp='" . $oldvalues["needbmapp"]. "' Where Pos='" . $values["Pos"]."'";

CustomQuery($sql);

}
else {

}
If ($oldvalues["fldtype"]=='Both') {

$sql = "update Pos_Inventory set fldtype='Both' Where Pos='" . $values["Pos"]."'";

CustomQuery($sql);
// notice I changed the field back to it's original value even though someone updated it to another value ( Status ex.... )
} else {

}
echo '<script>alert("Confirmed! / POS has been updated / IF set for specific customer, future POS for this item will reset to your original setting and details will be listed upon edit under form entries on edit page")</script>';
After looking at this you should get the idea that you can show multiple records after update as details in your add,list,edit page . You just need to check off show details on add page and edit page and finally list page to show all related records in the related tables setup on master/details relation setup on first page of PHPrunner or in add tables on second option query designer. click the checkbox in the table setup page (linking tables) to show where you want to see linked related records( checkbox - list,edit,add and more options to help) Wish they would allow screenshots here instead of a url to show as security won't allow me to show you setup.

Hello Cardman

I tried to understand what you need,I am not sure i got the point but i got a results as you describe please follow this steps i did

You need only 3 Tables :

Table Order with fields : id, Request_Nr, Agent, Status

Table Assets with fields : hw_id,Request_Nr,Type,Serial,Status

Table Tracking with fields : id, Request_Nr, Agent, Status

set Table Order as Master and Assets as Child link in the field Request_Nr
In visual editor of the add page of Assets set Request_Nr as Lookup wizard from the table Order with auto fill Status = Status

then set Status as Lookup wizard from list value with the values" Created, Ordered, Proceed) defaults Value Created

In events got the Order , Edit page , before recodred Updated and add the code :

In add page event no need to add any thing

Then Create a customer view of the table Order and name it TrackingDetails with the sql code:

Please notice the the join is a right join

when you generate this application and follow ur steps on the comment No #10

You will get at the end 11 records(Rows) on the page Tracking Details.

I tried it and it works as you describe

I could not imagine that my question would be so confused.

There are three tables: Master, Details, Target.

I really wanted only one answer to a basic question:is it possible with a code to copy several/all connected details into a another (Target) table each time when data in the master table are updated?

A joining of the target table to another table is not desired.

If this is possible, then I consider the purchase support/coding if no other expert here can not or will help.
All suggestions offered here do not meet my expectations. With John's proposal, only one record is copied to the target table. Alain's proposal is based on joined tables. The result is dynamic and I can not use this as tracking / history. Nevertheless many thanks guys. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=81468&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />

You might be able to do this using a trigger in your database.

Jerry, just 4 words: "you are the best"!

Only a simple trigger AFTER UPDATE and it works.

That's solution!!!!
Thank you and best Regards <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=81470&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />

I am kind of late. While Jerry's approach has worked for your purpose, I just want to post my traditional query method. And I am not sure if this is going to work or not.
Table_Order table After record updated event

I am happy this approach works for you. I do a lot on the database side. Sometimes it's just easier.

I've tested it and this is the result:
Error type 256

Error description Unknown column 'REQ1001' in 'where clause'

URL localhost/demoaccount/table_order_edit.php?submit=1&editid1=1&

Error file C:\xampp\htdocs\demoaccount\connections\Connection.php

Error line 642

SQL query *select from table_assets where Request_Nr = REQ1001**
Thanks anyway

This is probably because your Request_Nr field is not numeric as stated in your earlier post. Try to add single quote to its variables. Again I cannot promise that this is going to work.
Also I found a syntax error in my previous post. -Sorry.