I want to add a note field and a check box to my page and I think I want to record this information in a json file.

[kat35601]

I want to add a note field and a checkbox to my list/grid page and save this information to a json file using uomptrucknumber as the key. The note information will be information for the whole truck not by order. The check box will let them know that the truck is ready to have paper work printed.

I have not add the note field or checkbox because I did not want to start off wrong.

echo "<table>";
echo "<tr style='background:#82fbfd'>";

echo "<th>ShipDate</th>";
echo "<th>ShipMeth</th>";
echo "<th>Name</th>";
echo "<th>LoadID</th>";
echo "<th>Drop</th>";
echo "<th>OrderID</th>";
echo "<th>Status</th>";
echo "<th>FGI</th>";
echo "<th>FGM</th>";
echo "<th>TotalBox</th>";
echo "<th>Cubes</th>";
echo "<th>Total</th>";
echo "<th>Notes \n</th>";
//echo "<table>"; 
echo "<tr style='background:#82fbfd'>";

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}


	
function ShowDetail($row){
       
    if($row['ompclosed']==-1){

        $theColor="#AAF0D2";
            }else{
                $theColor="#FFFFFF";

            }
     if($row['ompclosed']==0 && $row['date'] < date("m/d/Y")) {
        $theColor="#FF0000";
     }    
     
        
		echo "<tr style='background-color :$theColor'><td>" .$row['date'] ."</td>";
        echo "<td>" .$row['ompshippingmethodid'] ."</td>";
        echo "<td style='text-align:left'>" .$row['cmoname'] ."</td>";
        echo "<td>" .$row['uomptrucknumber'] ."</td>";
		echo "<td>" .$row['uompdropsequence'] ."</td>";
        echo "<td>" .$row['ompsalesorderid'] ."</td>";
        echo "<td>" .$row['ompclosed'] ."</td>";
        echo "<td>" .round($row['FGIqty'],0) ."</td>";
        echo "<td>" .round($row['FGMqty'],0) ."</td>";
        echo "<td>" .round($row['uomptotalboxcount'],0) ."</td>";
        echo "<td>" . number_format($row['uompvolumetotal'],2) ."</td>";
        echo "<td>" .  number_format($row['ompordertotalbase'],2) ."</td>";
        echo "<td style='text-align:left'>" .$row['ompordercommentstext'] ."</td></tr>" ;
			  }
			  

	
//****************************************************************

while ($row = odbc_fetch_array($result))
{
    if($row['ompclosed']==-1){

        $theColor="#AAF0D2";
            }else{
                $theColor="#FFFFFF";

            }
     if($row['ompclosed']==0 && $row['date'] < $today) {
        $theColor="#FF0000";
     }    

    if($row['uomptrucknumber'] != $loadid) {
        
        if ($loadid !== 0) {
            echo "<tr style='background:#eee'>
                     <td colspan='7'>TOTAL</td>
                     <td>$TotalFGI</td>
                     <td>$TotalFGM</td>
                     <td>&nbsp;</td>
                     <td>&nbsp;</td>
                  
                     <td>$loadTotal</td>
                   
                  </tr>";
        } 
        
       
      
        $TotalFGI=0;
        $TotalFGM=0;
        $loadTotal=0;
    }

    ShowDetail($row);
    $loadid = $row['uomptrucknumber'];
    $TotalFGI+=round($row['FGIqty'],0);
    $TotalFGM+=round($row['FGMqty'],0);
	$loadTotal +=$row['ompordertotalbase'];
   
    
   
}    
echo "<tr style='background:#eee'>
         <td colspan='7'>TOTAL</td>
         <td>$TotalFGI</td>
         <td>$TotalFGM</td>
         <td>&nbsp;</td>
         <td>&nbsp;</td>
         <td>$loadTotal</td>
      </tr>
      </table>";
	
		

odbc_close($connect);

?>

 

[Barand]

If you have the information in your database, why do want to store it again in JSON format. Although I expect the drivers will be delighted to be handed their deleivery schedule printed

in JSON format.

 

28 minutes ago, kat35601 said:

&& $row['date'] < date("m/d/Y")

You cannot correctly compare dates in m/d/Y format

[gizmola]

Are you familiar with database design and normalization?  Do you have a schema diagram or structure of the tables?  

The main thing I can glean from your question is that you probably have a truck table, and that this is either some information where there is a 1-1 relationship between that information and a truck OR it is information that is related to a truck that is good for a period of time (ie.  it could be current information with an end date, such that there would be a historic record of this information, but you also want to be able to locate and provide the current data for a particular truck, ignoring information that is now essentially expired.

Really in order to design or alter a database schema appropriately, the requirements need to be clear and well understood to make the proper enhancements.

[kat35601]

Three people see this list/grid. When the truck is ready to load we have a person that will print the truck paperwork from our erp system not from this list/grid and the note is to tell the person printing the paper work if they need two copies or a seal or lock etc..... Right now they email this information and email sometime gets over looked with this its up on there screen. As far as the table goes I think all I need is the uomptrucknumber, checkbox and note fields. I was thinking json because I was hoping it would have low overhead verse writing back to my MSSQL Plus I wanted to learn how to create,write and update a json file.

[gizmola]

1 hour ago, kat35601 said:

Three people see this list/grid. When the truck is ready to load we have a person that will print the truck paperwork from our erp system not from this list/grid and the note is to tell the person printing the paper work if they need two copies or a seal or lock etc..... Right now they email this information and email sometime gets over looked with this its up on there screen. As far as the table goes I think all I need is the uomptrucknumber, checkbox and note fields. I was thinking json because I was hoping it would have low overhead verse writing back to my MSSQL Plus I wanted to learn how to create,write and update a json file.

So, first off, json is a data format.  A "json file" is just a file that contains serialized json, that can be unserialized back into a json structure.  The primary use of json is to provide interoperability between javascript functions, and in many cases persistent storage.  Yes, you should use json to for example, provide data to ajax calls, but that would be something you would design into your backend api and javascript UI code.  You can for example, read data from a database and easily turn it into json format using the json_encode() function.  Ditto with receiving client information and converting it back into arrays or objects with json_decode().

As these are just notes telling someone what to do, there isn't any intrinsic benefit to storing the data in  json format, and even less value in having that json be in a separate file that needs to be maintained, tracked, read from and written to.  If you had chosen to build your app around MongoDB, that uses json format to store data and query the db, that would be another thing, but your idea about using json, clearly is making your system less uniform and stable, with no strong justification.  Just use a mysql text column.

Again, I don't know if you have 1 table, 3 tables or 50 tables. 

Even without knowing that, I can tell you some things, based on the information you've provided:

This "Ready to Load" flag is for:

• A truck
• For one load

Again, I don't know anything about your business, so I don't know if a truck can have multiple loads in a day.  These details are important, because they are manifested in a design that will work, vs. one that doesn't.

As for the note itself, the thing that jumps out at me, is your statement that these are just instructions for the person printing out ERP material.  Again, I don't know who enters these notes, but I assume it's a back office person, so the obvious question is: why isn't that note being put into the ERP?  Doesn't the person printing the material use the ERP to view and print the appropriate materials from it?  Seems like that should be something in the ERP and not in a totally separate system.

Let's assume that you are going forward with your current plan ---

For example, let us say you have a truck table, so you add a "ready_to_load" attribute (tinyint: 0 or 1 value)  and a "print_notes" text field that you put notes into.  

"someone"  in your company sets this flag based on some criteria, which then should signify to the "manifest printing person" that various documents from the ERP system need to be printed, and some additional activity should happen.

I should also point out that these are 2 different features that aren't really associated.  One is an activity flag, and the other is some information for a person performing a particular activity.

What are the potential problems putting both these things into the truck table?

• How does this data get reset?  Perhaps when the truck is loaded there is a button that a user uses when the truck leaves the depot?   It's unclear.  If users are expected to interact with the system, reset the flag, and overwrite the note, you have a situation where you invite mistakes because a user forgets to clear the note, and on the next load, people are operating based off notes from a prior load.  A person could see a flag set and prematurely print manifests that aren't actually ready yet.
• If you reset the flag and empty the note, the data is now lost forever.
  • If the instructions weren't followed, how do you know what the were, and where the mistake was made?  Did the original instructions omit something needed?

My best guess is that this information should go into a table related to truck that has a one-to-many relationship to it.  It might be named "truck_load".

truckload
----------
truckload_id  (int unsigned primary key auto_increment)
truck_id (foreign key containing truck_id from truck table)
load_date (date, datetime or timestamp, depending on requirements)
ready_to_load (tinyint, default 0.  1 indicates ready to load)
notes (text)

 

This is my best guess at what you should have to make this work.  One row for each truckload.  You can join to the truck table using the truck_id, and will display the data for the particular day or day/time combination.

 

[kat35601]

uomptrucknumber is unique every-time never duplicated. the data here is only good for thirty days when the uompturcknumber falls off of the grid/list I would want to remove it from the json file. The date and time move constantly so I would not want that in the json file. This is for what we call  pickups we don't handle the logistic for these other companies do and they are a lot of no shows and moving these around.All of this is  onetime information never needed again.

[kat35601]

Today they use a excel spreadsheet that someone keys this information in everyday several time a day that has a check box and note field. I would like some help in adding a checkbox and note field to this code that has the key of uomptrucknumber. and save it so when someone pulls it up that the checkbox and note field are there.