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?
Getting data from second table field with calculations
Re: Getting data from second table field with calculations
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....
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
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.
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
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?
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
Please find attached screenshot and inform me if I did anything wrong.
- Attachments
-
- leaves.png (121.95 KiB) Viewed 15027 times
Re: Getting data from second table field with calculations
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'];
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
Still not working.
Re: Getting data from second table field with calculations
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:
After update:
- Recall after insert function to reuse the same code.
- Note: When recalling a function the $args parameter is without "&"
Cheers,
Weoi
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;
- 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);
}
Weoi
Re: Getting data from second table field with calculations
Thankyou so much wplim. You are great. It worked.
Re: Getting data from second table field with calculations
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
Hello lectura,
could you tell me what exactly is the problem?
could you tell me what exactly is the problem?
Re: Getting data from second table field with calculations
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
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
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
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
i already fix , it i add this code in hock after update
i think its same your code
also we can use ajax but i find this is easy for me
thanks alot
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;
}
also we can use ajax but i find this is easy for me
thanks alot