How to export Customer trial balance to Excel by Query????

benjamint

Dear All,

Please help me with the following problem:

I want to export the customer trial balance to Excel with a Query (Microsoft Query), so it can be refreshed with 1 click. I already tried to link several tables, but unfortunately I didn't succeed.

This is the code by the way:

Customer.SETRANGE("Date Filter",0D,DateFrom - 1);
Customer.CALCFIELDS("Net Change (LCY)");
BeginBalance := Customer."Net Change (LCY)";

WITH DtlCustLedgEntry DO BEGIN
SETCURRENTKEY("Customer No.","Posting Date","Entry Type","Currency Code");
SETRANGE("Customer No.",Customer."No.");
SETRANGE("Posting Date",DateFrom,DateTo);
SETFILTER("Entry Type",'<> %1',"Entry Type"::Application);
CALCSUMS("Debit Amount (LCY)","Credit Amount (LCY)");
DebitAmt := "Debit Amount (LCY)";
CreditAmt := "Credit Amount (LCY)";
TotalBalance := BeginBalance + DebitAmt - CreditAmt;
END;

THANKS IN ADVANCE

ufuk

You can write SQL in MS query. So this is simply a SQL question. All the values you'll calculate exist on Detailed Cust. Ledger Entry table. You can write a query summing related amounts filtered by the Entry Type and Posting Date values. (As far as I remember there was a tool in mibuso which converts nav code, flowfield calculation etc. to sql, I think it can help you a lot)