Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / unique field for RM20201
Microsoft Dynamics GP (Archived)

unique field for RM20201

(0) ShareShare
ReportReport
Posted on by jsteelecrm 365

Whenever a payment is made against an invoice, I want to use that information to update our CRM system. I am using scribe to do this. This process will run every hour and Scribe needs to understand which rows it has already processed. 

Great Plains records the date the payment was applied but not the time so if my process ran every hour, it would pick up all the transactions every day. Is there a setting that needs to be applied in order for it to record the time as well? The field Time1 currently states 1900:01:01 : 00:00:00:00.

My plan B was to create a custom table and whenever a row was processed, to take the id from that row and add to the custom table. If that table is checked on each transaction Scribe will know if it has done it already today. 

Which field in RM20201 is unique? I have heard not to use DEX_ROW_ID as it can change?  Would TRXSORCE be best to use? 

*This post is locked for comments

  • Suggested answer
    L Vail Profile Picture
    L Vail 65,271 on at
    RE: unique field for RM20201

    Hi,

    The primary key for the RM20201 table includes four fields. To identify a unique record, you need to look at the following fields:

    APTODCNM (apply to document number)

    APTODCTY (apply to document type)

    APFRDCNM (apply from document number)

    APFRDCTY (apply from document type)

    Therefore, you would need to use those four fields to figure out if the record has already been processed.

    Kind regards,

    Leslie

  • Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: unique field for RM20201

    DEX_ROW_ID can change and should not be used for integrations, particularly during  upgrades, sometimes tables may be dropped and repopulated regenerating the DEX_ROW_ID for all the rows.

    I wrote a blog post about this:

    http://www.timwappat.info/post/2015/09/02/DEX_ROW_ID-Dynamics-GP-column-integrations-or-Customisation

    Also if you run out of dex_row_ids and have to reset the values as I've had to, it would also change the dex_row_ids...

    http://www.timwappat.info/post/2016/11/08/Dynamics-GP-Arithmetic-overflow-error-converting-IDENTITY-to-data-type-int

    If you are aware of the danger, you can capture the row ids before and after any table rebuilds and remap your dependent tables or "last processed id" fields appropriately- personally I'd not risk this.

    Tim.

  • Tim W Profile Picture
    Tim W 2,925 on at
    RE: unique field for RM20201

    I'm trying to envision other issues that using the apply table might bring.  Problem I would foresee (and maybe it's not a big deal) but depending on process things could get unapplied as part of standard processing then re-applied somewhere else or even back on the same document later.  Just depends so might be you have to put some kind of a control around RM20101 so that you are considering the original and current transaction amounts also as far as what you display.

    Dex Row ID won't change (never heard that), but there are a few scenarios where it will disappear from this table as part of normal process.

    Couple thoughts.

  • jsteelecrm Profile Picture
    jsteelecrm 365 on at
    RE: unique field for RM20201

    TRXSORCE does look a poor choice. I just found a number of rows using the same number. Does your GP record the time in this table? In my other scribe maps I can set the net change to last modified date and time, with no time in this table my process fails.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: unique field for RM20201

    On second thought Dex_Row_ID is not a good choice

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: unique field for RM20201

    I am a user of scribe and I would not use TRXSORCE. I believe that field or the data from that field is used in the GL transactions table. I would suggest plan B. Just an FYI the DEX_Row_ID only changes when new records are added. The existing records maintain their DEX_Row_ID. You could use Dex_Row_ID as a flag  

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans