Page 1 of 1
Getting data from second table field with calculations
Posted: 2014-05-07 05:08
by mansoor
Can anyone please guide me. I searched almost every topic in the forum but didn't found clear guide for my issue.
table1= employee
field1= id
field2= name
field3= annual_leaves (total leaves allotted to an employee. I set default value to 15)
table2= leaves
field1= id
field2= name (lookup field from table1)
field3= app_leave (applied leave. this is where an employee will enter his required number of leaves but this should not be greater then alloted leaves from table1 field annual_leaves)
field3= rem_al (Remaining annual leaves........... I want this field to be automatically calculated when a user enter his applied leaves value in app_leave field. This value should calculate values from the formula rem_al = annual_leaves - app_leave
i am a newbie with php. Can anyone guide me how can I achieve this?
Re: Getting data from second table field with calculations
Posted: 2014-05-07 06:47
by peebee
Try applying a leaves_before_update() hook in your leaves.php hook file
See here:
http://bigprof.com/appgini/help/advance ... ore_update
function leaves_before_update(&$data, $memberInfo, &$args){
// calculate Remaining Annual Leave
$annual_leaves=sqlValue("select annual_leaves from employee where id='{$data['selectedID']}'");
$data['rem_al'] = $annual_leaves - $data['app_leave'];
return TRUE;
}
I think that might work for you....
Re: Getting data from second table field with calculations
Posted: 2014-05-07 07:24
by mansoor
I applied your code but some problem.
1- I entered value 2 in applied field and save it, the remaining field value is not auto populating and not showing remaining leaves balance (still blank/ empty)
2- When I re-visit this entry again to open and click on save second time it shows me value of -2 in remaining field rem_al. Same is the case if I add 5 in applied leave it shows -5.
Re: Getting data from second table field with calculations
Posted: 2014-05-07 09:13
by peebee
1- The hook won't update the rem_al field until the record is saved. The result should appear after the record is updated. That's the purpose of the tablename_before_update() hook.
2. From what you say, it appears as though the calculation is not taking into account the 15 that you say is already entered into the the annual_leaves field in the employee table as a default? That's why when you enter 2 in app_leave the result is -2 in rem_al. It is just calculating $data['rem_al'] = ? - $data['app_leave']. So enter 5, result is -5, etc...
Are you sure the correct table name is "employee" and field name is "annual_leaves". Have you checked the value in the annual_leaves field is 15? And when I think about it, you are likely going to have a problem I can see because while you might be able to calculate the remianing leave in the leaves table, you are also going to have to subtract the app_leave amount from the remaining annual_leaves days in the employee table so future calculations are correct? Would that be right?
Re: Getting data from second table field with calculations
Posted: 2014-05-07 10:21
by mansoor
Please find attached screenshot and inform me if I did anything wrong.
Re: Getting data from second table field with calculations
Posted: 2014-05-07 23:11
by peebee
Getting the value of annual_leaves from the employee table is the problem. There's a left join required to the employee table to get the value. Sadly, my coding skills are pretty poor too.
ref:
http://www.w3schools.com/sql/sql_join_left.asp
something like.... (although you'll probably have to fiddle around with this)?
$annual_leaves=sqlValue("select e.annual_leaves from employee e left join leaves l on e.id=e.name where leave_id='{$data['selectedID']}'");
$data['rem_al'] = $annual_leaves - $data['app_leave'];
Re: Getting data from second table field with calculations
Posted: 2014-05-08 04:25
by mansoor
Still not working.
Re: Getting data from second table field with calculations
Posted: 2014-05-12 05:25
by wplim
Hi Mansoor,
Here's what you need:
Add these codes in leaves.php in the hook folder.
Use after insert instead of before because the calculation is performed after data is stored into database.
Also, I have set the rem_al as read only.
After insert:
Code: Select all
function leaves_after_insert($data, $memberInfo, &$args){
$id = makeSafe($data['selectedID']);
$emp_id = makeSafe($data['name']);
$apply = sqlValue("SELECT app_leave FROM leaves WHERE leave_id = '$id' ");
$total = sqlValue("SELECT annual_leaves FROM employee WHERE id = '$emp_id' ");
$balance = $total - $apply;
sql("UPDATE leaves SET rem_al ='$balance' WHERE leave_id ='$id' ", $eo);
return TRUE;
After update:
- Recall after insert function to reuse the same code.
- Note: When recalling a function the $args parameter is without "&"
Code: Select all
function leaves_after_update($data, $memberInfo, &$args){
return leaves_after_insert($data, $memberInfo, $args);
}
Cheers,
Weoi
Re: Getting data from second table field with calculations
Posted: 2014-05-12 06:17
by mansoor
Thankyou so much wplim. You are great. It worked.
Re: Getting data from second table field with calculations
Posted: 2017-12-19 19:36
by lectura
Am still struggling with codes... i have tried this but in my results its gives me 0 value.. am not sure where i did wrong.. any help is welcome..
Re: Getting data from second table field with calculations
Posted: 2018-01-08 17:32
by R Tammam
Hello lectura,
could you tell me what exactly is the problem?
Re: Getting data from second table field with calculations
Posted: 2018-01-28 15:40
by xbox2007
hello
i have same project and i try this code , it work only after i press Save changes !!!!
i want ask if there are any way to make update while inserting date
Re: Getting data from second table field with calculations
Posted: 2018-02-20 04:58
by R Tammam
Hello xbox,
redarding to your question , you should
1-make an ajax fie to get balance
$id = makeSafe($_REQUEST['selectedID']);
$emp_id = makeSafe($_REQUEST['name']);
$apply = sqlValue("SELECT app_leave FROM leaves WHERE leave_id = '$id' ");
$total = sqlValue("SELECT annual_leaves FROM employee WHERE id = '$emp_id' ");
$balance = $total - $apply;
2- then you should make javascript code to update the reurned balabce
Re: Getting data from second table field with calculations
Posted: 2018-02-20 07:16
by xbox2007
i already fix , it i add this code in hock after update
Code: Select all
function vacation_after_update($data, $memberInfo, &$args){
$id = intval($_REQUEST['id']);
$name = intval($_REQUEST['name']);
$bb_B = sqlValue("select bb_1 from vacation where id ='{$id}'");
$bb_A = sqlValue("select bb from vacation where id ='{$id}'");
$annual_1 = sqlValue("select annual_1 from Emp where id='{$name}'");
$casual_1 = sqlValue("select casual_1 from Emp where id='{$name}'");
$total_Aday = sqlValue("SELECT sum(days) from vacation where Action ='مواÙقة' And vcation = 1 AND name='{$name}'");
$total_Cday = sqlValue("SELECT sum(days) from vacation where Action ='مواÙقة' And vcation = 3 AND name='{$name}'");
$total_Annn = $annual_1 - $total_Aday;
$total_casu = $casual_1 - $total_Cday;
sql("update Emp set annual='{$total_Annn}' where id='{$name}' AND '{$bb_B}'=''", $eo);
sql("update Emp set casual='{$total_casu}' where id='{$name}' AND '{$bb_A}'=''", $eo);
return TRUE;
}
i think its same your code
also we can use ajax but i find this is easy for me
thanks alot