You, Me and Dynamics GP
Over the last couple of weeks we’ve looked at some SmartConnect/Excel integration items for Dynamics GP. I’ve got one more for you.
We were integrating from SmartConnect to GP using Excel as the source. We’d had some problems with filtering incoming data with this version of SmartConnect. The spreadsheet source had 20 rows of headers, one for each potential company, but most of them were blank. These were fixed and populated by data from another excel tab. Below the headers were detail lines that corresponded to the headers.
So how was I going to filter out just the populated lines? I would have to do it in Excel. I dreaded writing some kind of array formula to make it happen and then I remembered ODBC.
As a test, I used Excel’s ODBC functionality to look back into the sheet of the same workbook I was in. This let me write a simple, SQL-like “Select * from blah blah blah” to filter the data into a new Excel sheet. I repointed the integration to the new Excel sheet and it worked perfectly. Problem solved. This was just way too easy.
