SmartConnect for Stock Count Entry in GP
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!
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.
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.
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.