Let’s suppose that you have the following entries and what you need is an algorithm that will calculate the number of periods (months) between the End Date and Start Date and allocate the amounts equally for each period.
How would you do this ?
In this post I present 2 ideas about how to solve this problem.
We first need a function that receives 2 parameters (StartDate and EndDate) and returns the number of periods between the 2 dates. Then we call this function for each record with Entry Type = Initial Entry and Allocated = False, we divide the Amount from the entry by the number of periods and create new entries for each period.
Calculate the number of months between two dates using this formula: we calculate the difference between the years of the two dates, multiply the result with 12 and add the difference between the months of the two dates. For example if we have Start Date: 01/02/2016 and End Date: 01/12/2017, the number of months is: (2017-2016) * 12 + (12-2) = 22 months.
In order to apply this logic in NAV I wrote the following CAL code:
LOCAL CalculateNoOfPeriods(StartDate : Date;EndDate : Date) : Integer //first method NoOfYears := DATE2DMY(EndDate,3) - DATE2DMY(StartDate,3); NoOfMonths := DATE2DMY(CALCDATE('<CM +1D>',EndDate),2) - DATE2DMY(StartDate,2); EXIT(12 * NoOfYears + NoOfMonths);
Calculate the number of months between two dates using the NAV Date Virtual table: first we filter the Date record from the StartDate to the EndDate and then loop through it. Within the loop we set the StartDate to the first day of the next period. For Example we start from StartDate 01/02/2017 and at the second iteration within the loop we set StartDate to 01/03/2017 and so on, until the StartDate becomes greater than the EndDate. Within the loop we also set the filter on the Date record from the next StartDate to the EndDate, so when it iterates to the next Date record it will skip records between 01/02/2017 and 01/03/2017 (for example) and calculate the number of months by incrementing a local integer variable (i).
The CAL code looks like this:
LOCAL CalculateNoOfPeriods2(StartDate : Date;EndDate : Date) : Integer //second method. Advantage: if needed you can Mark the periods in the Date record //and return the marked periods using a byReference parameter StartDate := CALCDATE('<CM +1D>',StartDate); EndDate := CALCDATE('<CM +1D>',EndDate); Date.SETRANGE("Period Start",StartDate,EndDate); Date.FINDSET; REPEAT StartDate := CALCDATE('<CM +1D>',Date."Period Start"); Date.SETRANGE("Period Start",StartDate,EndDate); i += 1; UNTIL (Date.NEXT = 0) OR (StartDate > EndDate); EXIT(i);
Then, in order to obtain the result from this example I called the function for each entry and created Allocation entries for each period:
OnRun() AllocationExample.SETRANGE("Entry Type",AllocationExample."Entry Type"::"Initial Entry"); AllocationExample.SETRANGE(Allocated,FALSE); IF AllocationExample.FINDSET(TRUE,TRUE) THEN REPEAT //NoOfPeriods := CalculateNoOfPeriods(AllocationExample."Start Date",AllocationExample."End Date"); NoOfPeriods := CalculateNoOfPeriods2(AllocationExample."Start Date",AllocationExample."End Date"); AmountToPost := ROUND(AllocationExample.Amount / NoOfPeriods,0.01); AllocationDate := AllocationExample."Start Date"; REPEAT AllocationDate := CALCDATE('<CM +1D>',AllocationDate); AllocationExample2.INIT; AllocationExample2."Entry No" := 0; AllocationExample2."Entry Type" := AllocationExample2."Entry Type"::"Allocation Entry"; AllocationExample2."Start Date" := AllocationDate; AllocationExample2.Amount := AmountToPost; AllocationExample2.INSERT; NoOfPeriods -= 1; UNTIL NoOfPeriods = 0; AllocationExample.Allocated := TRUE; AllocationExample.MODIFY; UNTIL AllocationExample.NEXT = 0;
This is just an example in order to present the methods you can use to accomplish something like this. In case you have to implement something similar, please adapt the idea to your own needs. In my example I used a Table object 50002 “Allocation Example” and Codeunit 50011 “Allocate Amounts Example”. You can download the sample object from this link: Allocate Amounts Sample objects