Tips and Tricks – Page 7 – Marije Brummel Blog | Business Central, Azure and more…

Tip #39 | Pipe Filter with Excel | Alternative

I just looked at the video by Kerry Rosvold on how to make a pipe filter in excel. It’s awesome. You can watch it here.

Now, I have an alternative way that I have been using for the last many many years and I don’t understand why I never shared it.

Blogging it now kinda make me a smart-ass. Maybe I am that. I apologize. 😉

So this is how I do it.

You copy a few lines to excel like this:

And in Excel you paste, and copy the number column

And you paste/transpose and copy the results again

Then you open a notepad, paste the rows and copy the tab character

Then you select find/replace, paste the tab character and replace with the pipe sign

There you have your filter.

Now you have two options. Maybe there are more.

Thanks again Kerry!

Tip #38 | SETSELECTIONFILTER to SETFILTER

This week I got a question from one of my book readers about how to convert a SETSELECTIONFILTER to a SETFILTER command.

This is something that is implemented in the standard product on a few list pages. The Customer List page (22) is one of them.

The code is handled in codeunit 46, SelectionFilterManagement

This is the code:

 RecRef.OPEN(TempRecRef.NUMBER);
 TempRecRefCount := TempRecRef.COUNT;
 IF TempRecRefCount > 0 THEN BEGIN
   TempRecRef.FIND('-');
   WHILE TempRecRefCount > 0 DO BEGIN
     TempRecRefCount := TempRecRefCount - 1;
     RecRef.SETPOSITION(TempRecRef.GETPOSITION);
     RecRef.FIND;
     FieldRef := RecRef.FIELD(SelectionFieldID);
     FirstRecRef := FORMAT(FieldRef.VALUE);
     LastRecRef := FirstRecRef;
     More := TempRecRefCount > 0;
     WHILE More DO
       IF RecRef.NEXT = 0 THEN
         More := FALSE
       ELSE BEGIN
         SavePos := TempRecRef.GETPOSITION;
         TempRecRef.SETPOSITION(RecRef.GETPOSITION);
         IF NOT TempRecRef.FIND THEN BEGIN
           More := FALSE;
           TempRecRef.SETPOSITION(SavePos);
         END ELSE BEGIN
           FieldRef := RecRef.FIELD(SelectionFieldID);
           LastRecRef := FORMAT(FieldRef.VALUE);
           TempRecRefCount := TempRecRefCount - 1;
           IF TempRecRefCount = 0 THEN
             More := FALSE;
         END;
       END;
       IF SelectionFilter <> '' THEN
         SelectionFilter := SelectionFilter + '|';
       IF FirstRecRef = LastRecRef THEN
         SelectionFilter := SelectionFilter + AddQuotes(FirstRecRef)
       ELSE
         SelectionFilter := SelectionFilter + AddQuotes(FirstRecRef) + '..' + AddQuotes(LastRecRef);
       IF TempRecRefCount > 0 THEN
         TempRecRef.NEXT;
     END;
   EXIT(SelectionFilter);
 END;

In older versions than 2013, this code is on the page/form object.

Enjoy!

Tip #37 | Tracking Shipments from Dynamics NAV [Edited]

NOTE: This blog entry was edited after a comment from one of my readers.

Last week during one of my sessions for the NAVUG Summit one of the attendees asked a question about best practices to track shipments from NAV. The scenario is that you ship via UPS or Fedex and want to see the status. Answering the question, I obviously overdesigned the solution thinking about a factbox with a webservice calling out and displaying the status in the factbox.

Keith Owen came up with a much simpler solution that he already implemented and was willing to share. After publishing the first version of this post I edited it, since I was not aware that this was even a standard feature of NAV.

Hyperlink

Instead of showing the actual status in Dynamics NAV, you run a hyperlink against a predefined URL.

This URL can be stored in the shipping agent table, and from the Sales Shipment you can call the Track Package action.

Thanks Keith!

Tip #26 – Grouping in the HomeItems

Ever noticed the grouping in your homeitems?

I did, and today I decided to find out.

If you go to your Activities page of your role center (in my case the Purchase Agent Activities) you see the cues that are defined. Most cues are flowfields in the source. To open the correct page on clicking they have a DrillDownFormID.

This is where the grouping starts. Please notice the page.

Now let’s go to the Role Center. Mine is 9007, Purchasing Agent Role Center.

This page has a lot of Actions devided into different groups, together creating the user experience. One of these groups is the HomeItems creating the menu items we are looking for.

The first Action of the HomeItems is the Purchase Orders. Let’s look at the properties…

And notice the same page as in the Activities. This is what Dynamics NAV is using for the grouping. See what happens if I remove it?

The grouping is completely gone!

Enjoy!

Tip #27 – Using Temp tables in Arrays

I’ll be honest, I found this out by accident lately but I want to share it anyway. Since it took me almost 15 years to find, it might not be know to some others.

As many of you know, using temporary tables in Microsoft Dynamics NAV is a very powerfull feature. We can use it to buffer data, to simulate SELECT DISTINCT etc. And they are free.

Using temporary tables becomes even more powerfull when adding an array to it. Let’s see this in an example.

We’ll create a new codeunit with a variable CustTemp that has two dimensions.

So now we can use CustTemp[1] and CustTemp[2].

Let’s write some C/AL Code with this.

We create a new record using CustTemp[1]. We then try a FINDFIRST on CustTemp[2]. Let’s see what message we get.

How cool is this! The contents of the CustTemp[2] is the same as that of CustTemp[1].

So what can we do with this. Let’s add some more code.

We add a new record to CustTemp[2] and filter on this value using CustTemp[1]. Then we show the value of Name and the number of records in CustTemp[2].

What does this give?

The CustTemp[1] now has the value of the Name we insterted in CustTemp[2]. CustTemp[2] ignores the filter we used in CustTemp[1].

Ok Mark, great stuff but what can I do with this?

Microsoft uses this in at least two places in the standard Navision product.

The fist place is the UpdInvPostingBuffer() function in Codeunits 80 and 90. This temporary buffer variable has two dimensions which are used to combine a select distinct with a total of the values.

The second place is the Inventory Profile Offsetting Codeunit (99000854) in Navision Manufacturing. In this codeunit supply and demand is created in the InventoryProfile table.

Cheers,

/Marq

Tip #14 – Autoincrement Interaction Log Entries

Interaction Log entries are a very powerfull feature of Dynamics NAV. From everywhere in NAV you can add an interaction log entry if you print a report and from withing the CRM part of NAV you can register all other interactions like emails, phonecalls, mailings etc.

Interactions can be related to a path of multiple to-do’s that can be sinchronised with Outlook.

Technicaly the interaction log entry table is clustered by an entry no. Like al Entry tables in NAV this handled manualy by C/AL code. This means that every time someone creates a new interaction log entry, the last record in the table is locked, thus isolating all transactions creating a new record.

SQL Server can also maintain the entry number for you. This is built in technilogy called Identity Insert.

To activate this feature for a NAV table change the property Auto Increment of the field.

Every table can have one field with this feature. This can be (part of) the primairy key or not.

If you enable this field you will encounter two typical issues. The first issue you will notice is that if you create a new entry the value of the autoincrement field has to be set to zero. The other issue is that if an error occurs during the transaction, the entry number will be skipped, leading to gaps in the numbering.

If the latter is not a functional issue then the first can be solved by rewriting some code. For Interaction log entries this is just two places. I will let you figure out those places yourself. Stick out tongue

Tip #23 – The ‘Return’ Shortcut

Today I spent some time at a customer creating a new role center for Dynamics NAV CRM users.

One of the challenges we had was to create a My To-Do list on the Role Center that was based on the actual table. All went well until we started to test and compare to My Customers.

We could not get the double click on the lines to open the To-Do card.

So we started to ‘Find The Differences’ process. It is like playing the picture game with my kids.

Then, after trying out a few things we found this in the My Customer action properties…

Amazing isn’t it! Because return (hitting the Enter key) is the same as double clicking the line it works. [:P]

So now, let’s implement one of my favorite features.

Go to the General Ledger Entries page (20) and open the actions.

And make Navigate the default action!

So now when I double click an entry it will Navigate.

