web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Restrict Duplicate Cus...
Microsoft Dynamics AX (Archived)

Restrict Duplicate Customer Reference value when Creating new Sales Order.

(0) ShareShare
ReportReport
Posted on by Naveen Mishra 4

Hello Expertise.

When I create new sales order there is a field named Customer Reference .

requirement is , if  record already  exist in customer reference field system should provide warning message.

Please Suggest .


Thanks!

Naveen Mishra

*This post is locked for comments

I have the same question (0)
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 299,373 Super User 2025 Season 2 on at
    RE: Restrict Duplicate Customer Reference value when Creating new Sales Order.

    Hi Naveen,

    What type of answer are you looking for? It would be a customiztion. Do you need guidance how to solve this?

    If the requirement was an error, you.could have done it with a new index on the table where duplicates were not allowed.

    A warning requires x++ coding on e.g. the validate write method of the table.

  • Naveen Mishra Profile Picture
    Naveen Mishra 4 on at
    RE: Restrict Duplicate Customer Reference value when Creating new Sales Order.

    Hi André Arnaud,

    Thanks for response.

    Yes i need guidance how to solve this.

    As you suggest i create new index for customer reference  field in Sales Table and set allow duplicate properties to NO.

    But  when i Synchronize Sale Table, I receive below error.

    SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.SALESTABLE' and the index name 'I_366CUSTOMERREFIDX'. The duplicate key value is (5637144576, brmf, ).

    Can you Please suggest for above error and also for warning message in X++ Code.

    Thanks.

  • Verified answer
    Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: Restrict Duplicate Customer Reference value when Creating new Sales Order.

    Hi Naveen Mishra,

    You are getting this error because you already have 2 records with same customer reference in data base so index could not be created. If you want to follow this path you need to modify data to make it unique by this fields before creating new index. Also please bear in mind that it will throw error and won't let user to save a record.

    If you want to get warning you need to write code on salesTable validateWrite method that will check if there is already record with same customer reference, something like

    select firstOnly RecId from salesTable
    where salesTable.CustomerRef == this.CustomerRef
       && salesTable.RecId       != this.Recid;

if (salesTable)
{
    ret = checkFailed("Your warning message here");
}


  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 299,373 Super User 2025 Season 2 on at
    RE: Restrict Duplicate Customer Reference value when Creating new Sales Order.

    Hi Naveen,

    The suggestion for the index was only intended when you would have an error and don't allow duplicates. The reason why you get the error is probably because a lot of orders do'n't have this value filled.

    The code suggestion from ievgen will work, but you can enhance it with 2 additional features:

    - Ignore blank customer references

    - Customer reference unique per customer. So another customer could use the same reference.

  • Naveen Mishra Profile Picture
    Naveen Mishra 4 on at
    RE: Restrict Duplicate Customer Reference value when Creating new Sales Order.

    Thanks Guys for response.

    Now when I enter Customer reference value I am receiving warning message if value is already exist through Validate Right Method.

    But Now in requirement have some changes.

    I created a new Base Enum Field named CheckPO in Sales Parameter Table, And this field is Set in Account Receivable Parameter on general Tab. 

    Base Enum have three elements Named 1. Reject Duplicate 2. Accept Duplicate 3. Warn in case of Duplicate .

    Now when user  enter value in customer reference field -  

    1.In case of Reject duplicates: System should pop up message and should not allow the user to proceed further unless the data entered in the customer reference field is removed and should not allow to save.

    2. Just Accept Duplicate .

    3.Warn in case of duplicates: System should pop up message and should allow user to proceed further and should allow saving the data.

    For Achieve this in which method need to right code in Sales table, its Validate Right method Or need to create new method ?

    Please suggest  where need to right  X++ code and  what should i need to write in new X++code to achieve this.

    Thanks! 

     

     

     

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 299,373 Super User 2025 Season 2 on at
    RE: Restrict Duplicate Customer Reference value when Creating new Sales Order.

    Hi Naveen,

    You can implement the full code in the validateWrite field.

    The pseudo code could be like:

    Get enum field value from Sales parameters

    if (parameter enum != enum::accept)

    {

       select on salestable for possible duplicate (like mentioned above and this code is working for you now)

       if (salesTable.RecId)

       {

           switch enum

               case enum::reject

                   throw error(...

                case enum::warning

                    warning(...

       }

    }            

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

#2
NNaumenko Profile Picture

NNaumenko 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans