Introducing Microsoft Dynamics 365

By Marielena Zajac, Marketing Specialist

It is all anyone in the Microsoft Dynamics community has been discussing. Since Microsoft’s CEO Satya Nadella’s announcement back in July, Microsoft Dynamics 365 has caused quite the stir among partners and customers alike. Dynamics 365 unifies CRM and ERP cloud solutions together into one unified, cloud-based application platform. Nadella’s main takeaways to eager customers were that Microsoft Dynamics 365:

  • Helps organizations move from monolithic application suites to purpose-built, SaaS applications with intelligence built in
  • Integrates Microsoft Office 365
  • Introduces a customer centric business model to build what you want and buy just the capabilities you need, while ensuring all roles have access to all the data they need to be successful in their jobs

All this buzz has consumers asking, “what is Dynamics 365?” and “what does this integration mean for my business?” More importantly, what sets Dynamics 365 apart from the competition?

It’s Purpose-Built

According to Microsoft, Dynamics 365 provides customers a modern and familiar experience with built-in insights, intelligence, and workflow. It allows customers to choose the exact functionality that they need, which can be selected from a huge catalog that include sales, marketing, customer service, field service, project management, accounting, and much more. Dynamics 365 merges current CRM and ERP functionality into one comprehensive, cloud-based collection of apps and services for business.

Dynamics 365 apps are independently deployed and bring together the full power of Microsoft’s enterprise offerings. With these business apps, customers can start small and only pay for what is needed, and from there expand their arsenal.  The app catalog includes built-in insights, predictive intelligence, and workflow optimization, among many other selections. All of these will be delivered through simple, easy-to-use mobile experiences with offline capabilities.

It’s Productive

Dynamics 365 will provide an immersive user experience with Office 365. Microsoft states that there will be a deep integration between Dynamics 365 and Office 365 that will connect the structured workflow of business apps with the unstructured work of collaboration and productivity. With Dynamics 365, all information you have can easily be uploaded and shared on Office 365, and it is offered across web, mobile devices, and the PC.

It’s Intelligent

Power BI and Cortana Intelligence will be natively embedded into Dynamics 365 to attain actionable next steps and predictive insights for customers everywhere, making this the AI product that everyone will be eager to demo. Cortana will enable cross-sell recommendations to help sales reps predict which products and services a customer will need, and uses tools like sentiment analysis to check on the likelihood of closing a deal. Meanwhile, the Power BI components of Dynamics 365 align big data and analytics to enable every user to gain their own analytical insights. With Dynamics 365, comprehensible data is always at the user’s fingertips.

It’s Adaptable

In an industry that is constantly evolving, you need a system that can be easily adapted. Dynamics 365 supports existing capabilities that any Dynamics CRM or Dynamics AX user is running today, and has introduced Microsoft PowerApps and Microsoft Flow, which are powerful new extension capabilities that work across all the data that’s been stored in Dynamics 365. Specifically, Microsoft Flow automates workflows across applications, while Microsoft AppSource offers SaaS applications that cover a huge range of requirements. To view Edgewater Fullscope’s  AppSource apps for the manufacturing industry, click here.

The new Common Data Model surfaces all the data stored within Dynamics 365 across a set of custom entities that model data across all the different business apps. This feature allows you to seamlessly extend and accelerate all of your business apps as well as all your productivity solutions.

With Dynamics 365, customers can get the job done quickly and effortlessly while adjusting to a changing environment at a moment’s notice. As Satya Nadella said during the reveal of Dynamics 365, “The motivating force behind everything we do and everything we build is to empower our customers to make things and make things happen. Together with our customers and partners, we are reinventing business processes for a world where devices, data, intelligence and connectivity can be everywhere.”

An award-winning Microsoft manufacturing partner with nearly two decades of experience, Edgewater Fullscope can guide you into a move to Microsoft Dynamics 365. Our team has deep manufacturing experience that can help you combat your toughest operational business challenges.

For more information or to request a demo of Microsoft Dynamics 365, call at 770-772-3121 or visit

New Blog Series: Keeping Employees Safe with Microsoft Dynamics AX

By Randy Goldbeck, Fullscope Services Director

With manufacturing operations, there is always the possibility of an accident or damage to someone’s health. All work exposes people to hazards, including loads that are manually handled; dangerous machinery; toxic substances; electricity; working with display screen equipment or even psychological hazards such as stress.

The reason there are not more accidents and diseases caused by work is that we have learned a lot over the years, and systems of prevention are in place. Most accidents are preventable. Despite all the precautions, there are still thousands of workplace injuries every year. To control these types of hazards, employers need to develop the necessary expertise, experience, judgement, and initiative within their work force to properly implement and maintain an effective PSM program as envisioned in the Occupational Safety and Health Administration (OSHA) standard.

We have developed a new blog series that focuses on the major elements of a Process Safety Program. It will explore how Microsoft Dynamics AX can help your company setup a Process Safety Management (PSM) system to provide a better, safer work environment.

The major objective of a PSM is to prevent unwanted hazardous conditions that could expose employees and others to serious dangers. An effective PSM program requires a systematic approach to evaluating the whole process. Using this approach, the process design, process technology, process changes, operational and maintenance activities and procedures, non-routine activities and procedures, emergency preparedness plans and procedures, training programs, and other elements that affect the process are considered in the evaluation.

The purpose of the standard as a whole is to aid employers in their efforts to prevent or mitigate hazards that could lead to accidents in the workplace and possibly in the surrounding community.

Check back for future blogs in this series, incuding:

  • Hazards of chemicals used in the Process
  • Technology of the Process.
  • Equipment in the Process. Employee Involvement
  • Process Hazard Analysis Operating Procedures
  • Employee Training Contractors
  • Pre-Startup Safety Review.
  • Mechanical Integrity of Equipment
  • Written Procedures
  • Inspection and Testing
  • Quality Assurance
  • Non-routine Work Authorizations.
  • Managing Change
  • Incident Investigation
  • Emergency Preparedness
  • Compliance Audits
  • Planning Staffing
  • Conducting the Audit
  • Evaluation and Corrective Action

Cycle Counting In Microsoft Dynamics AX

By Randy Goldbeck, Fullscope Director of Services

Cycle counting is a specific and practical way to measure inventory by selecting a sample of your items every day and recording the actual on-hand quantities. A good cycle count program also identifies errors in inventory balances and corrects them, so the actual count agrees with what is in the system. The objective is to measure and correct inventory errors on a continual basis. The cause of the problem should be investigated and corrective action taken to fix the root cause.

Guidelines for Cycle Counting Programs

The following are some guidelines to a good cycle count program:

  1. Every item number should be counted over a planned time period.
  2. Some item numbers may be counted more frequently than others depending on their value and usage.
  3. Inventory is material, piece parts, sub-assemblies, and products waiting to be used in production or to be sold to customers.
  4. Inventory is identified by item number carried in warehouses, in inventory locations and by production orders for work-in-process.
  5. Inventory accuracy relies on timely processing of transactions in the Dynamics AX system.
  6. An initial goal should be to have inventory be at a 90% accuracy level. Over time, this goal should ultimately reach 99% accuracy.

What Makes Up Accurate Inventory?

In the following table is item number 5140 really accurate? Actually, No. When using locations, the actual quantity on hand in each location should agree with the system quantity.

cycle count table a

Inventory Tolerances

Error tolerances can be used to accommodate counting errors on small non-critical, inexpensive items. These tolerances represent the acceptable differences when or when not to post cycle count transactions. If a low cost item is within its acceptable tolerance, then the cycle count will not be posted. The table below illustrates an example of a low cost item that might have a procedural tolerance and therefore would not be considered to be inaccurate.cycle count table 2The following table represents a suggested policy for acceptable tolerances according to ABC classifications (Described later)

cycle count table b

ABC Classifications

An assumption based on Pareto’s law suggests 20% of the parts represent 80% of the inventory value. The “ABC” classification method classifies items according to certain characteristics, like historical usage in costs for the last 6 to 12 months. Pareto’s law suggests that 80% of any characteristic is represented by approximately 20% of the items in that characteristic. Applying that to inventory, 80% of the total dollar value is represented by 20% of the item numbers. Therefore, a suggested approach to determining ABC percentages might be:

  • A Items = 20%
  • B Items = 30%
  • C Items = 50%

Sample Cycle Count Plan

Because of personnel time constraints, every item cannot be counted every day. In order to accommodate this, the frequency of counting an item should be set up according to the example in the table below.

cycle count table 3

Based on the plan you could expect to make the following counts. By doing this ahead of time you can evaluate the feasibility of the cycle count.

cycle count table 4

Setting Up a 30-Day Control Group

The 30-day control group is a proven method to begin a cycle count program. To begin select 20 -30 item numbers that are used most frequently to manufacture products. These items should represent a cross section of the typical item numbers used in your company. These items are going to be your audit base. Remember, the goal is to identify and correct reasons for inventory in-accuracies.

Day One

  • Determine the target item numbers and establish those items as an inventory control group.
  • On the first cycle count select only this control group and cycle count each item number.
  • Compare the cycle count values to the perpetual inventory values in the system and determine if each item number is accurate.
  • For those items that are not accurate, make an inventory adjustment and make a note of your overall inventory accuracy.

Day Two

  • Count the same 20-30 item numbers. The number of item numbers with errors should be dramatically reduced.
  • The item numbers that have in-accuracies were caused by errors in yesterday’s transactions. Finding the cause of errors is a simple activity of looking at yesterday’s transactions.

Next 30 Days

Count these same 20-30 items for the next 30 days. When an inaccuracy is found, it’s always a result of an error caused by yesterday’s transactions. The objective is to find the cause and correct it as quickly as possible.The purpose of the 30-day control group audit is to find and repair the most frequently caused transaction errors. It also provides a fast start with quick results to begin a cycle count program. By the end of the 30 days, the most frequently occurring errors are usually identified and repaired.

The purpose of the 30 day control group audit is to find and repair the most frequently caused transaction errors. It also provides a fast start with quick results to begin a cycle count program. By the end of the 30 days, the most frequently occurring errors are usually identified and repaired.

Cycle Count Considerations

The following are normal considerations to allow for when setting up your cycle count program:

  • If you have 10,000 items in the system only 7,500 may have inventory balances.
  • Usually inventory is stored in two locations.
  • One partially dedicated person (2 hours per day) can usually count 15,000 items a year or 60 counts per day.

Cycle Count Cut-off

In order to minimize disruptions, specific times for completing the cycle count should be followed. In addition, you should verify that all transactions have been submitted and processed before beginning the cycle count. We recommend that you count during low production activity times and complete counts and reconciliation as soon as possible

Cycle Count Analyst

In order to ensure a sound cycle count program, you should identify a cycle count analyst. Usually this person will spend about 90 minutes a day counting and processing adjustments to inventory.

Stay tuned … In the next Cycle Count blog, we will show you how to setup and manage a cycle count program in Microsoft Dynamics AX.

Top 5 Challenges Facing Specialty Chemical Manufacturers and How to Solve Them

Regulatory updates, modifications, new compliance requirements and the need to continuously make adjustments for OSHA, DHS, HCS, CFATS, REACH, FDA requirements are some of the unique challenges specialty chemical manufacturers face every day. We understand these challenges because we’ve spent over 20 years working with specialty chemical manufacturing customers.

What are the top five challenges specialty chemical manufacturers face?

  1. By far, the number one challenge our specialty chemical customers talk about is the regulatory changes and requirements placed on them daily. These changes come at a very rapid speed, and include all sorts of associated costs and efforts.
  2. The second challenge is the increasing cost of raw materials and products and the way the marketplace is changing and affecting those costs.
  3. Global competition and the push for higher quality at lower cost margins form the third challenge.
  4. Our customers tell us that mergers and acquisitions are the fourth challenge. Questions we hear “Are the right business systems in place to accommodate a new merger or acquisition? Is the existing business system scalable?”
  5. Finally, the fifth challenge our specialty chemical manufacturing customers are concerned about is new product development. Executives wonder “Are the right products being developed? What are the associated costs? What are the time constraints?”

Addressing Top 5 Specialty Chemical Challenges with Technology

Is your company struggling with any of these challenges? Selecting the right technology platform, including an enterprise resource planning (ERP) system, is the optimal place to start. Here are four traits to consider in an ERP solution:

Consider a business system that is compliant with global regulations. The system should provide documentation of standard processes, track and log operations and results, lot traceability and track items from cradle to grave providing confidence in your ability to implement an effective recall procedure. Look at a system that offers visibility and cost control. The new system should offer real-time inventory tracking so you can adjust forecasts and inventory to help ensure adequate supply for demand. Make sure the new system is flexible and scalable to accommodate organic and merger and acquisition growth and offers multi-currency and multi-language functionality so your company can compete globally.

If you want to start addressing some of these challenges now or in the future, let us help. We are one of the largest resellers of Microsoft Dynamics AX ERP, one of the fastest growing ERP solutions from the world’s leading software company. Sold only by partners, Dynamics AX is now available in the cloud or on premise. We have more than 20 years of experience helping specialty chemical manufacturers tackle industry-specific challenges including meeting regulatory compliance requirements and providing real-time insights into product costs. With AX, we can help you:

  • Manage product compliance, including Material Safety Data Sheets
  • Provide real-time insights into cost trends
  • Make proactive decisions using data analytics
  • Provide complete batch attribute tracking

Additional Information

Take a look at these additional resources for specialty chemical manufacturers:

New Video: Top Five Challenges Facing Specialty Chemical Manufacturers and How to Solve Them

Quick Product Demo Video

Chemical Fact Sheet


AOT Queries Get More Complex Than You Think

By Bill Concepcion, Fullscope Technical Consultant

In my experience, I found instances where a Microsoft Dynamics AX developer created a custom SSRS report in AX using a data provider where the data provider is using a simple query and one or more subqueries. When I ask, “Why not create the query on the Application Object Tree (AOT) and use the query for the report instead of creating a data provider?” the answer I receive is that one or more data sources in the query must relate to multiple data sources in the query. The thought is either AX query objects from the AOT do not support a data source to relate to multiple data sources, or the developer just didn’t know how to do it. That’s why I decided to write this blog.

Let’s consider a scenario to illustrate the issue. Let’s say we have the following custom table:

graphic1  Now suppose that we need to create a query object on the AOT that will cross-reference Table1 with product variants and get the Variant ID. Product variants are contained in the InventDimCombination table. Let’s start with the query. The issue will quickly become apparent. The query may look something like this:


It’s possible to have this query start with Table1 and work our way down to InventDimCombination but I chose to start with InventDimCombination. Everything looks fine with the relationships except for one thing: the relationship between Table1/InventDim_2 is not sufficient to correctly relate to InventDimCombination/InventDim_1. Currently, all items in Table1 that happen to have the same variant dimensions will match with the current InventDimCombination record creating a one-to-many relationship, which is undesirable. Table1.ItemId is also needed to relate to InventDimCombination.ItemId.

How can this be done? Well, in regards to the Relations node, it is not possible to relate to more than one data source under a single node. Yet it is needed to get Table1.ItemId to match with InventDimCombination.ItemId. The multiple Table1 records that will potentially match can be filtered by creating a range.


A range is created to filter the Table1 records that are matching by dimension to include only those records that also match the current variant item. The resulting subquery is shown through the tooltip. Here is the query as shown in SQL Server:


This technique appears not to be widely known or used. Perhaps, you may have tried this technique and got an error and gave up. There are two things needed to make this technique successful:

  1. The comparison must be surrounded in parentheses. If there are multiple comparisons through ORs or ANDs, then each comparison must be enclosed in parentheses and the entire expression must be enclosed in parentheses. For example, ((datasource1.field1 == datasource2.field1) && (datasource1.field2 > datasource3.field2)).
  2. As implied by the example with #1 above, the references in the range are data source names and not table names, although a data source name might be the same as a table name.

As you might guess, complex queries may be created using this technique. For one customer, I used this technique for a query that resulted in a dozen subqueries for a custom report and a data provider wasn’t needed.

I hope you found this blog useful. Shoot me an email at with any specific comments or questions.

Default and Site-specific Order Settings in Microsoft Dynamics AX 2012

By Chris Jamison, Fullscope Solution Architect

This blog entry explains how default order settings and site specific order settings are stored in Microsoft Dynamics AX 2012. The method of storage is often confusing since the two sets of order settings are stored in three different tables. The three tables are segregated by module (Purchase, Inventory, Sales), but each of the module-specific tables stores both default and site specific order settings.

  1. Released product default order settings

Order settings are defined for products in the Released product details form, under the Manage inventory action pane tab.

Released product details> Manage inventory> Default order settings (form EcoResProductDetailsExtended):


Figure 1 – Displays Default order settings (form InventItemOrderSetup):

figure2Figure 2

The default order settings are stored in three tables, one each for the Purchase, Inventory and Sales modules. In these three setup tables, the default order settings are stored by itemId and always have the InventDimId field set to “AllBlank”. The three records below (Table 1) correspond to the screenshot shown above (Figure 2):