Dear Microsoft, can you please implement this. [8-|]

Enjoy…

Tip #36 | Using Mark and MarkedOnly in the Role Tailored Client

I always wanted to blog about myself, but humble as I am I never got around to do that. 😉

But now I have an excuse.

When the Role Tailored Client was introduced in 2009 one of the things that is missing from the UI was the ability to Mark some records and filter on Marked Only records. This is usefull when you want a subset of your data with records that have no common value to filter on.

In the Classic Client, now Development Environment, you can find the functions here:

However, this feature was discontinued from the IU, but not from C/AL.

In the C/AL Symbol Menu it is still possible to Mark records and filter them.

So let’s see what happens if we use that in a Page step-by-step. We’ll use the Customer List (Page 22)

Step 1 – Add a new Action Category (Optional)

To make it easy for end users to find the options we’ll start by adding a category for the actions we are going to add later.

Note that in order to add a new Category you need to also add the default ones. In this case we will work with Category4.

Step 2 – Add Actions with the correct properties

We add three actions in the ActionItems category

And we assign a Name, Caption, Image and PromotedCategory. I also choose to make them big and promoted.

Step 3 – Add C/AL Code

We add the C/AL commands to the actions

Note that we use the reverse value to toggle the values on and off

Step 4 – Add a column indicator

To make is easy for users to see if a record is marked or not we add a new column to the page with Mark as the source expression

The Result

When we run the page you see that we can Mark records which will be indicated in the new column, filter on them and easily clear the Marks.

TIP: Copy/Paste

Since we did not use any variables or functions we can just copy/paste the actions around to any other page, as long as the ActionCategoryML does not conflict with existing values.

Enjoy

Tip #21 – Implementing No. Series

Editors note: This tip was brought forward from my previous blog and is still as actual as it was back then. Meanwhile the Design Patterns initiative have posted a similar walkthrough on their site.

Number series is a great feature in Navision that allows you to use all kids of numbering methods easilty and also allows the application to work the same everythere,

So how do I implement Number Series in a new table. Let’s go through that step by step. For this example we will use the Item table to copy and paste from.

Step 1 – Create the table.

A table that has number series has a minimum of two fields. A primairy key field of type code length 20. But also a second field to store the number series. This is a code field as well with length 10.

Go to the object designer and create a new Table. The first field will be created manualy like this.

Please call it No. and not something else or otherwise our copy paste actions will fail.

Secondly we will open the item table in the designer as well. Find the No. Series field and copy/paste this.

Now our table looks like this.

Step 2 – Add the code

When you implement number series in your table, there are three required pieces of code. One in the OnInsert trigger, One in the OnValidate of the No. field and a new function that is always called OnAssistedit.

We will copy and paste this code from the item table like this.

When you are done with the copy/paste action your new table should look like this.

Step 3 – The variables

When you now compile your new table you will get an error message like this.

This is because we have not yet declared the nessesairy variables. We will need two of them, the Inventory Setup table and the NoSeriesManagement codeunit. We can also copy and paste this from the item table.

So our variables are like this.

Please notice that you will still receive the same error after this.

Step 4 – Code changes

In order to have the code compile we have to change it a little bit. The code we copied is reading the inventory setup in a function but that is not nessesariy in our case. We will just read it directly.

So go to the C/AL code and do a find and replace on GetInvtSetup by InvtSetup.GET like this.

Step 5 – Save the table (Twice).

Now we need to save the table, but when we try we’ll get this error.

The reason for this is simple to explain. We have created a new field (No. Series) and started using that field in code. But because the table was not saved in between, the compiler does not know that field yet. To solve this we need to save the table without compiling.

And then compile the table from the object designer.

Step 6 – Create your own number series.

If you look closely at the code we just created you’ll see that it uses the field Item Nos. from the inventory setup table. There is a high chance that you will want to use a number series of your own so let’s create one. We will do this in the inventory setup table as well but this can also be any other setup table in Navision.

Open the Inventory Setup table in the obect designer and copy the Item Nos. field like this

And paste it as number 50000. And rename to Test Nos.

We will also add this to the Inventory Setup Form or page

