Mass Add/Update

Mass Add/Update

HelpID:524122038

Navigation: Tools >> Routines >> Inventory >> Mass Add/Update. This window is also available from the CC Stock Count Schedule window by clicking the Mass Add button.

The Mass Add/Update window can perform the following tasks:

  • Add Items to a Stock Count
  • Set/Update Count Date and Count Interval
  • Set/Update Tolerance

After assigning Value Codes, the next steps are to assign Items to a count schedule and set Tolerance values. Use the Mass Add/Update window to create a Query that identifies a group of Items, then execute the desired Task on those items (such as setting the Next Count Date or updating the Tolerance information).

Add Items To A Stock Count

If you open Mass Add from the CC Stock Count Schedule window, the Stock Count ID field will be filled-in automatically and the Task will be set to Add Items to Stock Count.

If you opened the window from Inventory Routines, select Task “Add Items to Stock Count”, and enter a Stock Count ID.

This is a two-step process:

  1. Create a query that identifies the group of Items you want to add to the count
  2. Add those items to the Count

You can repeat this process as many times as needed. The utility will not duplicate items on the Count. The Query Results window only shows Item-Site combinations, or Item-Site-Bin combinations, that are not already on the Stock Count.

The steps below show how to create a query for Value Code = A items that are in a specific range of Item Classes.

Value Code = A

  • Select FIELD = “Value Code on Item-Site”
  • Select the operator of “is equal to”
  • Enter “A” in the value field
  • Click the ADD button.

Item Class Range

  • Select FIELD = Item Class
  • Select the operator of “is between”
  • Select the Item Classes. “Is Between” includes the selected values (in this case the Item Classes ATT CORD and SERVERS-2 are included, as are all Classes in between).
  • Click the ADD button.

The complete Query looks like this:

Click RUN QUERY to see the results.

To change back to the Query Builder window, click the Query radio button:

One of the criteria can be removed by selecting it, then clicking the REMOVE button.

When the Query Preview shows the desired list of Items, click the UPDATE button.

See SAVE QUERY below.

Update Count Date/Interval

Dynamics GP automatically increments the next Count Date by adding the Count Interval Days each time a Stock Count is posted for an Item-Site or Item-Site-Bin. Count Date can be helpful when performing regular Cycle Counts (as opposed to a complete physical) to ensure all items are eventually counted. For example, if all items should be counted at least once every 6 months, Setup/Update Count Date will calculate how many items need to be counted each day during that time period and assign a group of items to each day. For example, if 100 items need to be counted every 10 days, it would assign 10 items to be counted each day.

Setting Count Date and Interval is not required.

Select Task “Set/Update Count Date”.

Count Interval: is the number of days between each count of an item.

Start Date: is the next date on which cycle counting should start. For example, if the date is 4/12/2017, the first group of items will be counted on 4/12/2017, the next group on 4/13/2017, and so on.

Count On: select the days (i.e. work days) on which cycle counting can occur.

Min Items/Day: enter the minimum number of items that should be assigned per day. For example, if 20 items should be counted every 90 days, Min Items/Day = 2 would result in assigning two items each day.

Query

Create a Query to identify the group of items on which you want to set the Count Date/Interval.

The steps below show how to create a query for Value Code = A items that are in a specific range of Item Classes.

To perform that assignment, follow these steps:

  • Select FIELD = “Value Code on Item-Site”
  • Select the operator of “is equal to”
  • Enter “A” in the value field
  • Click the ADD button.

Then:

  • Select FIELD = Item Class
  • Select the operator of “is between”
  • Select the Item Classes. “Is Between” includes the selected values (in this case the Item Classes ATT CORD and SERVERS-2 are included, as are all Classes in between).
  • Click the ADD button.

The complete Query looks like this:

Click RUN QUERY to see the results.

To change back to the Query Builder window, click the Query radio button:

One of the criteria can be removed by selecting it, then clicking the REMOVE button.

When the Query Preview shows the desired list of Items, make sure the Count Interval and Count Date are set, then click the UPDATE button.

The update routine will first calculate how many items should be assigned to each day. This is the total number of records returned by the Query, divided by the Count Interval. It then updates the Count Interval and Next Count Date for the first group of items. Next Count Date is incremented by one day, and if that falls on an allowed workday, it updates the second group. This proceeds until all groups of items have been updated.

Next Count Date and Stock Count Interval are set in the Item Quantity Master table (IV00102).

See SAVE QUERY below.

Update Tolerance

Tolerance is used to prevent submitting a count when one or more items has a count that is larger than should be allowed. See the Tolerance section of this manual for more details.

Use of Tolerance settings is not required.

Select Task “Set/Update Tolerance”.

Select a Tolerance option, and if appropriate, enter a Quantity, Percent or Dollar Amount.

Create a Query to identify the group of items on which you want to set the Tolerance options.

The steps below show how to create a query for Value Code = A items that are in a specific range of Item Classes.

To perform that assignment, follow these steps:

  • Select FIELD = “Value Code on Item-Site”
  • Select the operator of “is equal to”
  • Enter “A” in the value field
  • Click the ADD button.

Then:

  • Select FIELD = Item Class
  • Select the operator of “is between”
  • Select the Item Classes. “Is Between” includes the selected values (in this case the Item Classes ATT CORD and SERVERS-2 are included, as are all Classes in between).
  • Click the ADD button.

The complete Query looks like this:

Click RUN QUERY to see the results.

To change back to the Query Builder window, click the Query radio button:

One of the criteria can be removed by selecting it, then clicking the REMOVE button.

When the Query Preview shows the desired list of Items, make sure the Count Interval and Count Date are set, then click the UPDATE button.

See SAVE QUERY below.

Save Query

The Query built by the Mass Add/Update window can be saved and reused. The tSQL query can also be edited to add custom business logic.

NOTE: The saved query for “Add Items To Stock Count” is tied to the Stock Count ID, so it cannot be re-used for other Stock Count IDs.

Step-1

Select the Task Type (i.e. Add Items to Stock Count) and ADD one or more selection criteria.

Step-2

Click the RUN QUERY button. This creates the tSQL query used by the window.

Step-3

Click the EDIT button. If you have existing saved queries you can skip Step 1 and 2 and just click the EDIT button.

Regardless of the Task Type, the query populates the same temporary table. While the entire query can be edited as needed, keep in mind that it needs to populate the temp table shown in the “header” part of the Save Query window (“insert into…”). The key on the table is Item Number, Location Code and Bin Number.

Note that for the “Add Items to Stock Count” query, the Stock Count ID (i.e. “DEMO”) is contained in the query. Do not change it.

Once a query has been saved, the Mass Add/Update window cannot be used to add/remove selection criteria. The Add/Remove buttons will be locked.