Open Forum

 View Only
  • 1.  #integrations

    Posted Aug 24, 2022 12:40 PM
      |   view attached
    Hello all!

    I am hoping someone can help me with an integration I have been working on for a new company.  I need to import sales docs but have the distribution for one of the docs to multiple revenue accounts; having the receivable account automatically generate and sum up all the amounts from the revenue accounts.  I am inserting an example I received of what could be done what it would look like, as well as how I have the integration set up so far.
    I keep getting errors that the invoice # already exists  - it doesn't even integrate one of the documents which I find to be odd.
    What should happen;

    How I have it set up;  please see attached.

    Any help would greatly be appreciated!

    Ann

    ------------------------------
    Ann Strasser
    Accounting Manager
    Dungarvin
    Mendota Heights MN
    ------------------------------

    Attachment(s)

    docx
    INTEGRATION SETUP.docx   127 KB 1 version


  • 2.  RE: #integrations

    TOP CONTRIBUTOR
    Posted Aug 25, 2022 08:24 AM
    Hi Ann,

    Take NAME and AMOUNT out of your Group By selection for the header, and that should help. It looks like your source file screen shot is one invoice and you are including columns that have different values in your group - so it is trying to create two invoices and causing the duplicate error.

    Hope that helps,

    ------------------------------
    Mark Wiley
    Dynamics Credentialed Professional
    Senior Solutions Architect
    GraVoc
    Ohio Office
    ------------------------------



  • 3.  RE: #integrations

    Posted Aug 25, 2022 03:17 PM
    That worked!  Now I am left with trying to figure out how to get the AR receivable account to populate based off of what is on the customer card.  I have set it up to be default in hopes of it adding up the amounts from all the different revenue accounts, but it is not pulling in the account number or populating with any amount.......Any thoughts?

    ------------------------------
    Ann Strasser
    Accounting Manager
    Dungarvin
    Mendota Heights MN
    ------------------------------



  • 4.  RE: #integrations

    TOP CONTRIBUTOR
    Posted Aug 26, 2022 08:26 AM
    Hi Ann,

    Hopefully you have someone on staff that can write SQL scripts.  If not, you can check with your GP partner.  They should be able to handle this.

    You can write a SQL script that will execute after each invoice or after each file to modify the GL accounts.  It is added on the far right tab named 'scripts'.  It will take a little SQL skill to write the SQL script.  A little programming skill to call the SQL script from within IM.  And a little GP database skill to know the columns to interrogate and update (i.e. ACTINDX on RM10101 table). 

    IM does include sample scripts for reference.

    Good luck.

    Chris


    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------



  • 5.  RE: #integrations

    Posted 30 days ago
    Thank you Chris.   I have completed what I could and have now reached out for the SQL script portion.  Thank you for your help.

    Ann

    ------------------------------
    Ann Strasser
    Accounting Manager
    Dungarvin
    Mendota Heights MN
    ------------------------------



  • 6.  RE: #integrations

    TOP CONTRIBUTOR
    Posted Aug 26, 2022 08:35 AM
      |   view attached
    Are your source files text, CSV, etc.? You can change your header to Advanced ODBC and then summarize the Amount column. Check out this link:
    https://www.gpug.com/communities/community-home/digestviewer/viewthread?GroupId=247&MID=16931&tab=digestviewer

    Your script would be something like this:
    SELECT [Invoice #], Date, ID, SUM(Amount) AS DocAmount
    FROM sourceFile
    GROUP BY [Invoice #], Date, ID

    Note: I would change your headings to not have spaces or #-signs. They can gum-up the works.


    ------------------------------
    Mark Wiley
    Dynamics Credentialed Professional
    Senior Solutions Architect
    GraVoc
    Ohio Office
    ------------------------------



  • 7.  RE: #integrations

    Posted 30 days ago
    Thank you Mark.  I completed what I could get done and have now reached out regarding the script.  I don't have much experience with that at this point.  Thank you for your he;p!

    ------------------------------
    Ann Strasser
    Accounting Manager
    Dungarvin
    Mendota Heights MN
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role