Notice that we create a new (fast) tab for our field. This is best practice.

Run the form or page and populate the number series.

Step 7 – Change the C/AL code

To use this new number series we need to go back to the code in our new table and do another find and replace action. This time replace Item Nos by Test Nos.

And save the table.

Step 8 – The User Interface

Last step in implementing the Number Series functionality is adding it to the user interface. This is done via the OnAssistedit trigger. We will demonstrate both forms and pages.

Create a new form with the form wizzard or page with the page wizzard.

Now go to the code behind the No. field by focussing on the field and press F9. You will see the four triggers, OnLookup, OnValidate, OnDrilldown and OnAssistedit like this.

Our code will go into the OnAssistedit trigger of both the form and page and is copied from form 30. The Item card.

Now when we run the page or form you’ll see that it works and how easy it was to implement.

enjoy…

/Marq

TIP #35 | Using Queries in Pages & Reports

This post is an extention of the post I did yesterday about Queries and performance.

As I said Queries deserve more attention. Unfortunately this is another post about what Queries cannot be used for, but with a workaround.

Whenever you want to make a report or page (or form in the old days) show data from two or more tables in one view you run into a challenge. Even though reports allow complex datasets they are easier to design if your dataset is single layer.

Technically it has always been possible to link a table object in NAV to a SQL Server view allowing you to join tables on SQL Server and have a single view in NAV. I never bloged about this cause I’m not a huge fan of this but more information can be found here on MSDN.

The first question that I can remember being in a session at MDCC about queries few years ago after the applause was: “Can we use this as a source for a page or a report?”. The answer was, “That would be great but is not within the scope of the current project”. And today after the release (soon) of R2 this is still the case, it is out of scope and my guess is it will be for a while.

The single and most important reason queries are here is to avoid the loopy-loopy code patterns.

But what if we want to use the query as a source for pages or reports. Well, that solution was presented during that same session at MDCC within minutes: “Then you use the integer table”.

And yes, that would be the solution. Or at least one solution, there is a second more elegant solution.

Let’s discuss the integer solution first.

An example of using the integer table for reporting has always been report 111 Customer – Top 10 List. This report allows you to show the top X customers by sales in your system. X defines the number of loops over the integer table.

As you might (or should) know the integer table is a system table (2000000026) that has been there forever allowing you to build repeat code Patterns. A similar table is the Date table (2000000007).

Writing this I realise that these system tables deserve their own blog article too. Stick out tongue

So let’s start with a cool query that we will use in our page.

The query combines sales information from the Value entries per customer. Now how do we show this in a page.

First step is to create a new listpage with the wizard based on the Integer table like this:

When we are in the page designer the next step is to add the query as a global variable and add a counter variable. Then we add all the query fields to the page and write some code as displayed

You can immediately see that this is not a real nice solution cause we loop at least twice trough the query. This is to get the number of rows to filter on the integer table.

When we run this page on Cronus we see this result:

And yes, we are happy and dancing.

But,

Try page up and down a little now..

Ups…

Now you might have a solution but I could not find one. And there are more issues with this way of showing a query in a page, we cannot filter, we cannot sort.

So what is the solution?

SourceTableTemporary

Quite a few years ago I have blogged about this property. And the power still counts.

But Mark, are you saying we should create a table now? Yes you should. But, that is an extra object! I know but it is free. Tables that are only used temporary do not need to be in a customers license.

So let’s continue.

First we create the table, with Entry No. as primairy key

Remember this table is free of charge, no license required. We only use it as buffer.

Step 2 is to create a new page on this table and toggle SourceTableTemporary to Yes.

And we add a very small amount of code

And this code we run from the OnOpenPage trigger and voila: this is the result:

All the gizmo’s work like filtering and the new interactive sort on all columns that is introduced in NAV 2013 R2

Hope this post was not to long.

Enjoy!

Advertisement: I do Tips & Tricks workshops and What’s new training! Send me an email if you are interested or fill out this contact form!

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Tip #27 – Using Temp tables in Arrays

Share this:

Like this:

Tip #14 – Autoincrement Interaction Log Entries

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

TIP #35 | Using Queries in Pages & Reports

Share this:

Like this: