account balance

Hi

I want to design in crystal reports an customer’s account balance, something like this

I need an sql for Date, Invoice, Description, Charges, Credits and Total. I’m using navision 4.0 sql server 2005.

Thanks.

Best regards.

Hi Jonatan, and welcome to forum!

Looks like your’e more familiar with Crystal and MSSQL, than Navision and its data structures…

Exact T-SQL script nobody from outside can give you, but here are some hints:

First, in SQL DB every Company in DB has its own set of all (almost) tables, thus every table is named in this manner: [Company Name$Table Name]. So if you have more than one Company in your DB, FROM clauses can’t be hard-coded, which means even SPs are recompiled before each run, as you need to use variables in table names.

Now, Navision side. Never build reports on Document tables as Orders, Invoices, etc etc - use ONLY corresponding Ledger tables instead, in this exact case it should be Customer Ledger Entry and maybe you’ll need Detailed Cust Ledger Entry and some other, depending of your reporting needs.

Exact Table & Field names you can see in SSMS, they are descriptive enough, but ask for help some colleague nearby, who knows Navision DB structure. Foreseeing your possible next question - no, there is not a document available describing Navision DB schema in detail, only generic schema of each application area was somewhere in App Designer’s Guide.

Thank you very much. I ask for help and now I have almost all info I need.

I have this:

Select * from [Cust_ Ledger Entry] where [Customer No_] = ‘C-1003-01’

It shows a list of invoices, payments, etc. But it isn’t the same information that it’s inside Navision.

In Navision, Object Designer, Table, when browsing “Cust_ Ledger Entry” table it shows 10 extra columns.

I review triggers from Cust_ Ledger Entry and it creates 4 extra tables called $21$0, $21$1, $21$2 and $21$3 with following columns:

bucket int (values are 1,2,3,4,5) PK

f3 varchar (values are customers no.) PK

f23 datetime

s18,s16 decimal (values are amounts)

But I don’t see any foreign key related to Cust_ Ledger Entry, so I don’t how to join those tables with Cust_ Ledger Entry.

Any idea for joining that tables?

Best regards

You really need someone near you, who can explain Navision DB intricacies - this is far too wide theme to answer here in single post [:)]

“Extra” fields are what in Navision is called Flowfields. (in short - summarized data storage for faster access)

They do not (and can not) exist in SQL DB tables, it’s in Native only. Tables with strange names are SQL DB substitute for these calculated fields, but begining with ver5 SP1 even these are replaced by Indexed Views.

Think of them as subselect clauses - e.g. “SELECT SUM(someamount) from (more detailed entries table) where…” – and not only sum, it can be lookup, etc.etc.

You don’t need to these $xx$y tables, instead, from Navision’s Object Designer examine table’s definition - in Flowfield’s properties you can find almost SQL-compliant definition of such “field” and understand how to “transcript” it to SQL subselect clause.

Then there are Option fields (e.g. different “Types”). In SQL these all are integer, for corresponding Option string again look in TableDesigner - in T-SQL personally I substitute such with CASE structure, but tastes differ.

Finally comes the best - value of one field can determine other field’s relation target table… [H]

and as hometask for you - have you noticed that ABSOLUTELY ALL fields in Navision have NOT NULL constraint? Well, what the hell contains empty date field then? And “closing date”, seen in Navisian as “C31/12/09” ?

updated 21/Jan/10 http://dynamicsuser.net/forums/t/30730.aspx check this, too…