Page 1 of 1
Math/Update field after record is saved.
Posted: 2018-07-23 17:39
by jhart
I have a table "Table1" In this table is a field with a numeric value. Units. Current Value is 100. I have another table "Table2" that is a child of Table one. I have a field in the table called "UnitsUsed". I need to have the field in "Table1" updated and subtract the amount in "UnitsUsed" and the display the new amount.
Not sure how to start. Can someone assist me please?
Re: Math/Update field after record is saved.
Posted: 2018-07-23 19:02
by pbottcher
Hi,
you can do this in the hooks/<Table2>.php file. There you will find the functions <table2>_after_insert and <table2>_bevor_update and <table2>_after_update and <table2>_after_delete.
The data array has the values from your detailview. So update units in table1 via the sqlvalue function.
Something like
sqlvalue("UPDATE table1 set Units=Units-".$data['UnitsUsed']." where ### PUT HERE your matching between table1 and table2 ### = ". $data['' ## PUT here your table2 reference to table1 from your form ###'] );
for the insert.
Make sure your logic covers all aspects which can occure, especially if you allow updates.
Re: Math/Update field after record is saved.
Posted: 2018-07-23 22:01
by jhart
Ok so here is a screen shot of my two lists. Here is a copy of the command I entered into the Hooks file:
function BHISNotes_after_insert($data, $memberInfo, &$args){
sqlvalue("UPDATE Clients set BHISUnits=BHISUnits-".$data['UnitsUsed']." where client = ". $data['fullname'] );
return TRUE;
}
After I save the screen for the data entry seems to hang and when I click the X no math has been performed.
Re: Math/Update field after record is saved.
Posted: 2018-07-24 06:02
by pbottcher
Hi,
can you try
Code: Select all
sqlvalue("UPDATE Clients set BHISUnits=BHISUnits-".$data['UnitsUsed']." where id = ". $data['Client'] );
$data['fullname'] is not available.
Re: Math/Update field after record is saved.
Posted: 2018-07-24 18:19
by jhart
Yes this work great. Next small but important question - How can I make the BHISUnits field in the first table read only to all users but admins? And if this is possible would it affect this hook from updating the field value?
Re: Math/Update field after record is saved.
Posted: 2018-07-24 21:24
by pbottcher
Hi,
you can use this in the hooks/Clients.php
Code: Select all
function Clients_dv($selectedID, $memberInfo, &$html, &$args){
/* current user is not an admin? */
if($memberinfo['group'] != 'Admins'){
$html .= <<<EOC
<script>
\$j(function(){
\$j('#BHISUnits').prop('disabled', true);
})
</script>
EOC;
}
And it does not affect the other hook.
Re: Math/Update field after record is saved.
Posted: 2018-07-24 21:32
by jhart
I added the code as shown and when I browse to the page I get the following Error:
This page isn’t working
localhost is currently unable to handle this request.
HTTP ERROR 500
Attached is my clients.php
Re: Math/Update field after record is saved.
Posted: 2018-07-25 05:29
by pbottcher
Hi,
you need to add an addtional closing bracket } to end the Clinets_dv function.
So after EOC; you need } }
Re: Math/Update field after record is saved.
Posted: 2018-07-25 10:49
by jhart
I did catch that. Does it matter that the field BHISUnits is an Integer? " \$j('#BHISUnits').prop('disabled', true); "
Re: Math/Update field after record is saved.
Posted: 2018-07-25 11:16
by pbottcher
No, why?
Re: Math/Update field after record is saved.
Posted: 2018-07-25 11:17
by jhart
Well I added the closing bracket but I am still getting the same error
Re: Math/Update field after record is saved.
Posted: 2018-07-25 12:28
by pbottcher
Hi,
I see the function definition 2 times in your screenshot, remove one pls.
Re: Math/Update field after record is saved.
Posted: 2018-07-25 12:35
by jhart
OK, I am so sorry this isn't working, I am sure it is something I must be doing. I have deleted the section and re added. Attached is the whole hooks file. Thank you for your help and patience. I am just learning and appreciate your help!
Re: Math/Update field after record is saved.
Posted: 2018-07-25 12:50
by pbottcher
Sorry, but you still have the function defintion twice.
I re-add the code here as it should be:
Code: Select all
function Clients_dv($selectedID, $memberInfo, &$html, &$args){
/* current user is not an admin? */
if($memberinfo['group'] != 'Admins'){
$html .= <<<EOC
<script>
\$j(function(){
\$j('#BHISUnits').prop('disabled', true);
})
</script>
EOC;
}
}
make sure function Clients_dv exists only once.