Simulate DEX_ROW_ID in a SQL View Using ROW_NUMBER() – #MSDYNGP


I have a requirement in which I have to access a SQL view from within my customisation dictionary, in order to create a custom lookup for users to select a value based on an Extender form and an Extender lookup. Easiest option is to create an Extender view (which in turn creates a SQL view for us).

Now, this is the view that I am suppose to refer to from my custom dictionary. Dexterity allows us to refer to any SQL object by simply create a TABLE definition and mention the SQL object (table or view) name as the physical name.

Dex View

Everything looks perfect till you actually see below error messages at runtime:

Dex View - Error 1

Dex View - Error 2

Error message is quite obvious; you do not have DEX_ROW_ID in that SQL view that you are referring to. Every single Dexterity table must have DEX_ROW_ID at the backend. It cannot afford to not have one.

So how am I going to resolve this? By simply adding a record number dynamically to the SQL view created by Extender. How to do that? By adding the T-SQL function ROW_NUMBER(). This is how I achieved it:

ROW_NUMBER USAGE

 

Definition of ROW_NUMBER() can be found here: ROW_NUMBER (Transact-SQL).

A simple yet powerful SQL function has given me the power to do what I wanted in no time. Oh, and my custom lookup referring to this view is working like a charm. Users are happy and so am I.

VAIDY

Analytics For Dynamics GP – BI Solution From Mark Polino (DynamicAccounting.Net) #MSDYNGP


Mark Polino of DynamicAccounting.Net has released first among series of Excel Dashboards, Sales Analytics Starter Edition, for Dynamics GP. This dashboard series is named as Analytics For Dynamics GP.

Sales Dashboard

Sales Starter Edition has got following charts with whole set of important filters:

  • Top Customers (with costs)
  • Top 10 Products (with costs)
  • Top 10 Salespeople
  • Top 10 Sales Territories
  • Top 10 Sites
  • Sales with Trend and Projection
  • Sales Mix with Item Class

It’s a simple plug and play kind of dashboard on Microsoft Excel (v2010 & v2013) file. Setting this up once you buy it is so very easy. How to map your GP data server is clearly explained as part of this dashboard solution. And you can modify this as you wish once you link this to your GP data server.

As it is just an Excel file with a straightforward GP data server connection, all you need to do is to click on refresh to get the real time information.

This sales dashboard starter edition is priced at $249 +$49 annual maintenance. Maintenance covers updates, improvements and fixes. Click on links provided across this post to visit Mark’s Analytics For Dynamics GP product page, learn more about this awesome BI solution and know how to buy your copy.

In my personal opinion, this is a simplistic and satisfactory BI solution which meet its promises pretty well.

VAIDY

#MSDYNGP Extender View Involving Extender Window/Form – Strange “Add Link” Issue & Reason.


Firstly, I am not sure whether I should categorise this as an issue. Let me explain this with an example.

For illustrative purpose, my requirement is to assign an additional information to customers. In addition to Country Code, I would like to assign from which continent a customer is from.

First step; to create an Extender Form named Continent Maintenance in which I will maintain list of continents. I do not need anything information other than an ID (Continent ID) and a name (Name); as shown below:

Snip20140114_1

Note that there are no fields apart from ID and Description.

Second step; I am going to create an Extender Window for Customer Maintenance (Cards -> Sales -> Customer -> Additional -> Continent) to assign a continent to a customer. Extender Window definition is as follows:

Snip20140114_2

Third step; is to check whether things are properly done and confirm. Let me open Customer Maintenance and see if I can access this new Extender information:

Snip20140114_3

Perfect. Let’s now get into the actual issue.

I would like to create a view to retrieve customer continent information. To achieve this, I would like to link Assign Continent (Extender Window) with Continent Maintenance (Extender Form) to get the continent name. Ideally, my view should retrieve following:

Customer ID, Continent ID, Continent Name

Now, when I try to create an Extender View linking my Extender Window and Form, I end up facing below issue:

Snip20140114_4

Snip20140114_6

Did you see that? I do not have my Extender Form fields shown here. I have two fields; Continent ID and Name. Where are they? Why are they not shown here? Shouldn’t it be available for me to link with my Extender Window’s Continent ID?

REASON: If your Extender Form DOES NOT have any other field than an ID and a Description (in my case, Continent ID and Name), ADD LINK To Field will not list out the ID and Description fields.

Is that the actual reason? Let’s confirm by adding another field to Extender Form as follows:

Snip20140114_7

After adding above field, Additional Info., look at my Extender View Add Link now:

Snip20140114_8

Did you see that? They are available now. AFTER adding a field in addition to default ID and Description fields.

And I am not sure how many have ever noticed this. I am noticing it for the first time now. I haven’t created any form with only ID and Description till now. I had to spend 4 hours to identify this reason, honestly. Had no idea whatsoever.

Those who are going to deal with Extender views with form(s) having ONLY ID and Description fields, save your 4 hours. :-)

VAIDY

Management Reporter Configuration – Part Of Domain Error


I am not sure how many would have noticed this error message. Just thought of sharing this with you all.

I installed Management Reporter 2012 CU7 on my machine yesterday night for some testing. I had not done this outside a network (being physically away from a domain) before, so never expected an error. But following error message popped up:

Snip20140106_4

 

My machine is already part of a domain. So above message was a bit confusing. Later I realised that I MUST be connected to my domain when the configuration process is run.

Which means the machine on which you are installing Management Reporter Server component CANNOT be physically outside the domain (even though it is already added to one) when you configure it.

This morning I restarted the configuration process and it’s done without issues. Interesting.

VAIDY

Mission MB3-701 Accomplished (Microsoft Dynamics GP 2013 – Financials) #MSDYNGP


MS_2013(rgb)_1511

There is no better way than this to end an year and begin one. Biggest achievement in recent times; without doubt.

After passing MB3-700 (Microsoft Dynamics GP 2013 Installation & Configuration), it sounded quite logical to take the next step, MB3-701 (Microsoft Dynamics GP 2013 Financials). Got damn nervous upon reaching the exam schedule. Stressed, tired, overloaded.

I have ample experience in implementing GP, so clearing MB3-700 was not that difficult. But Financials, MB3-701, is different ball game altogether. I had no idea how questions would be asked; 45 questions from 6 modules (System & Company Configuration, General Ledger, Bank Management, Payables Management, Receivables Management & Fixed Assets). There are no online/classroom training courses available yet. After another strenuous 2 weeks of preparation, stress and tiredness, here I am; feeling like standing on top of this world. :-)

Mission MB3-701 Microsoft Dynamics GP 2013 Financials Accomplished.

I am now officially Microsoft Specialist: Microsoft Dynamics GP 2013. I started 2013 as usual, like any other year. But I have ended it on the highest possible note. I have never been this proud of my own achievements before. This one is so damn special.

Welcome 2014.

VAIDY

GP Web Client: Rendering Issue – Some Facts


Almost a month back, I had posted my GP web client test drive results on how the client is rendered on Mac based browsers and possible issue with Silverlight plugin. I am probably wrong.

Everything works other than pictures; that’s what I had found. Upon drilling down further, what I realised is that it sounds obvious that it doesn’t work on Mac based browsers. Reason: Native Pictures.

Definition of Native Picture says following:

Snip20131230_12

Consider, for instance, the following snapshot of GP login window on a web client rendered on Mac Safari:

Snip20131230_10

It’s not shown. Initially I thought it was something to do with Silverlight rendering. But not exactly. It’s because, this picture is a Native Picture. And by definition, it’s specific to Windows OS. Look at this picture definition below:

Snip20131230_9

Apparently, by nature, it’s NOT supposed to show up on any OS other than Windows.

It’s not just this picture. Lookup Button icons, Note icons are all Native Picture types. And due to that, they are not going to render on any other OS. And if I am not mistaken, this will remain as it is at least till next major version of GP.

Those who implement GP web client MUST be aware of this.

VAIDY

Where Have #MSDYNGP Product Printable Manuals Gone?


I am not sure how many of us actually refer to GP user manuals; the ones that come in PDF format and contain module functionalities. But I do, religiously. When I came to know that they are not available offline anymore, I was a bit upset. After a bit of traversing here and there, I have finally found the location of them.

Take a look at below screenshot, which compares GP 2010 Printable Manuals menu and GP 2013 Printable Manuals menu:

Snip20131224_3

 

Apparently, when you click on that “Documentation and resources for Microsoft Dynamics GP” link from GP 2013 Printable Manuals window, it takes you to this webpage:

Snip20131224_4

 

And click on Documentation and resources for Microsoft Dynamics GP 2013 (highlighted above with RED box), you will be taken to following link: http://technet.microsoft.com/en-us/library/jj673201(v=gp.20).aspx.

From there, locate Printable Guides [GP 2013] as shown below:

Snip20131224_5

Once you click that, you will be taken to following link from where you can find all your module manuals: Printable Guides.

Biggest hassle is to click on each link found on this page, being taken to another page and from there download the PDF and it literally kill us. If they are available offline already on GP applications folder, like how it used to be before, it would have been awesome.

Never realised that Microsoft would put them all online and not plant them on our GP application folder anymore. Strange strategy.

VAIDY