[David Morinello]

?Hopefully a quick question…

What is it telling me if field MODIFDT for RM00101 is not updated, but DEX_ROW_TS is updated?

I have a large list of Customers that I need to check if any field it is being modified or not. DEX_ROW_TS is updated on the whole list to today’s date, but only 14 had today’s date for MODIFDT. Were they all modified or only 14?

——————————
David Morinello
Senior Dynamics GP Systems Architect
Ascend Learning, LLC
Leawood KS
——————————

[Steve Erbach]

,

Very interesting question. I had thought that ?DEX_ROW_TS would be updated whenever someone touched a record… but this mass update is unexpected.

I’ll be watching this thread…

Regards,

——————————
“Sparkly” Steve Erbach – Business Analyst
GLC Minerals, LLC – Green Bay, WI
Co-Chair, GPUG WI (Green Bay) Chapter
GP 2016 R2 (16.00.0579) / MR 2012 CU16
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: twitter.com/serbach
——————————
Welcome to Dynamics!
You do not have permission.
Call Steve.
(hat tip: Lou Spevack)
——————————
——————————————-

[Jo deRuiter]

?Hi

This may be obvious, but do you have any integrations that are running that might affect any of the RM00101 or related fields? including addresses and email addresses?

Any third party apps that are linked to GP?

——————————
Regards,

Jo deRuiter
“That GP Red Head”
Senior Financial Systems Consultant
Heartland Business Systems, LLC
Eau Claire WI
770-906-4504 (Work)
770-906-4504 (Cell)
——————————
——————————————-

[David Morinello]

Jo,
?So, so many moving parts. Many and varied integrations. Probably the most significant are the Scribe CRM integrations for Customers.

• There are over a million rows in RM00101.
• I am looking at a key customer list of about 7K records.
• About 15 to 20 of these change MODIFDT date daily
• All 7K+ change DEX_ROW_TS daily

What I am doing next is the take a snapshot (yea SmartConnect!) of the RM00101 table (WHERE [CUSTNMBR] IN ) a few times a day to see what fields, if any, change.

Bob, thanks for the link! I read it earlier. It is interesting info, however it doesn’t really help explain my MODIFDT vs.  DEX_ROW_TS  question. Why one and not the other? If any field in a record changed, wouldn’t MODIFDT change also?

——————————
David Morinello
Senior Dynamics GP Systems Architect
Ascend Learning, LLC
Leawood KS
——————————
——————————————-

[Jo deRuiter]

?hI

Here’s my theory, but it will take testing – the DEX_ROW_TS can be affected by related table updates.  I know, in my experience, if I DIRECTLY update the Customer Master Card….general information, options, etc… even the accounts that the MODIFDT is changed.

IF I end up running updates on or changing related fields like the email settings for the customer the DEX_ROW_TS gets updated, but NOT the MODIFDT.

I can help do some testing, but I do know that in a lot of cases the DEX_ROW_TS can be changed based on tables RELATED to the table that the DEX_ROW_TS is in and not necessarily that affected table directly…   I hope I’m making sense.

That’s why if you have an integration running that might affect the related tables – like updating email addresses, etc then that could be the reason that the DEX_ROW_TS is changing and not the MODIFDT

——————————
Regards,

Jo deRuiter
“That GP Red Head”
Senior Financial Systems Consultant
Heartland Business Systems, LLC
Eau Claire WI
770-906-4504 (Work)
770-906-4504 (Cell)
——————————
——————————————-

[David Morinello]

?Jo,
A nice theory that makes perfect sense. Many of these customer get the addresses (RM00102) updated often, so that could very well be what I am seeing here.

I will add to my tests some checks on the other related RM tables over the next few days.

Thanks!

——————————
David Morinello
Senior Dynamics GP Systems Architect
Ascend Learning, LLC
Leawood KS
——————————
——————————————-

[Frank Heslin]

Hi ,

First, I want to say that when I read the thread subject, I was 99% sure that it was going to be one of your posts, LOL!

I think the post from that refers to a blog by sort of does explain why one & not the other changed.

I made a change on the Customer Address Maintenance card (RM00102) & this is what I found:

For explanation purposes:
RM00101 = Source Table
RM00102 = Related Table

RM00102.MODIFDT = today
RM00102.DEX_ROW_TS = today & now

RM00101.MODIFDT = 2016-01-26
RM00101.DEX_ROW_TS = today & now

So, if any changes are made to one of the Related tables, the DEX_ROW_TS gets updated in the Source table as well.
???I think you need to expand your research into “Related” tables.

Happy searching!

——————————
Frank Heslin
Financial Systems Administrator
ExamWorks, Inc.
Atlanta GA

GPUG Advisory Committee Member
Summit Track Leader
GPUG All Star 2016

GP2015 R2
MR CU14
——————————
——————————————-

[Jo deRuiter]

?

Wow!  A theory of mine was right!!!   This is a red letter day! LOL!!

Thanks for going to the trouble to prove this out, I was VERY curious!?

——————————
Regards,

Jo deRuiter
“That GP Red Head”
Senior Financial Systems Consultant
Heartland Business Systems, LLC
Eau Claire WI
770-906-4504 (Work)
770-906-4504 (Cell)
——————————
——————————————-

[Bob DiPasquale]

Here’s a good read about DEX_ROW_TS and it’s mysterious behaviors … Beware DEX_ROW_TS for data synchronisation | Dynamic Code Blocks

Timwappat		remove preview
Beware DEX_ROW_TS for data synchronisation | Dynamic Code Blocks DEX_ROW_TS is a SQL server database field. It is found on some tables in Dynamics GP ERP system(since V10). The field contains a UTC timestamp that is “stamped” whenever that row in the database is changed, setting it to current time and date. View this on Timwappat >

——————————
Bob DiPasquale
——————————
——————————————-

[David Musgrave]

Modified Date is probably only updated when the record is changed from the maintenance window.

DEX_ROW_TS is updated by a SQL trigger when any change to the record is made.

Modified Date will only have the date, DEX_ROW_TS will have the date and time in UTC.

David

——————————
David Musgrave MVP, GPUG All-Star

Managing Director
Winthrop Development Consultants

Perth, Western Australia

http://www.winthropdc.com
——————————
——————————————-

[John Arnold]

To expand on David’s comment:

The zDT_RM00101U trigger on RM00101 updates the Time Stamp (DEX_ROW_TS) with the GETUTCDATE() function.   As it is said here (GETUTCDATE (Transact-SQL)), the database time zone offset is NOT included…  When I run SELECT GETDATE(), GETUTCDATE()  I get:

GETDATE() = 2018-03-07 06:23:25.700
GETUTDDATE() = 2018-03-07 14:23:25.700

When I run  CONVERT(FLOAT, GETDATE() – GETUTCDATE())  to get the my time zone offset, I get  -0.33333333…  which is -1/3 of a day = -8 hours.  That matches my pacific time zone.   I’m not sure what will happen next week once Daylight Savings Time has started…

So, for most of us, the value in the time stamp will need to be modified to get the time and date you are looking for.

I hope this helps!

——————————
John Arnold
Senior Software Engineer
US Digital
Vancouver WA
——————————
——————————————-