Skip to content
+1-888-319-3663

COMMUNITY FORUM

SmartConnect for Stock Count Entry in GP

Audrey Forrest asked 4 years ago
With no results on this site, and the only Google search I found regarding SmartConnect for Stock Counts in GP reading ”You cannot integrate to the stock count window using smartconnect.”, I thought I would help out my fellow SmartConnect users by documenting here how I managed to import count results into GP using SmartConnect. It is not available within the nodes on the GP connector, but you can still integrate. If your organization is like mine, we use the Stock Count feature for full Physical Inventories, which is a lot of data entry, and the Stock Count entry screen in GP is far from intuitive. Integrating via SmartConnect gives our organization the freedom to develop a better entry system. This option uses the SQL connector, so keep that in mind this map will likely need to be run by someone with elevated permissions to the tables.
1. Set up a SQL connection to your Dynamics database in the generic connector.
2. Set Up a map with your desired Data Source Type (I used Excel). You will need to have a minimum of the Stock Count ID, Item Number, Location Code, Bin Number and Counted Quantity in your source.
3. For the destination, select \”Microsoft SQL Table\” and then choose the SQL connection from Step 1.
4. For the table, choose IV10301. This is the stock count entry line table.
5. In the map, map your stock count ID, Item number, location code, bin number and counted qty. I also mapped Counted date and time from my source document, and I think that is recommended. Map verified to a local constant of 1.
6. Check the ”Update Existing” box and Group Data by at least Stock count ID.
Save and run the map.
I use this map only for count entry and not to overlap anything pre-calculated by the system (captured quantity, variance quantity) and I do not run it for anything related to generating the count itself. Then I can review it in the stock count entry window before processing. I hope this helps anyone else that was looking for this like me!
Patrick Roth Staff replied 4 years ago

Good work on that Audrey and glad that it works for you.

From a SC perspective, the only thing I would note that going directly to table means no data validation so that you’d have to be sure that your data is correct (ie the Items you integrate exist in IV00101, you aren’t entering in -5 for physical count, etc) which direct to table doesn’t give us. So you’d have to be careful about integrating in bad data.

That said, this isn’t like a SOP Document which is a lot more complicated and putting in an item that doesn’t exist isn’t going to blow up GP so the risk is pretty low.

If you wanted to take it one more step by adding validation to your inserts, then you’d make a stored procedure with params for the params you are setting.

Then in the proc, put in your data validation and return any errors.

the econnect sdk talks about how to create such a proc and what it would look like.
https://msdn.microsoft.com/en-us/library/aa973834.aspx
https://msdn.microsoft.com/en-us/library/bb625127.aspx

once you have that, you can switch the destination to “stored procedure” and mark the econnect checkbox.

For bonus points in the SmartConnect community, then you could use Node Maintenance to add the proc there and the params.

After this is set up, you could make your map using a GP Destination and your custom destination type and map it the same as any other GP node.

Patrick
eOne

Audrey replied 4 years ago

Thanks, Patrick! This is great info. I will look into the Microsoft articles to create more controls around the process. The more I can use SmartConnect to get large data into GP, the better 🙂


If you would like to submit an answer or comment, please sign in to the eOne portal.