Danjo's two cents NAV, Powershell, Android, C#, Lync, Win8, …

13Feb/130

NAV – Some basic functions #1

When working with Data in Microsoft Dynamics NAV, there are some functions you do not need often, but if you need them, you will probably need to run them on many tables at once.

One example is truncating all the data in some tables. Yes, there is a function for that: DELETEALL.

But it is a lot of work to define a bunch of variables for the tables, name them in a speaking maner and use DELETEALL on the variable

Here is what you could do instead:

Use a new or existing Codeunit "Basic Functions" and include this function:

Now you just need to run this function.

It could look like this:

Basics.TruncateTable(50000);

Basics.TruncateTable(50001);

Basics.TruncateTable(50002);

Basics.TruncateTable(50003);

Basics.TruncateTable(50004);

...

Tagged as: No Comments
23Jan/130

Time for numbers – Annual Report

Two weeks ago wordpress.com released the annual reports for blogs using jetpack.

Here is a short summary of the facts for Danjo's 2 cents:

- about 1400 views

- visitors from 53 countries

- 51 new posts

Follow this link if you want to see the official version:

 

http://jetpack.me/annual-report/34636280/2012/

 

Filed under: other No Comments
30Oct/120

Dynamics Techincal Airlift [Germany] A look at the SQL-Side of Querys

During my airlift presentation I have shown a SQL-Statement which was captured using sql profiler during runtime of a query-object.

This query brings the same results as TOP10-Customer-Report, but in shorter time.

Here is what the statement behind the query looks like.

SELECT TOP (10) ISNULL("Customer"."No_",@0) AS "No",ISNULL("Customer"."Name",@1) AS "Name",ISNULL(SUM("SUB$Customer$Sales (LCY)"."Sales (LCY)$Cust_ Ledger Entry$SUM$Sales (LCY)"),@3) AS "Sum_Sales_LCY",ISNULL(SUM("SUB$Customer$Balance (LCY)"."Balance (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)"),@5) AS "Sum_Balance_LCY" FROM "Demo Database NAV (7-0)"."dbo"."CRONUS AG$Customer" AS "Customer" WITH(READUNCOMMITTED)

OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Sales (LCY)$Cust_ Ledger Entry"."Sales (LCY)"),@2) AS "Sales (LCY)$Cust_ Ledger Entry$SUM$Sales (LCY)" FROM "Demo Database NAV (7-0)"."dbo"."CRONUS AG$Cust_ Ledger Entry" AS "Sales (LCY)$Cust_ Ledger Entry" WITH(READUNCOMMITTED)  WHERE ("Sales (LCY)$Cust_ Ledger Entry"."Customer No_"="Customer"."No_")) AS "SUB$Customer$Sales (LCY)"

OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Balance (LCY)$Detailed Cust_ Ledg_ Entry"."Amount (LCY)"),@4) AS "Balance (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)" FROM "Demo Database NAV (7-0)"."dbo"."CRONUS AG$Detailed Cust_ Ledg_ Entry" AS "Balance (LCY)$Detailed Cust_ Ledg_ Entry" WITH(READUNCOMMITTED)  WHERE ("Balance (LCY)$Detailed Cust_ Ledg_ Entry"."Customer No_"="Customer"."No_")) AS "SUB$Customer$Balance (LCY)"

ORDER BY "Sales_LCY" DESC,"No" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50, FORCE ORDER, LOOP JOIN)

This example shows how the query-object increases performance by the usage of TOP, nested statements and SUM.

23Oct/120

NAV2013 – How to access OData

Did you already try out the new possibilities of OData coming with Microsoft Dynamics NAV 2013?

Here is a quick summary of what you can do when accessing OData webservice.

All examples can be done with default installation of Cronus and publishing Page 22 as a webservice with the name Customer.

 

Show all OData-feeds:

http://localhost:7048/DynamicsNAV70/odata

Show Metadata:

http://localhost:7048/DynamicsNAV70/odata/$metadata

Show all customers:

http://localhost:7048/DynamicsNAV70/odata/Customer

Show customer with the primary key 10000 (using key):

http://localhost:7048/DynamicsNAV70/odata/Customer('10000')

Show all customers with Location Code BLUE (using field-filter):

http://localhost:7048/DynamicsNAV70/odata/Customer?$filter=Location_Code eq 'BLUE'

Show Name, Location Code and Credit Limit (LCY) from customers (selecting a couple of fields only):

http://localhost:7048/DynamicsNAV70/odata/Customer?$select=Name, Location_Code, Credit_Limit_LCY

Show Name, Location Cod and Credit Limit (LCY) for customers with Location Code BLUE (selection and filter):

http://localhost:7048/DynamicsNAV70/odata/Customer?$filter=Location_Code eq 'BLUE' &$select=Name, Location_Code, Credit_Limit_LCY

%d bloggers like this: