Do not allow non-duplicates in string value, except empty strings

(0) Share

Report

Posted on by lukbel

280

Hi all

Running Dynamics AX 2012 R3 CU9

Have run into a business requirement where we would like to ensure SalesTable.PurchOrderFormNum is set to not allow duplicates.

Normally I would do this with an index, but the field is blank by default, and does not get filled in for every order. I think the index would treat all the blank values as "duplicates".

Is there a way to not allow duplicates, but exclude empty values?

Cheers

Luke

*This post is locked for comments

All responses (2)

Answers (1)

lukbel 280 on at

Like (0)

Report

RE: Do not allow non-duplicates in string value, except empty strings

Thanks for all your help folks :)
Verified answer

Sohaib Cheema 46,612 User Group Leader on at

Like (3)

Report
RE: Do not allow non-duplicates in string value, except empty strings

Hi,

one of the ways can be writing logic on validateWriteServer method of SalesTable.

Create a new method on sales table as following

public boolean PurchOrderFormNumAlreadyExists(CustPurchaseOrder _numberToFind,RefRecId _currentRecord) { boolean ret; SalesTable lclSalesTable; ; ret = false; lclSalesTable.clear(); select firstOnly PurchOrderFormNum from lclSalesTable where lclSalesTable.PurchOrderFormNum == _numberToFind && lclSalesTable.RecId != _currentRecord; if(lclSalesTable.PurchOrderFormNum) { ret = true; } return ret; }

Then call this method from validateWriteServer by writing following lines of code just above of, return ok;

if(this.PurchOrderFormNum && this.PurchOrderFormNumAlreadyExists(this.PurchOrderFormNum,this.RecId)) { ok = checkFailed('The Purchase Order form number already exists. Please use a new unique number.'); }