Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Tammy Chavez]

Good Afternoon,

I am working on an integration to pull in PO’s (not customer related) using IM with eConnect and keep running into a Duplicate Order error.  Can anyone provide me with a sample source file/mapping?  I have two sources in my integration.  See screenshots below:

Thanks in Advance.

——————————
Tammy Chavez
Senior Software Consultant
LightHouse Business Information Solutions, LLC.
Albuquerque NM
——————————

[Charles Allen]

It looks like the PO Import data source could be the problem. Are you importing multiple purchase orders? Do you have grouping in your data source to ensure only one row appears per PO in the header data source?

 

 

Charles Allen

Senior Managing Consultant | BKD, LLP

BKD Technologies

Office 713.499.4629

Cell 713.494.2104

 

 

****** BKD, LLP Internet Email Confidentiality Footer ******

Privileged/Confidential Information may be contained
in this message. If you are not the addressee indicated in
this message (or responsible for delivery of the message
to such person), you may not copy or deliver this message to
anyone. In such case, you should destroy this message, and
notify us immediately. If you or your employer do not consent
to Internet email messages of this kind, please advise us
immediately. Opinions, conclusions and other information
expressed in this message are not given or endorsed by my
firm or employer unless otherwise indicated by an authorized
representative independent of this message.

Any tax advice contained in the body of this email was not
intended or written to be used, and cannot be used, by the
recipient for the purpose of avoiding penalties that may be
imposed under the Internal Revenue Code or applicable state
or local tax law provisions.

These discussions and conclusions are based on the facts
as stated and existing authorities as of the date of this
email. Our advice could change as a result of changes in the
applicable laws and regulations. We are under no obligation
to update this information if such changes occur. Our advice
is based on your unique facts and circumstances as you
communicated them to us and should not be used or relied
on by anyone else.

——Original Message——

Good Afternoon,

I am working on an integration to pull in PO’s (not customer related) using IM with eConnect and keep running into a Duplicate Order error.  Can anyone provide me with a sample source file/mapping?  I have two sources in my integration.  See screenshots below:

Thanks in Advance.

——————————
Tammy Chavez
Senior Software Consultant
LightHouse Business Information Solutions, LLC.
Albuquerque NM
——————————

[Tammy Chavez]

Hi Charles,  Yes I am importing multiple PO’s and they are grouped by the PO#  There are actually 13 total PO’s to be imported.

——————————
Tammy Chavez
Senior Software Consultant
LightHouse Business Information Solutions, LLC.
Albuquerque NM
——————————
——————————————-

[David Joosten]

Hi,
Have you defined a query relationship between PO Import and PO Line for the PO number? Also, if an item occurs more than once in the import, you will need to be sure that you have setup the IM options correctly to handle that.

——————————
David Joosten
Enterprise Architect
Premier FMCG (Pty) Ltd
Waterfall City
——————————
——————————————-

[Andrew Kennedy]

Tammy,
It looks as if you have the same PO number repeated for different Vendor IDs.  The PO# will need to be unique per Vendor.

——————————
Andrew Kennedy
Principal Consultant
Socius
Brecksville OH
——————————
——————————————-

[Blair Christensen]

Tammy,

This is what I have used to import my PO’s.  I set up two tables: a header and a detail.  There is an extra key field in the detail that provides the link between the two for the integration.  Then you load the information from your source into the two tables, then match them up in the integration, link the header and detail, and you should be good to go.  (There are additional tables which could be linked in for extended information, but not many need them.)

CREATE TABLE INTG.SalesOrderImportHeader (

DocumentKey INTEGER NOT NULL --A unique identifier for just this record. Links to Detail

, DocumentType INTEGER NOT NULL DEFAULT 1 --{1: Quote, 2: Order, 3: Invoice, 4: Return, 5: Back Order}

, DocumentTypeID VARCHAR(15) NOT NULL --Check your Document Types and assign this

, DocumentNumber VARCHAR(17) NOT NULL --Sales Order/Invoice #

, DocumentDate DATE NOT NULL --Document Date or system date

, DefaultSite VARCHAR(10) --Default Site. If none do not set in Integration

, BatchID VARCHAR(15) NOT NULL --Default Batch. I use a static one but you can dynamically create it

, CustomerNumber VARCHAR(15) NOT NULL --Remember this must be the GP Customer ID!

, CustomerName VARCHAR(64) NOT NULL --Customer Name

, CustomerPONumber VARCHAR(20) --Customer PO Number (leave blank if none)

, CurrencyID VARCHAR(15) NOT NULL --Check your Currency ID. Probably 'Z-US$'

, TradeDiscount NUMERIC(19,4) --Invoice level Trade Discount

, FreightAmount NUMERIC(19,4) --Invoice level Freight Charge

, MiscellaneousAmount NUMERIC(19,4) --Invoice level Misc Charges/fees

, PaymentAmountReceived NUMERIC(19,4) --Usually blank (payment comes later)

, TermsDiscount NUMERIC(19,4) --Usually blank

, TermsDiscountReturned NUMERIC(19,4) --Usually blank

, CommentID VARCHAR(15) --Usually blank

, CommentText VARCHAR(MAX) --Usually blank

, Note VARCHAR(MAX) --Usually blank

, GLReference VARCHAR(30) --Usually blank

, IntegrationFlag INTEGER --{0: Ready to integrate, 9: Integrated Successfully, 1-8: Various intermediary stages/error conditions}

)

;

CREATE TABLE INTG.SalesOrderImportDetail (

DocumentKey INTEGER NOT NULL --Links back to Header

, LineNumber INTEGER NOT NULL --Unique row number for this document

, ItemNumber VARCHAR(30) NOT NULL --GP Item Number

, UnitofMeasure VARCHAR(8) NOT NULL --Unit of Measure (see UofM Schedule)

, ItemQuantity NUMERIC(19,4) NOT NULL --Quantity Sold

, UnitPrice NUMERIC(19,4) --Unit Price (if known)

, ItemDescription VARCHAR(100) --Item Description

, ExtendedPrice NUMERIC(19,4) --Unit Price (if known) * Quantity Sold

, QuantityBilled NUMERIC(19,4) --Usually Blank

, QuantityFulfilled NUMERIC(19,4) --Usually same as ItemQuantity

, QuantityCancelled NUMERIC(19,4) --Usually Blank

, QuantityBackOrder NUMERIC(19,4) --Usually Blank

, QuantityToOrder NUMERIC(19,4) --Usually Blank NOTE: THIS IS NOT THE SAME AS ITEMQUANTITY

, UnitCost NUMERIC(19,4) --Unit Cost (if known)

, DropShip INTEGER --Usually Blank {True/False}

)

;

These should map straight across to the integration fields.  Then all you need to go is create something to load your data into this table and another to clear it out either before or after load.

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-

[Jo deRuiter]

In both your PO Sources, mark “PO Number” as “Is Key” and ensure that in your query you link up all of the related fields between the two sources – but don’t use the “Group By” in the Source file settings.

——————————
Jo deRuiter
Lead Techno Functional Consultant
Aisling Dynamics Consulting
Canton GA
——————————
——————————————-

[Tammy Chavez]

Tried this and now I don’t get 13 po’s  queried I get 241.  How do I get to do 13?  Also still getting the duplicate order error.

——————————
Tammy Chavez
Senior Software Consultant
LightHouse Business Information Solutions, LLC.
Albuquerque NM
——————————
——————————————-

[Tammy Chavez]

Here are my settings for the PO import.

13 Different PO’s

241 Lines for all 13 PO’s


Relationship 

Still get DUPLICATE ORDER message

——————————
Tammy Chavez
Senior Software Consultant
LightHouse Business Information Solutions, LLC.
Albuquerque NM
——————————
——————————————-

[Jen Kuntz]

Hi Tammy,

Two thoughts for you…

1. On the error itself, that isn’t telling you there is a problem in your data to be imported, that is telling you those PO#’s already existing in Dynamics GP. Is there a chance any of your imports were successful and the PO#’s in your file are actually already in GP?

One trick I’ve used in the past is to try to manually type one entry in, exactly as my data appears, and see what the error is in GP itself. There is the odd time when the error message you get in eConnect isn’t actually the real error. It doesn’t happen often but at times, you could be chasing your tail in one direction and only find out hours later the problem is something different entirely. By manually mimicking it in GP, using only the fields & mapping you have in your file, GP may show an error message.

2. ON the linking between PO Header and PO Line, you don’t need to have a relationship between each field if the PO# is unique. You should only need to link from PO# in the Header file to the Line file and all the related lines would appear.

Hope that helps.
Jen

——————————
Jen Kuntz, CPA, CGA
Microsoft MVP, Business Solutions
Kuntz Consulting Inc.
Cambridge, ON, Canada
——————————
——————————————-

[Chris Donnelly]

Great advice from Jen.  That’s what I always do.  Also, here are a couple more things you can try…
I would try removing all of the fields in your grouping except PO#.
Go into SQL and see if you can find duplicate POs.

——————————
Chris Donnelly
InterDyn BMI
Roseville MN
——————————
——————————————-

[Tammy Chavez]

Happy Monday all!  

I finally figured this out.  I had the Sequence line the same number for all items on the PO.  Once I changed it to be the same as the Line number (1, 2, 3, 4, etc.)  It worked beautifully!  

Now on to the Receiving import.  YIKES!!!

——————————
Tammy Chavez
Senior Software Consultant
LightHouse Business Information Solutions, LLC.
Albuquerque NM
——————————
——————————————-