280
I want to be able to pull from Smartlist inventory purchases by customer, sale of inventory to customer, date we paid for inventory and date customer paid their receivable. I was looking for a common denominator in each module so I could isolate the data to export to Excel and then create a pivot table. Essentially, I want to find out how long it is from the time we purchase the inventory and pay for it until the customer pays us. We are using Dynamics GP 10.
*This post is locked for comments
I had forgotten all about the direct link to Excel. Used this all the time working for a prior company. Thanks for your in put. It has been very helpful.
Not a problem.
You can also link the view directly in Excel with the data connectors.
Make sure you have an SQL user and password for reporting outside of GP.
With a direct link in Excel you will easily create reports and have the possibility of pivot tables that will always be up to date with GP info.
OK - glad to know it's doable, thanks. Then I suppose I could use Microsoft Report Manager once I have created my SQL views and export the data to Excel, correct? Since unfortunately we don't have Smartlist Builder only the canned Smartlist that comes with v. 10. If you could please confirm utilizing Report Manager before I make an attempt at this. I know just enough about SQL to be dangerous. :) Thanks.
Hi Brigitte.
Not an easy task but doable.
Strongly suggest building sql views which you can then attach in GP with Smartlist Builder.
Depending on your processes here are your choices.
If your purchases are directly linked to the sales order you can use the SOP60100 table which links the 2 documents.
You can then find which invoice was generated from the SOP and the Receiving and Invoice Created from the PO and link to the AR or AP documents to see if documents are paid off.
If you sell inventory you will need to use the IV10200 file which contains all receipts and also refers to the receipt number which you can use with POP10600 to find the AP Invoice and the IV10201 file which will show you all expeditions made from this receipt showing invoice numbers.
Hope this helps
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (