150
I am creating an integration of GP invoices to Dynamics CRM invoices. I have exported data from the SOP30300 table for the invoice line items and I would like to have a uniqiue id in my staging table. Can I combine SOPNUMBE and LNITMSEQ to create a unique id. I have read that I should never consider using DEX_ROW_ID as a unique id as it can change during a GP Upgrade.
*This post is locked for comments
Since you are creating a staging table, you can create a column of data type UNIQUEIDENTIFIER to store a GUID for each line inserted in it, i.e.:
CREATE TABLE SOP_LINE_STAGE ( ItemNumber VARCHAR(50), ComponentSequence INT, SOPNumber VARCHAR(30), LineItemSequence INT, QuantityInvoiced NUMERIC(19,5), UnitPrice NUMERIC(19,5), ExtendedPrice NUMERIC(19,5), UniqueId UNIQUEIDENTIFIER DEFAULT NEWID() );
You don't need to engineer some complicated key to keep track of a unique line value.
The primary key for SOP30300 is
SOPTYPE
SOPNUMBE
LNITMSEQ
CMPNTSEQ
If all your documents are invoices set SOPTYPE to 3 and CMPNTSEQ to 0 if yo have no components of bills of materials. DEX_ROW_ID can be used under certain circumstances but this is not one of them.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (