I have a table 'routes' with a look up field named 'UnitAssigned' which pulls from the 'bus_status' table field name 'BusNumber'
I also have a field named 'Spare' in the 'bus_status' table.
When the BusNumber is 'assigned' to the route table by that field 'routes.UnitAssigned' looking it up I want the field 'bus_status.Spare' to change from Yes to NULL if it was Yes.
When the bus number is un-assigned to the route (no longer selected in the look up field) then I want the 'bus_status.Spare' field to change from NULL to Yes.
This is tricky at best..
I have the IF part, and the how to insert part.. just not sure how to do the part about making the action happen only if that field is updated..
Something like this code below.. but the IF UPDATE does not work.
IF UPDATE $data['routes.UnitAssigned'] UPDATE 'bus_status' SET Spare='Yes' where $data['Spare'] IS NULL
???? HELP ?????
If field modified do action..How to?
If field modified do action..How to?
Tina O'Connell
Web Dev & Appgini FAN
Web Dev & Appgini FAN
Re: If field modified do action..How to?
Since routes.UnitAssigned is a lookup field, it would store the primary key value of the record from the bus_status table. So, in the routes_after_update() function in hooks/routes.php, you could place your check .. something like that:
This covers the assigning part .. but doesn't cover the case where the unit is unassigned ... the idea for this is to use a global variable in the routes_before_update() function to store the old value of UnitAssigned, and check if it has changed in the routes_after_update() hook. If it did change to be empty, update the bus_status table to set Spare to 'Yes'.
Code: Select all
if($data['UnitAssigned']){
sql("update bus_status set Spare=NULL where id='{$data['UnitAssigned']}' and Spare='Yes'", $eo);
}
AppGini plugins to add more power to your apps:
- DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
- Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
- Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.
Re: If field modified do action..How to?
Ahmad,
I tried to do this global.. but it did not work.. can you tell me how the before update global should be..
I just had same code as the {$messagedata} but named it $messagedata2 instead and put the global $varname in front.. no luck.. when I went to do the insert into the old data field nothing inserted..
I tried it 4 different ways.. just doing query's for the data and calling the query a global variable name and trying to set that into the old data field.. again no luck.. how should this be coded?
Thanks,
Tina
I tried to do this global.. but it did not work.. can you tell me how the before update global should be..
I just had same code as the {$messagedata} but named it $messagedata2 instead and put the global $varname in front.. no luck.. when I went to do the insert into the old data field nothing inserted..
I tried it 4 different ways.. just doing query's for the data and calling the query a global variable name and trying to set that into the old data field.. again no luck.. how should this be coded?
Thanks,
Tina
Tina O'Connell
Web Dev & Appgini FAN
Web Dev & Appgini FAN
Re: If field modified do action..How to?
I get what you are trying to do there.. the idea is easy.. the application is not.
Here is what I tried..
BEFORE UPDATE HOOK
Then after udpate...
Your suggestions are welcome.. where am I going wrong.. this is not putting anything into the table for old data but the new_data works perfectly as does everything else in the log table.
The email works great too.. did not put that up here but on different topic.
Here is what I tried..
BEFORE UPDATE HOOK
Code: Select all
function routes_before_update(&$data, $memberInfo, &$args){
global $messageData2;
foreach($data as $field => $value){
$messageData2 .= "$field: $value \n";
}
return TRUE;
}
Code: Select all
$LogChanges=mysql_insert_chng();
sql("INSERT INTO 'Historical_Log' SET RouteNumber='{$data['#recID']}', ChangedBy='".getLoggedMemberID()."', ChangedDate='".time()."', old_data='{$messageData2}', new_data='{$messageData}', $eo");
return TRUE;
}
The email works great too.. did not put that up here but on different topic.
Tina O'Connell
Web Dev & Appgini FAN
Web Dev & Appgini FAN
Re: If field modified do action..How to?
Tina,
the syntax of the insert statement must be
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
Try this
sql("INSERT INTO Historical_Log (RouteNumber, ChangedBy, ChangedDate, old_data, new_data)
VALUES( '{$data['#recID']}', '".getLoggedMemberID()."' , '".time()."', '{$messageData2}', '{$messageData}') ; $eo");
Maybe not all the 'and " are correct
Succes,
Bert
the syntax of the insert statement must be
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
Try this
sql("INSERT INTO Historical_Log (RouteNumber, ChangedBy, ChangedDate, old_data, new_data)
VALUES( '{$data['#recID']}', '".getLoggedMemberID()."' , '".time()."', '{$messageData2}', '{$messageData}') ; $eo");
Maybe not all the 'and " are correct
Succes,
Bert
Bert
I am using Appgini 5.75
I am using Appgini 5.75
Re: If field modified do action..How to?
How do I do the 'IF' statment part?
If the field is changed or modified.. I want it to capture the data, not if it is not changed..
Not just the entire record, just one field..
What is the syntax for...
if routes.UnitNumber is Modified(ACTION CODE HERE to save and insert into log)
???
If the field is changed or modified.. I want it to capture the data, not if it is not changed..
Not just the entire record, just one field..
What is the syntax for...
if routes.UnitNumber is Modified(ACTION CODE HERE to save and insert into log)
???
Tina O'Connell
Web Dev & Appgini FAN
Web Dev & Appgini FAN
Re: If field modified do action..How to?
Sometimes it is difficult to understand what exactly the problem is and what the solution, but i think i know what you want.
For that reason I have created a simple AppGini application with three tables: routes, units and historical log, just for testing.
It is saved and you can download it from
https://www.dropbox.com/s/6kbp0is22wzfpsa/tina_db.axp
On the hook file routes.php are some changes: routes_after_insert, routes_before_update and routes_after_update
For debugging a small change in routes_footer.
You can download it from
https://www.dropbox.com/s/uebf2setaaqscxi/routes.php
If you download and install this in your (test) database, you can test it and look code of routes.php for the solution.
Succes,
For that reason I have created a simple AppGini application with three tables: routes, units and historical log, just for testing.
It is saved and you can download it from
https://www.dropbox.com/s/6kbp0is22wzfpsa/tina_db.axp
On the hook file routes.php are some changes: routes_after_insert, routes_before_update and routes_after_update
For debugging a small change in routes_footer.
You can download it from
https://www.dropbox.com/s/uebf2setaaqscxi/routes.php
If you download and install this in your (test) database, you can test it and look code of routes.php for the solution.
Succes,
Bert
I am using Appgini 5.75
I am using Appgini 5.75
Re: If field modified do action..How to?
Bert! You are so wonderful to take your personal time to do this for me. I am most grateful and the code is marvelous.. it worked!!! Thank you so much!!!
Tina O'Connell
Web Dev & Appgini FAN
Web Dev & Appgini FAN