Need A Quick Check by someone... Memo DMT Gotcha!

Epicor ERP 10
1

So… if you don’t include the memodate in the memo dmt template it sets all your memodates to the date of the DMT for all records updated…

This is the case today after I ran a DMT yesterday to update memo categories.

I restored a backup of our live environment to our test environment to get the original dates back.

Now I want to add those original dates to all of the records that I updated.

I am thinking I can query the EpicorLive.ice.MemoTable as m and then join the EpicorTest.ice.MemoTable as m2 on m.company = m2.company and m.sysrowID = m2.sysrowID.

Would that be sufficient to get the corresponding original date for my live data? I don’t think the sysrowID changes does it? In other words, a backup from yesterday should have the same sysrowID for memos as the ones that are in my live environment today, correct? Of course newly added memos won’t exist in test, but am I good here? Just take the memodate from the test table with that join?

Thanks in advance to anyone who followed along and might be able to confirm I am on the right track.

2

Assuming the “update” process doesn’t delete the prior record and create a new (with new SysRowID). Which I would doubt it does.

But to be safe, the Key fields should suffice, and they definitely shouldn’t be different between the backup and the live tables.

If this is something that is a future concern, might want to make a BPM so that if it updated by a specific user, it retains the original memodate.

Just curious, the Ice.Memo table also has a field named “MemoUserID”. Did that get updated too?

3

I kept the Memo.Username in my template… I don’t have any reason for why I kept that in my template and not the memodate, but I bet if you excluded it, it would change it to the user who ran the DMT.

Thanks for your input Calvin. I will do it on the keys too and see if I get a different result, it’s just the memo table has so many “key” fields… where do I stop, what do I include?

For example, is “relatedtoschema” also needed in the join? I would probably be able to tell from data dictionary, but I’ll ask it here too.

4

Well, lots to choose from. Looks like the following fields would suffice.
RelatedToSchemaName, RelatedToFile, RelatedToSysRowID, MemoNum

image
image850×433 19.9 KB

Or go with the last one :wink:

5

I love that .chm.

6

It’s funny cause the DMT template doesn’t ask for RelatedToSysRowID.

7

I think there are several combinations that can uniquely identify a record.

I’m almost surprised that DMT would accept the SysRowID field. It forces you to use VendorID in most places (which it uses to looks up the VendorNum to actually use), and doesn’t allow VendorNum.

Or does it?

8

I wasn’t using the sysrowID in the template… I needed to know the join because I was building the template and I wanted to ensure I was getting the correct dates from the Memo table in our test database so that the template would upload the correct data.

These are required for the memo:

image
image743×47 2.43 KB

9

I’ve never seen a DMT template that required a “BOName” column. Is this a “combined” (MemoCat and Memo) DMT type?

But now that I think about it, the Memo table belongs to Ice, not Erp. So the format of the data in the table probably requires knowing which BO to run it through.

10

I didn’t either! Thanks again for your help!