Dynamics Community Forum Thread Details

I am using a report extension to implement a type of first in, first out logic for the detail trial balance report. The requirement is for all preceding inflows to be depleted completely before the next inflow is touched, therefore, first in, first out.

The reason for this is that the funding comes from an international donor and is converted to LCY at varying rates. So, to maintain consistency with audits and such, this kind of behavior is required. To achieve this, I have created a table which I use to store the records coming from the GL. When an expenditure will deplete an inflow, I split it up and create multiple lines on my new table.

    local procedure CreateFIFOAllocationLine()
    var
        CreditAmount: Decimal;
        GLAmount: Decimal;
    begin
        EntryNo += 1;
        FIFOAllocationLine.Init();
        FIFOAllocationLine."Entry No." := EntryNo;
        FIFOAllocationLine."Source Entry No." := GenLedgEntry."Entry No.";
        FIFOAllocationLine."Posting Date" := GenLedgEntry."Posting Date";
        FIFOAllocationLine."Document No." := GenLedgEntry."Document No.";
        FIFOAllocationLine."Global Dimension 1 Code" := GenLedgEntry."Global Dimension 1 Code";
        FIFOAllocationLine."Global Dimension 2 Code" := GenLedgEntry."Global Dimension 2 Code";
        FIFOAllocationLine.Description := GenLedgEntry.Description;
        if (i <> 0) and (i <= 1024) then begin
            // logic to calculate allocated amount for each inflow and populate the FIFO Allocation Line table
            if (InflowAmountArray[i] > 0) then begin
                if InflowAmountArray[i] > GenLedgEntry."Credit Amount" then begin
                    FIFOAllocationLine."Allocated Amount (ARC)" := (GenLedgEntry."Amount" / ExchangeRateArray[i]);

                    FIFOAllocationLine."Allocated Amount" := GenLedgEntry.Amount;
                    FIFOAllocationLine."Allocated Credit Amount" := GenLedgEntry."Credit Amount";
                    FIFOAllocationLine."Allocated Credit Amount (ACR)" := (GenLedgEntry."Credit Amount" / ExchangeRateArray[i]);
                    InflowAmountArray[i] -= GenLedgEntry."Credit Amount";

                    if GenLedgEntry."Debit Amount" > 0 then begin
                        FIFOAllocationLine."Allocated Debit Amount" := GenLedgEntry."Debit Amount";
                        FIFOAllocationLine."Allocated Debit Amount (ACR)" := GenLedgEntry."Source Currency Amount";
                        if GenLedgEntry."Global Dimension 2 Code" <> 'TRF' then begin
                            InflowAmountArray[i] += GenLedgEntry."Debit Amount";
                            FIFOAllocationLine."Allocated Debit Amount (ACR)" := GenLedgEntry."Debit Amount" / ExchangeRateArray[i];
                        end;
                    end;

                    FIFOAllocationLine.Insert();
                end else begin
                    // split the credit amount across multiple inflows if necessary
                    GLAmount := GenLedgEntry.Amount + InflowAmountArray[i];
                    CreditAmount := GenLedgEntry."Credit Amount" - InflowAmountArray[i];
                    FIFOAllocationLine."Allocated Amount (ARC)" := (-InflowAmountArray[i] / ExchangeRateArray[i]);

                    FIFOAllocationLine."Allocated Amount" := -InflowAmountArray[i];
                    FIFOAllocationLine."Allocated Credit Amount" := InflowAmountArray[i];
                    FIFOAllocationLine."Allocated Credit Amount (ACR)" := (InflowAmountArray[i] / ExchangeRateArray[i]);
                    InflowAmountArray[i] := 0;

                    FIFOAllocationLine.Insert();
                    // advance to next inflow if within array bounds, otherwise stop (set sentinel)
                    if i < 1024 then begin
                        i += 1;
                        EntryNo += 1;
                        FIFOAllocationLine.Init();
                        FIFOAllocationLine."Entry No." := EntryNo;
                        FIFOAllocationLine."Source Entry No." := GenLedgEntry."Entry No.";
                        FIFOAllocationLine."Posting Date" := GenLedgEntry."Posting Date";
                        FIFOAllocationLine."Document No." := GenLedgEntry."Document No.";
                        FIFOAllocationLine."Global Dimension 1 Code" := GenLedgEntry."Global Dimension 1 Code";
                        FIFOAllocationLine."Global Dimension 2 Code" := GenLedgEntry."Global Dimension 2 Code";
                        FIFOAllocationLine.Description := GenLedgEntry.Description + '-1';
                        FIFOAllocationLine."Allocated Amount" := GLAmount;

                        FIFOAllocationLine."Allocated Amount (ARC)" := (-CreditAmount / ExchangeRateArray[i]);
                        FIFOAllocationLine."Allocated Credit Amount" := CreditAmount;
                        FIFOAllocationLine."Allocated Credit Amount (ACR)" := (CreditAmount / ExchangeRateArray[i]);
                        InflowAmountArray[i] -= CreditAmount;
                        FIFOAllocationLine.Insert();
                    end else
                        i := 0;
                end;
            end else begin
                FIFOAllocationLine."Allocated Credit Amount" := GenLedgEntry."Credit Amount";
                if ExchangeRateArray[i] <> 0 then
                    FIFOAllocationLine."Allocated Credit Amount (ACR)" := (GenLedgEntry."Credit Amount" / ExchangeRateArray[i])
                else
                    FIFOAllocationLine."Allocated Credit Amount (ACR)" := (GenLedgEntry."Credit Amount" / ExchangeRate);
                FIFOAllocationLine."Allocated Amount (ARC)" := (GenLedgEntry."Amount" / ExchangeRateArray[i]);
                InflowAmountArray[i] -= GenLedgEntry."Credit Amount";

                if GenLedgEntry."Debit Amount" > 0 then begin
                    FIFOAllocationLine."Allocated Debit Amount" := GenLedgEntry."Debit Amount";
                    FIFOAllocationLine."Allocated Debit Amount (ACR)" := GenLedgEntry."Source Currency Amount";
                    if GenLedgEntry."Global Dimension 2 Code" <> 'TRF' then begin
                        InflowAmountArray[i] += GenLedgEntry."Debit Amount";
                        FIFOAllocationLine."Allocated Debit Amount (ACR)" := GenLedgEntry."Debit Amount" / ExchangeRateArray[i];
                    end;
                end;

                FIFOAllocationLine.Insert();
            end;
        end;
    end;

