Dynamics GP - SQL script to revert multiple bins into single bins

So you want to turn off multiple binning in Dynamics GP and need to record the multiple bin where the stock is held back into the basic single bin field against each location?

This script I just used to do that, it will pick the multiple bin with the most stock as the one to use for the single bin field. I would take a snapshot of the multiple bins table into excel, just for the record if performing this change (IV00112) and for future reference.

WITH CTE
AS (
SELECT ITEMNMBR
,LOCNCODE
,BIN
,ROW_NUMBER() OVER (
PARTITION BY ITEMNMBR
,LOCNCODE ORDER BY QUANTITY DESC
) RowSort
FROM iv00112
WHERE qtytype = 1
)
UPDATE IV00102
SET BINNMBR=BIN

--SELECT CTE.ITEMNMBR
-- ,CTE.LOCNCODE
-- ,BIN
-- ,BINNMBR
FROM CTE
JOIN IV00102 ON CTE.ITEMNMBR=IV00102.ITEMNMBR AND iv00102.LOCNCODE=CTE.LOCNCODE
WHERE RowSort = 1
AND BIN !=BINNMBR
 
Then, turn off multiple bins in the inventory setup window, and reconcile the inventory after doing so.

Naming confusion may be compounded by Enterprise and Business names for Dynamics products

We almost settle on one set of names for the realigned Dynamics ERP and CRM products when another set of product names seem to be upon us.

As Steve Endow points out, the CRM community seem to have claimed the “Dynamics 365” moniker for themselves (just see all the twitter activity and company marketing emails for proof), whilst the Dynamics ERP community are left with the scratty end of the deal when it comes to naming.

As the ERP products are migrated and morphed into new cloud & on-premises solutions, new names are certainly required to describe the new shape of offerings. The table below shows product names, previous to June 2017, with “Operations” and “Financials” differentiating the reinvention and re-imagination of what were the traditional Microsoft Dynamics AX and NAV offerings, respectively.

Current Name New name (June 2017)
Dynamics 365 for Operations Dynamics 365 for Finance and Operations Enterprise edition
Dynamics 365 for Financials Dynamics 365 for Finance and Operations Business edition

 

As the Financials product development has gathered pace, it is now obvious that it will eventually encompass the same (to all intents and purposes) set of functionality as the current NAV on-premise solution does today, thus inventory, warehouse management, manufacturing and more. Obviously this scope breaks far beyond a simple accounting function inferred by the “financials” naming and thus I can understand the need to re-frame the product name.

Enterprise and Business edition

So it is June 2017 it seems in rolls Enterprise and Business edition naming. This is to show the scale difference between the companies that the two solutions are targeted at, whilst attempting to also maintain the marketing message that they belong firmly to the same Dynamics 365 family tree. The name also tells the customer that the solutions will do everything they need, both finance functions and run the company operationally.Whilst the naming is more appropriate I have a small alarm to ring with the choice of Enterprise and Business as words to show scale. Todd McDaniel in the recent Enterprise Software Podcast pointed out that outside the software community Enterprise and Business do not perhaps indicate the scale of a company, as a marketing device this may not work as intended. I have to agree, in the United Kingdom this is even worse.

In the USA smaller to mid size companies are usually referred to as SMB - Small and Midsize Business, you see and hear this term all the time.
In European Union and international organizations such as the World Bank, the United Nations and the World Trade Organization, the term SME is used - Small and Medium Enterprise.

SMB Small and Midsize Business
SME Small and Medium Enterprise

 

SME is the term you hear all the time in the United Kingdom where you would use SMB in the US. Can you see the problem? Indeed Business and don’t Enterprise differentiate the scale of a company in our territory! -Whoops!

Although this is not a show stopper, it does mean companies selling the ERP orientated D365 solutions will have a little extra work cut out for them in other parts of the world as the significance of the meaning for Enterprise and Business will need to be explained to prospective customer CEOs, CFOs in the pre-sales marketing literature and meetings.

Uppercase in computer records–please no!

From time to time you encounter systems where the company style is to capitalise names in the system. I know of users who might as well glue the caps-lock on!

When to use a capital letter

If faced with the, we have always done it this way problem, fight it. Excuses come out like its for users with vision problems (there are magnifiers and system tools to help there), improves clarity for parcels going overseas to other cultures (I doubt it makes much difference in compter type, maybe for hand written?), etc.

Capitalisation is a lossy process

In the context of company names, street names, people names, the capitalisation can be important as it conveys information that is lost if it is presented in entirely uppercase. 
examples:

  • McKenzie vs MCKENZIE
  • O'Donnell vs O'DONNEL
  • MacBain VS MACBAIN

There are many others and in other cultures where the capitalisation conveys information that cannot be recovered again if lose, by the use of automated processing.

Mail Merge

It makes it look bad for mail merges later too

"Dear Mr ODONNEL

Thank you for your order."

Just looks bad.

Thus don’t shout at your customers with capitals, use your SHIFT keys!

Dynamics GP–Clicking “on order” in inventory enquiry shows blank empty with no purchase orders

Seen this from three users recently. The issue is that the user clicks the on order hyperlink text in item enquiry to see the purchase orders in Purchase Order Processing Item Enquiry, but nothing is shown in the window.

Purchase Order Processing Item Enquiry

Notice at the bottom it says processing. I assume that this window is trying to populate on another thread to keep it responsive and that thread is never returning the results?

Going to the GP process monitor in this example showed that it was clogged up with a posting process that was not budging.

Process Monitor

Once the user was cleared down, previous transaction level posting error dealt with and looked again, everything was working as expected.

I also have seen where doing this same thing but from a link I provide in another window, that calls the same enquiry window causes it to work. Weird! I can only think there might be two different methods being called, one with background and the other not? When I call it in my code I’m getting the foreground, when GP does it, it uses the background?

I’m guessing at all this, but thought it worth documenting the findings so far, I will come back to update this post if I find any root causes.