Table1Table 1: Default order settings as stored in AX tables

In summary, an item’s default order settings are stored in three tables, one record per table, with the table field InventDimId always equal to AllBlank.

  1. Released product site specific order settings

Released product details> Manage inventory> Site specific order settings (form EcoResProductDetailsExtended):


Figure 3 – Displays Site specific order settings (form InventItemOrderSetup)

figure4Figure 4 – Note this is the same form as that used for maintaining default order settings.

Users should maintain one record for each site defined in the default order settings for the item. In this example, since we have one site for purchasing, and a second site for inventory and sales, there will be two sites defined under site specific order settings.

For each site, under the general tab, users will define the default warehouse (or leave blank).


Figure 5 – OFFSITE settings

Note that the warehouse field can be left blank. In this example, since this site is only used for receiving, only the purchase warehouse is defined for the OFFSITE site.

For the ROL site, both inventory and sales warehouses will be defined, but the purchase warehouse can be left blank.

figure6Figure 6 – ROL site settings

Here is how the data is defined in the tables. Note here that InventDimId will always be set to the site being maintained, and the InventDimIdDefault field will correspond to the warehouse (or be blank if not used for the site).


Table 2: Site specific order settings as stored in AX tables (as displayed in figures 5 and 6 above)

Note: for InventDimId and InventDimIdDefault fields, a null value is not the same as the AllBlank value.

In summary, an item’s site specific order settings are stored in three tables (the same tables as used for default order settings), one record per site per table, with the table field InventDimId always equal to the site’s InventDimId.

  1. InventDimIds and the InventDim table

In the Table 1 and Table 2 shown earlier, each field value in the columns “InventDimId” and “InventDimIdDefault” is a key valued in the Inventory dimensions table, InventDim.

figure7Figure 7 – InventDim table values

Each value specified for either Site or Warehouse in the default settings will exist in the InventDim table. Notice that each InventDimId used in default settings corresponds to either a specific Site or a specific Warehouse (Site = InventSiteId, Warehouse = InventLocationId). The order setting tables do not specify InventDimId for the combination of site and warehouse.

  1. The order defaults in sales order entry

One use of order defaults is to set the initial values for site and warehouse on sales order lines. Figure 8 shows a newly added sales order line where the site and warehouse values are populated based on the default and site specific order settings (sales) for this item.

figure8Figure 8

The SalesLine record shown above has an InventDimId associated which keys to the InventDim record for the combination of Site and Warehouse shown on the Sales order form (Figure 8). In this example, the InventDimId is “ROL-000091”, which can be seen in Figure 7 above.

For site specific records, the warehouse referred to in InventDimIdDefault is for the InventDim record of the warehouse only, not site/warehouse combination. So when working with the records programmatically, you would need to create an InventDim record with the site and warehouse values combined into a third InventDim buffer, and use that buffer to lookup InventDimId for the combination of site and warehouse.

Here is an example of how the sales order line defaults would be found:

>Given InventDim1, with InventDimId = “ROL-000021” (this is for InventDim1.InventSiteId = “ROL”);

>And given InventDim2, with InventDimId = “ROL-000084” (this is for InventDim2.InventLocationId = “FG”);

>Create buffer InventDimTmp, with InventDimTmp.InventSiteId = InventDim1.InventSiteId and InventDimTmp.InventLocationId = InventDim2.InventLocationId

>Create buffer InventDim3 = InventDim::findOrCreate(InventDimTmp).

InventDim3.InventDimId will be returned as “ROL-000091”, for the combination of Site/Warehouse on the sales order line.

For more information other topics in Dynamics AX, call at 770-772-3121 or visit

Forecast Reduction in Microsoft Dynamics AX

By Donald Clark, Fullscope,  Functional Architect, CFPIM, CSCP

If your manufacturing company uses a forecast, one of the decisions you must make is how to handle that forecast when you run MRP, called Master Scheduling in Microsoft Dynamics AX.

For example, two key questions are: 1) how much of the forecast should you consider valid and 2) how should you consume the forecast with actual orders. Many take the approach that in nearer periods of time, you should consider less of the forecast when planning and in further out periods you should consider more of the forecast.

This is a fundamental concept that is generically referred to as “replacing forecast with knowledge” and Dynamics AX provides the tools to do just that.

Basic Setup

The first thing you need to do is to visit the Master Plan and look at the setup – specifically in the Forecast group where the Reduction Principle resides.

Path = Master planning > Setup  > Plans  > Master plans


The Reduction Principle determines how AX will reduce the forecast requirements when you run the Master Scheduling Process. The four choices, along with what each does, are:

  • None: No reduction occurs during master scheduling.
  • Percent – reduction key: The reduction key drives how AX reduces the forecast during Master Scheduling.
  • Transactions – reduction key: Transactions drive how AX reduces the forecast during Master Scheduling.
  • Transactions – dynamic period: Actual order transactions that occur during the dynamic period drive how AX reduces the forecast during Master Scheduling. The dynamic period covers the current forecast dates and ends with the start of the next forecast.

This example uses Percent – reduction key as the Reduction Principle. Now look at the Reduction key itself.

Path = Master planning  >  Setup  > Coverage > Reduction keys


As mentioned earlier, many people take the approach that in periods closer to the present, actual orders should have replaced the forecasted demand. Any remaining forecast should be reduced so that the organization does not over-produce. Those same folks also assume that the further out into the future you look, the more of the forecast you should take into account.

Again, this is just one approach to managing forecast reduction and is an approach the Reduction Key allows you to take.

This Reduction Key reduces the forecast 95% in the first month, 90% in the second month, etc. If you had a forecast quantity of 1,000 in July, AX would only calculate demand for 950 because the Reduction Key took it down by 95% assuming also that actual orders had consumed most or all the original forecast.

Here is a recap of the forecast reduction principles. The three basic choices are:

  • None – No consumption occurs


In this graph, the Master Plan is a combination of the Gross Requirements and Sales Orders. Sales Orders in AX do not consume the forecast.

  • Open Orders — Open orders consume the forecast


In the first period in this example, the Sales Orders of 30 consume all but 10 of the forecast quantity of 40.

  • Percent – reduction keys and orders consume the forecast

In this example, in the second period, Sales Orders = 20, Forecast = 40 (which will be reduced by 75%) so the total required quantity is 30.

Coverage Groups

Here’s one more setup to consider …the Coverage Group.

(Detailed discussion about the Coverage Group and its inner workings are beyond the scope of this post. This is a high level example.) You need to select the Reduction Key here. Remember the Coverage Group settings override the Master Plan settings.

Path = Master planning > Setup > Coverage > Coverage groups


Into Action

Now you can see how the settings play together in Master Scheduling. As an example, there is a Product FG-2112 that has a demand forecast of 500 each every 14 days as shown below.

There is also various sales order demand for this product.


When you run Master Scheduling, you get the following planned orders for the product.


Now you can look at planned order number 807311 in detail by double-clicking on it in the grid.


The Pegging tells the source of demand story for the planned order that has a total quantity of 169. You see three different sales order lines at 48 each and a demand forecast for 25. Remember the original forecast was 500, but this particular demand occurs in the first month so the Reduction Key reduces it by 95%, leaving us with 50 for the forecast.

This was a brief overview of how Dynamics AX allows you to consume forecasts, and the example here is just one of many ways to handle forecast demand in AX.

For more information on forecasting, or other topics in AX, call at 770-772-3121 or visit

New AX Tip: Queries – Not Really All or Nothing


By Rick Scruggs, Fullscope, Technical Solution Architect

When developers think about queries, they think the queries are limited for the most part because the combined ranges are all AND conditions.  Not so.

A recent requirement related to the CustOpenInvoicesListPage query surfaced. I needed to add an additional range on the custtrans data source for the field transtype. After going through the standard development practices and taking a look at all of the points of modification, I was befuddled by what I saw: multiple occurrences of the transtype field.

At first I didn’t think running the query would produce any records unless somehow the query was recreated in code at runtime. I searched for code that wasn’t there. A colleague gave me the idea to hover over the data source to get a look at the SQL statement. And there it was, right in front of me … (see Figure 1) Multiple ORs surrounding all of the transtype field conditions then an AND for the additional field not named transtype.


Figure 1: Shows the where clause when hovering over the custtrans data source

I recreated the same scenario on the SalesTable as a datasource to a junk query. I added the range for payment twice, and then added one for another field.  This also created an OR condition in the AOT query (see Figure 2). I have been involved with AX since it was called Axapta 3.0. This is the first time I have seen an AOT query with any type of OR condition not inside a special expression.  I was a little surprised.


Figure 2: Shows the select statement when hovering over the data source in the junk query with only two fields added

 Have you had a similar “a ha” AX moment? Share it here or shoot me an email

A Closer Look at UCCNet and GTINS: Why They Matter

By Randy Goldbeck, Fullscope Director of Services

What is UCCNet?

UCCnet, part of GS1 US, formerly the Uniform Code Council, Inc.UCC), was established in response to growing requests from retailers, manufacturers and suppliers to address the problem of inaccurate supply chain data, such as product descriptions, item numbers or price. Inaccurate or outdated data used in invoice pricing or purchase orders can result in product delivery errors and lost sales — problems estimated to cost the industry over $50 billion annually. UCCnet is a non-profit, neutral organization in charge of maintaining the GLOBALregistry.

The registry acts as a worldwide index that stores the basic information about all products distributed to retailers worldwide. Because UCCnet is a neutral party, major retailers have endorsed it and have created a mini-Y2K event as they request suppliers to comply. To publish to the GLOBALregistry, suppliers gather their product information and assign a unique 14-digit global trading item number (GTIN) to each product.

Because products can ship in many ways, a unique GTIN (Global Trade Identification Number) is required for all product packaging levels (e.g., 4-inch geranium, 6-inch geranium, 606 flat of geraniums, etc.). Once a product becomes part of the UCCnet GLOBALregistry, the supplier selects retailer(s) for publication. To publish, the supplier uses a global location number (GLN) that identifies each retailer. The supplier may publish to every store in the retailer’s organization or get as granular as publishing GTIN information to a single store. If this seems like a lot of work, it is. It requires forethought be given to product naming plus all existing product information must be scrubbed. Yet, in many cases, suppliers who have completed the UCCnet exercise have discovered that their newly created GTIN information is a better product description than what was originally stored in their enterprise resource and planning (ERP) systems.

 What are the benefits?

A recent A.T. Kearney study for Grocery Manufacturers of America – Food Marketing Institute revealed the following:

  1. Correcting catalog errors costs $60-80 per error.
  2. Thirty percent of item data in retail catalogs is incorrect.
  3. Each SKU requires 25 minutes of manual cleansing per year.
  4. Every invoice error costs $400 to reconcile.
  5. Sixty percent of all invoices have errors; 43 percent result in deductions.
  6. The average product roll-in takes six weeks.
  7. Percent of sales lost to inaccurate data is 3.5 percent. The net result is an estimated $50 billion loss through supply chain inefficiencies each year.
  8. In a presentation to the UCCnet Advisory Council, one major retailer revealed that 37 percent of product information from their largest supplier was being re-keyed. After the study, and because of corrective measures, the retailer discovered that it was getting more accurate dimension information and an increase in new product market share over other retailers. Many suppliers are keeping this study in mind and are asking their retailers to comply by setting recommended compliance deadlines.

Cleaning up your product data clearly makes sense.  For more information, visit

What do you think about UCCNET, GTIN and other practices mentioned in this blog? If you have questions or feedback, shoot me an email at

Part 2: How to Control Item Availability & Access Pricing Info in Microsoft Dynamics AX

dynamics-ax-technical-tipBy Glen Kiessling, Fullscope Senior Business Analyst

This is the second part of a blog post that explores List Items, a new feature in Microsoft Dynamics AX 2012 R3, and it can be found in the Sales and Marketing module in the Setup section.  This feature is designed to give you more options when creating a sales order and adding lines to the order. Item Lists gives you the option to have customer specific lists and to have Item Lists that are accessible to all customers at sales order creation. 

Did you miss Part 1:  Read it Now

Create a Sales Order from a Customer Specific Item List

Go to the Sales and Marketing or Accounts Receivable module in the Common section, click Sales Orders, then click All Sales Orders. On the Action Pane in the New section, click the Sales Order button*(see image 11). This will open the Create Sales Order form (see image 12), select customer account (see image 13), then on the General fast tab fill in Customer Requisition and Customer Reference fields as required, and select Site and Warehouse. On the Shipping fast tab, set the Requested Ship Date and the Requested Receipt Date (see image 13), then select the Mode of Delivery and Delivery Terms and click OK. Provided the checkboxes covered in the requirements section above are checked, this will open the Item List form (see image 14). On the left of this form are the Item Lists available to use with this customer. There will only be one list that is directly linked to the customer account as AX only allows one, but any of the non-customer specific lists will also show in this section. Click on the Item List desired. Manipulate the Items grid to include exactly the items, quantities and units of measure desired. Click the Copy and Close button** at the top of the form. This will close the Item List form and open the new Sales Order with the items (see image 15), quantities and units of measure from the Item List form on the sales order lines.

* A sales order can be created from the Customer Master by highlighting a customer account in the grid and then going to the Sell tab in the Action Pane and clicking the Sales Order button in the New section. This method of creating a Sales Order does not utilize the Item List functionality

** You can click the Copy button at the top of the form and this will open the new Sales Order with the items and quantities from the Item List but the Item List form will remain open in the background. 

Image 11 – New Sales Order

Image 11 – New Sales Order



Image 12 – Create Sales Order Form

Image 12 – Create Sales Order Form

Image 13 – Enter Required Fields on Create Sales Order Form

Image 13 – Enter Required Fields on Create Sales Order Form

Image 14 – Item List Form for Creating Sales Order Lines

Image 14 – Item List Form for Creating Sales Order Lines

Image 15 – Newly Created Sales Order with Item Lines from Item List

Image 15 – Newly Created Sales Order with Item Lines from Item List

Create a Sales Order from a Non-Customer Specific Item List

This process is the same as stated above but you select one of the non-customer specific Item Lists (see image 17). These types of Item Lists are available to all customers on all Sales Orders, regardless of whether or not the checkbox on the customer master file is checked to allow Item Lists. The checkbox on the Accounts Receivable Parameters turns on the functionality of Item List and makes the Item List form appear when clicking OK on the Create Sales Order form. The checkbox on the customer master file allows an Item List for that specific customer to be created.

Create a Sales Order without Using the Item List Option

If you do not want to use an Item List to create a Sales Order or if no Item List displays on the Item List form when it appears, you should click the close button at the bottom of the form (see image 15). This will open the Sales Order with no lines as normal.

Use of Item Lists vs. Copy Order Lines

The “Copy From All” function has its purpose but can inadvertently cause issues if you forget to click certain checkboxes for certain purposes. The ”Copy From All” function also copies information from the Sales Order Header on the order used and this can provide incorrect dates, sites, warehouses, modes of delivery and delivery terms. It is also possible to copy header and line charges from the Sale Order used that may or may not be desired or accurate.

The Item List does not refer to any previous sales orders when creating a New Sales Order, it only allows selection of items, their quantities and units of measure. When the item lines appear on the new Sales Order, AX acts as if they were added manually and adds all relevant information just as in the standard creation of Sales Order lines. This avoids the possible human errors in the “Copy From All” functionality. This also allows for some control of items for purchase by specific customers. By setting up Customer Specific Item Lists with only items that the specific customer is allowed to purchase and then setting business policies that all customer have a Customer Specific

Item List and all Sales Orders are to be created from the Item List form, will allow some control of what items a customer is permitted to purchase. This is not foolproof but it is better that spending time and money on a customization for this requirement.

Item Lists and Trade Agreements with Current or Future Effectivity

Since items added to a Sales Order from an Item List are just like normally created sales order lines, the Trade Agreement Pricing that match the effectivity dates provided by the parameters on the Sales Order will apply. Trade Agreements with future pricing, based on Price Date, will also apply correctly when using the Item List method.

Auto Charges and Item Lists

In addition to pricing, all Auto Charges setup for the Header or the Lines of the Sales Order will be created as normal when using the Item List method.

Item List Short Comings

There are a couple of things to realize when using the Item List method to create sales order lines.

  1. When the checkbox for Item List Generation is checked in Account Receivable Parameters, it activates the Item List form for all customers and all sales orders, even if the customer you are creating a sales order for is not activated for an Item List. This is easily skipped by closing the Item List form when it appears but it does present an extra step in creating a sales order.
  2. Since Microsoft did not include Catch Weight in R3, the code for Catch Weight items in the Item List form is not complete. You can still put Catch Weight items on an Item List and they do create sales order lines but the quantities and units of measure from the Item List do not move to the sales order line. Instead for a Catch Weight item, you end up with a line on the sales order for the item, but with a quantity of 1 and the Catch Weight unit of measure regardless of what was present on the Item List for quantity and unit of measure.

I covered a lot of ground in these two posts — if you have questions, shoot me an email at