This is the function that "copies" the GL lines to the new table and splits them if necessary. I have noticed some very weird behavior around the splitting:

The very first time there is a split, the separate records show correctly but the 2 immediate records after that do not show. Further down, at the second occurrence of a split, the split records do not show at all, along with one other normal record.

I can't seem to figure out what's happening. I am not using a temporary table (I empty the table and recreate the lines every time the report is generated), so I created a page in order to view what was bring inserted. I can see that all lines were correctly inserted. What am I missing?

        addlast("G/L Entry")
        {
            dataitem("FIFO Allocation Line"; "FIFO Allocation Line")
            {
                DataItemLink = "Source Entry No." = field("Entry No.");
                DataItemTableView = sorting("Source Entry No.", "Entry No.");
                // RequestFilterFields = "Posting Date";

                column(FIFOAllocatedCreditAmount;
                "Allocated Credit Amount")
                {
                }
                column(FIFOAllocatedCreditAmountACR; "Allocated Credit Amount (ACR)")
                {
                }
                column(Allocated_Debit_Amount; "Allocated Debit Amount")
                {
                }
                column(Allocated_Debit_Amount_ACR; "Allocated Debit Amount (ACR)")
                {
                }
                column(FIFOPostingDate; "Posting Date")
                {
                }
                column(FIFOEntryNo; "Entry No.")
                {
                }
                column(FIFODocumentNo; "Document No.")
                {
                }
                column(FIFOBankSummaryCode; "Global Dimension 2 Code")
                {
                }
                column(FIFODescription; Description)
                {
                }
                column(FIFOGLBalance; FIFOGLBalance)
                {
                }
                column(FIFOStartBalance; FIFOStartBalance)
                {
                }
                column(FIFOGLBalanceARC; FIFOGLBalanceARC)
                {
                }

                trigger OnAfterGetRecord()
                begin
                    FIFOGLBalance += "Allocated Amount";
                    FIFOGLBalanceARC += "Allocated Amount (ARC)";
                end;

                trigger OnPreDataItem()
                begin
                end;
            }
        }

Categories:

Dynamics 365 Business Central