Microsoft Dynamics AX (Archived)

RecId field to determine a duplicate entry

(0) Share

Report

Posted on by Community Member

Hi Folks,

I am new in Dynamics AX development and developing a method to detect a duplicate entry.

So far, the customization I am into is working good.

But, part of the code confused me a lot, I found it in other source to detect duplicates.

I am doing this on

WMSJournalTrans Table

According to it, to test if an entry has duplicate entry,

count(RecId)

then

if (WMSJournalTrans.recId > 1)

-Just wondering how this happen when the RecId is not the same value like below,

ItemId: 0000999

RecId: 5637205687 (First entry)

The duplicate entry with the same ItemId has

RecId: 5637209829  (Second Entry)

Thanks in Advance.

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

I think you may be confusing count(RecId) which merely counts the number of records returned by a query, with COUNT(DISTINCT RECID) as you might execute as direct SQL which returns the number of unique RecId values in the result set. Using count(RecId) is very common in X++ because the result, a BIGINT number, fits in the RecId field, whereas count(AccountNum) does not. Other types of aggregate functions do work in their field types, i.e. maxof(TransDate), and so on. The DISTINCT is not implied in X++.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Hi Brandon Wiese,

Thanks for your swift reply.

Can you explain me this?

ItemId RecId 0000991 5637205684 0000993 5637205685 0000996 5637205686 0000999 5637205687 (Duplicate) 0001000 5637205688 0000999 5637209829 (Duplicate)

RecId is very unique from each other, Or there is other fields involve when using the RecId? like it also look at ItemId field etc?

Thanks.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

What do you want to explain there? You have six records, each with its own RecId assigned by AX kernel when creating records, which must be unique. And the you have an ItemId fields, which clearly isn't set to enforce unique values, as you have duplicate values there. There is nothing strange to see.

Values of ItermId field and RecId field has nothing to do with each other.

Regarding your code, you want to detect duplicate values in the ItemId field, therefore you should add the grouping by ItemId.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Hi Martin,

I just want to understand how does count(RecId) helps in detecting a duplicate entry. Because, As I can see, RecId by itself is unique.

Sorry if I can't understand its use, Just finish the development training few weeks ago.

Below is my code.

while select count(RecId) from wmsJournalTrans group by wmsJournalTrans.ItemId where WMSJournalTrans.inventTransRefId == this.inventTransRefId && WMSJournalTrans.journalId == this.journalId { if (wmsJournalTrans.RecId > 1) { setPrefix(strFmt("Current Journal: %1", journalId)); infolog.add(Exception::Warning, strFmt("- Item No. %1 has %2 duplicates.", wmsJournalTrans.ItemId, wmsJournalTrans.RecId)); } }

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

What problem do you have with your code? I though this is what you wanted.

The query gives you the number of records grouped by ItemId. If a given ItemId is used just once, you'll get number 1. If the same ItemId is used for more than one record, you'll get a number higher than one. You seem to be aware of it and therefore you have a condition for RecId > 0. So what's the problem?

It could be made more efficient by using the HAVING clause, which would allow you to filter out records with count(RecId) = 1 already in database, but unfortunately it's not supported by select statements and you would have to use the query framework.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Martin,

Thank you for your reply.

There is no problem in the code, In fact it is working properly.

I am just confuse on how it work, just want to fully understand how it identify duplicates because X++ is different from other programming language (at least in my point of view).

I am just new in the X++ and I am from PHP - code igniter framework.

Ok, I understand now how it worked.

Thanks.

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

If you ever used SQL from PHP to query relational databases such as MySQL, you would find the syntax very familiar. Nevertheless PHP is very different from SQL; they're different kinds of languages. (Notice that I didn't mention X++, because that's not the main point here.)

Therefore learning basics of SQL may be very beneficial to you. Also, regarding X++-specific syntax of select statements, refers to AX documentation: Select Statement Syntax [AX 2012].

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

If your question is now answered, please don't forget to close the thread by marking the replies forming the answer (notice "Did this answer your question" beside each reply).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Martin,

Thank you for the information. I am just kind of confuse on the approach of X++.

Thanks.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Copy that.

Thanks.

Was this reply helpful? Yes No