(Remaining balance and end balance should be automated)the 15 in Remaining balance is default. As you can see the remaining balance for the next leave will be the end balance from the 1st leave and so on. I need your help on how to do this in salesforce. I have tried formula fields but the values change per leave. I also tried in reports but the custom formula cant do. Any useful comment will be appreciated. Thanks
My approach in tackling this problem would be to create 2 workflows on the Leave__c SObject.
The 1st) workflow evaluates on the following conditions
AND(IsChanged(No_Of_Days__c), PriorValue(Remaining_Value__c)!=15 )
Then Add a workflow action to update a fields and you'd set:
Remaining_Ballance__c = End_Ballance__c End_Ballance__c = Remaining_Ballance__c - Number_of_Days
The 2nd) workflow which deals with the case when the Remaining Balance is 15
AND(IsChanged(No_Of_Days__c), PriorValue(Remaining_Value__c)==15 )
Then Add a workflow action to update a field and you'd set:
End_Ballance__c = Remaining_Ballance__c - Number_of_Days
I have been experimenting for quite some time and I come up with this approach. Since I have Leave Transaction object which is the parent of the leave object, I created a rollup summary of all used leave. On the Leave object, I used work flow field update to get the value of the remaining leave balance. since it is a number filed, any changes in roll up summary cannot affect the number field in leave object.
You have a leave object. But if you are tracking the leaves for multiple users/Employees you would need to use the Use another object(User or some custom object like Employee) as the parent of the leaves object.
Let us assume that you have a custom object named Employee which is the parent of the leaves object. So have a field named 'Remaining_Leaves__c' in the Employee__c object - this field will contain the number of leaves remaining for the employee record. I am assuming that 'Employee__c' is a lookup parent of Leaves__c'(if it were MD then things will be simpler and you could use roll up summaries).
Next write a trigger on the 'Leaves__c' so that whenever a record is created, its parent Employee__c record's Remaining_Leaves__c field is reduced by the number of days the leave is taken for. If the relationship were a master detail - we could use rollup to count the 'number of days' of each child leave record and then delete it from original number of leaves to get the Remaining_Leaves__c .
Hope you get the idea. The table you have shown above would still be obtainable since you could always fetch the parent record values(Employee__c field Remaining_Leaves__c ) in the child record(Leaves__c). So rest should be fine.
If you are sticking with only the 'Leaves__c' object - and not an employee object , you lose the ability to track leaves for multiple employees. Well if that is what you want then fine.
So, now we are left with only the 'Leaves__c' object and then you want to obtain data in the form of the table shown , probably when you query,report on the object.
So now, write a trigger on 'Leaves__c' object. Logic :-
Whenever, a Leaves__c record is created or updated(before insert and before update) - query all the Leaves__c records to get the lowest End_Balance__c field value. [SELECT id, End_Balance__c FROM Leaves__c ORDER BY End_Balance__c ASC]
Then choose the first record from the query result and get its End_Balance__c - that will be the lowest End_Balance__c value in the system. So this will be the new record's 'Remaining_Balance__c' value, and since you already have the 'No_of_days__c' in the trigger.new, use it to calculate the End_Balance__c of the new leave record.
With this framework you can maintain, the leaves table.
PS: Be careful of bulkification and null pointer checks(when no leave records exist) in actual implementation.
Hope my understanding was correct and this helps.