web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Integration Manager: A...
Microsoft Dynamics GP (Archived)

Integration Manager: Advanced ODBC integration, how to combine rows into a single invoice?

(0) ShareShare
ReportReport
Posted on by FzZzT 135

Hello,

I'm helping a SMB change their Linux-based web site; they have GP 10.0. I finally have the integrations working, pulling data from SQL via ODBC. It works, but it creates a new invoice in GP for every line item in the query data, instead of grouping by order ID (for the order integration). I've tried messing with "is key" and some other settings, but nothing has worked...

How can I make GP combine multiple rows for the same order into a single invoice? Is it possible with ODBC and a custom SQL statement?

Thanks,

Josh

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at

    Break the integration into a header and detail section. Then create a link between the header and detail on order number. Hopefully in the header section you at least have order number, customer number, date and order total. Then in the detail section have item number, quantity, unit cost. If you look in your IM folder you will see a sample SOP integration using two source files. That should get you going.

  • FzZzT Profile Picture
    FzZzT 135 on at

    Oh, I only have one query that gets everything... I'll try breaking it up. Thanks!

  • FzZzT Profile Picture
    FzZzT 135 on at

    Well, I broke it into two, but it hasn't helped. I looked through the sample SOP integration, and mine is really similar (maybe even simpler, only two tables). I have the relationship set up from the order table to order detail table on order_id. I don't have a Document ID set (it's Default), but the old integration does it that way also...our order_id is put into a user-defined field. Could this be the problem? I did try with order_id for Document No but it only integrated a single line-item for my test order. I tried with my custom query and Simple ODBC for the detail table, and no dice...ugh. :(

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at

    Can you paste your two queries here?

    If you preview the two data sets in IM do you see what you expect to see?

    When you are doing you mapping, are the header fields using the header fields and the details fields using the detail fields?

  • FzZzT Profile Picture
    FzZzT 135 on at

    I think I finally figured this out. All of your suggestions helped. I didn't notice the preview menu item before, but that revealed a duplication in the order header query. Splitting them up makes sense too, I just has a poor assumption with that one, thinking GP would "figure it out". I think one order_id column was being pulled from the detail table and not the header table. Lastly, I think I needed to change "Record Source" option for the Items section. It was set to the order header table, and I think changing it to the order details table made GP lump them together instead of creating separate invoices. I'm having the users test, but hopefully this is all set now...

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans