2. PHPRunner General questions
  3. [SOLVED]  Updating Table Record from Field of Different Table
This topic is locked
[SOLVED]

 Updating Table Record from Field of Different Table

 		4/3/2015 8:28:33 PM
PHPRunner General questions
D
 dustinforehand author

I have 2 tables Inventory & Tracking

Inventory has Inv_ID, Serial, Model, Active

Tracking has Track_ID, Serial, Model, Date_In, Date_Out, Active
The two table have master / child relationship - With Inventory Master, and Tracking Child using the Serial Field
( No Relationships) I have created a "CUSTOM VIEW" of Inventory Database Called Active Inventory.

( No Relationships) I have created a "CUSTOM VIEW" of Tracking Database called Allocate.
I am trying to accomplish two things:

On the Custom View called Active Inventory - Its only the list page with a query for all records that have a "1" in the Active Field

  • I would like to display the field called "Serial" as a link, than when clicked it will open the "Add Page in Tracking" and Auto Populate the "Serial" field with the Value that was in the "Serial" field on the Active Inventory List page that was clicked.

  1. On the ADD PAGE of Allocate, I want to create a check box that will Take Value of Active Field and Update the Active Field in Table Inventory Based on the "Serial" since this is they Table Key in Table Inventory - I would like it to process after record is added.
    I am slowly learning MYSQL and PHP so anyone that can help me, I would greatly appreciate help or where to look for information to accomplish this.
C
 		copper21 4/3/2015

Dustin,
I am no expert either, but I have done this numerous times in my applications and seems to work well for me. So here are the steps to follow; this should help.

  1. Make the 2 views (Active Inventory and Allocate) so that they do have a relationship. Drag "Serial" from Active Inventory to "Serial" from Allocate. This will set up Active Inventory as the master and Allocate as the child. When the box pops up, uncheck all of the boxes. Make sure it states on top, "Master: Active Inventory -> Details: Allocate" and that the link fields should have "Serial" in both.
  2. Go the "Query" of Active Inventory and make an alias for Serial by doing something like "Serial AS SerialLink", in your query. This will create a new field in your Active Inventory list page. Place it where you want it on your list page.
  3. Go to the list page of Active Inventory in the editor and delete the link it created to the child table, you wont need it.
  4. On the new field you created, SerialLink, double click on the field and "view as" custom. You will use some javascript to make the add page pop-up. Here is the javascript:
    $value = "

    <script type=\"text/javascript\">

    // Popup window code

    function Allocate(url) {

    popupWindow = window.open(

    url,'popUpWindow','height=500,width=650,left=20,top=20,resizable=no,scrollbars=no,toolbar=no,menubar=no,location=no,directories=no,status=no')

    }

    </script>

    <a href=\"Javascript:Allocate('http://SERVERNAME/APPLICATIONNAME/Allocate_add.php?mastertable=Allocate&masterkey1=".$data["SerialLink"]."';);\">Allocate</a>";
    You will have to replace the servername and your application name based on what those names are. Also on the child table, Allocate, make sure that "Serial" is listed as the key in the "Pages" section of PHPRunner. You can also changed the size of the add page popup by changing height and width, etc.
  5. Make sure you have an "add page" for the custom view "Allocate".
  6. On your add page for allocate, make the Serial field read only.
  7. In the events of the add page for Allocate, go to after record added and put the following code:
    global $conn;

    $sql = "UPDATE Inventory SET Active = ".$values['Active']." WHERE Serial = ".$values['Serial']."";

    CustomQuery($sql);
    Make sure to use your actual table name in the database for the inventory table, not the custom view name. This is assuming that you have a checkbox with a field name of "Active" on the add page.
    Let me know if this works for you.
    Brian
D
 		dustinforehand author 4/4/2015

Thank you Thank you - Yes It worked PERFECT ! I apprecaite it!



Dustin,
I am no expert either, but I have done this numerous times in my applications and seems to work well for me. So here are the steps to follow; this should help.

  1. Make the 2 views (Active Inventory and Allocate) so that they do have a relationship. Drag "Serial" from Active Inventory to "Serial" from Allocate. This will set up Active Inventory as the master and Allocate as the child. When the box pops up, uncheck all of the boxes. Make sure it states on top, "Master: Active Inventory -> Details: Allocate" and that the link fields should have "Serial" in both.
  2. Go the "Query" of Active Inventory and make an alias for Serial by doing something like "Serial AS SerialLink", in your query. This will create a new field in your Active Inventory list page. Place it where you want it on your list page.
  3. Go to the list page of Active Inventory in the editor and delete the link it created to the child table, you wont need it.
  4. On the new field you created, SerialLink, double click on the field and "view as" custom. You will use some javascript to make the add page pop-up. Here is the javascript:
    $value = "

    <script type=\"text/javascript\">

    // Popup window code

    function Allocate(url) {

    popupWindow = window.open(

    url,'popUpWindow','height=500,width=650,left=20,top=20,resizable=no,scrollbars=no,toolbar=no,menubar=no,location=no,directories=no,status=no')

    }

    </script>

    <a href=\"Javascript:Allocate('http://SERVERNAME/APPLICATIONNAME/Allocate_add.php?mastertable=Allocate&masterkey1=".$data["SerialLink"]."';);\">Allocate</a>";
    You will have to replace the servername and your application name based on what those names are. Also on the child table, Allocate, make sure that "Serial" is listed as the key in the "Pages" section of PHPRunner. You can also changed the size of the add page popup by changing height and width, etc.
  5. Make sure you have an "add page" for the custom view "Allocate".
  6. On your add page for allocate, make the Serial field read only.
  7. In the events of the add page for Allocate, go to after record added and put the following code:
    global $conn;

    $sql = "UPDATE Inventory SET Active = ".$values['Active']." WHERE Serial = ".$values['Serial']."";

    CustomQuery($sql);
    Make sure to use your actual table name in the database for the inventory table, not the custom view name. This is assuming that you have a checkbox with a field name of "Active" on the add page.
    Let me know if this works for you.
    Brian