Add a Field to Your Dynamics GP Refreshable Data Connection with Excel

Refreshable data connection in Dynamics GP

Now that you’re using a Refreshable Data Connection between your Microsoft Dynamics GP system and Excel, you might find that you’re missing a field or two. It’s not as hard as you’d think to edit the data connection and add in those missing fields.

I’ll explain how. If you’re not using this feature yet, you need to – there are so many benefits.

  • No extra Dynamics GP licenses needed
  • Super-fast: <1 second for thousands of rows of data
  • Slice & dice your data with all the familiar functions of Excel
  • Real-time data coming straight out of Dynamics GP

How to add a field to your Refreshable Data Connection from Dynamics GP to Microsoft Excel

  1. Existing Connections.
  2. Choose the “Connection in this workbook.” Double-click on it.
  3. In the Data Import window, click on the Properties button.
    Refreshable Data Connection in Dynamics GP
  4. Go to the “Definition” tab at the top and find the “Command text” line. This is the SQL query telling the connection what data to bring in to Excel.All the field names are inside square brackets, so to add in the Batch Number field, add [Batch Number] after the other fields in the list, but before the “from SalesTransactions” or table name.
    Refreshable Data Connection in Dynamics GP
  5. Click on OK and then Yes, we want to make this change. You’ll get this huge message box on the screen confirming you want to do this.
    Refreshable Data Connection in Dynamics GP
  6. Next, Cancel – we just want the data to go to our existing area in Excel.
    Refreshable Data Connection in Dynamics GP

See how easy that was? The hardest part for me was figuring out what the field was called in Dynamics GP.

Want more information? No problem. We recently did a 30-minute webinar on Refreshable Data Connections for Dynamics GP and Excel. Watch the recording here.

Leave a Reply