Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Update Records
Microsoft Dynamics AX (Archived)

Update Records

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi Community,

I have a class which extends from RunBase, which is programmed to Import Data.

I want to update the Records.

Usually when I try to insert the Records from the csv file again, I get an error "The record is already there, can not fill in record"

But now I want it to be ignored and the record should be updated, no mather if it already exists or not. And if the record has been changed in the csv file it should updated it in AX as well.

Any recomendations ?


Class: SYCimportData_Update

Methods:
classDeclaration
main
run
dialog
getFromDialog
pack
validate
unpack
doForEach

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Update Records

    Hi People, first of all I would like to thank You for Your help.

    I got a solution for my Problem.

    I first wrote a find method for my table, in which I can identify one record with my PK (SycAccountNum) .

    ----------------------------------------------------------------------------------------------------------------------

    public static SycStagingTable find(SycAccountNum_WEB _webaccount, boolean _update = false)

    {

       SycStagingTable         stagingtable;

       ;

       stagingtable.selectForUpdate(_update);

       if (_webaccount)

       {

           select firstOnly stagingtable

               where stagingtable.AccountNum_WEB == _webaccount;

       }

       return stagingtable;

    }

    ----------------------------------------------------------------------------------------------------------------------

    And then in my doForEach Method I added this

    private void doForEach(container _record)

    {

       SycStagingTable         stagingtable;

       SycAccountNum_WEB       accountnumweb;

       ;

       accountnumweb   = conPeek(_record,4);

       stagingtable    = SycStagingTable::find(accountnumweb, true);

       stagingtable.LastName                = conPeek(_record,1); //first Column

       stagingtable.FirstName               = conPeek(_record,2); //second Column

       stagingtable.AccountNum              = conPeek(_record,3); //third Column

       stagingtable.AccountNum_WEB          = conPeek(_record,4);

       stagingtable.AddressStreet           = conPeek(_record,5);

       stagingtable.Housenumber             = conPeek(_record,6);

       stagingtable.AddressZipCodeId        = conPeek(_record,7);

       stagingtable.AddressCity             = conPeek(_record,8);

       stagingtable.AddressCountryRegionId  = conPeek(_record,9);

       stagingtable.Email                   = conPeek(_record,10);

       stagingtable.Phone                   = conPeek(_record,11);

       stagingtable.CustGroupId             = conPeek(_record,12);

       if (stagingtable.validateWrite())

       {

           stagingtable.write();

       }

       else

       {

           throw error("@RET2939");

       }

    }

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Update Records

    Hi,

    In your find method signature, I could see so many parameters. In general, parameters that are mere enough to get the record from a table are used as mandatory and remaining as optional.

    For example, if AccountNum, firstName and lastName are sufficient then use the following signature.

    static SycStagingTable find(

   LastName                _lastname,

   FirstName               _firstname,

   AccountNum              _accountNum,

   SycAccountNum_WEB       _webaccount = "",

   AddressStreet           _street = "",

   SycHousenumber          _housenumber = "",

   AddressZipCodeId        _zipcode = "",

   AddressCity             _addresscity = "",

   AddressCountryRegionId  _country = "",

   Email                   _email = "",

   Phone                   _phone = "",

   CustGroupId             _custgroupid = "",

   boolean                 _update = false

                                           )
  • Thomas Vogt Poulsen Profile Picture
    Thomas Vogt Poulsen 345 on at
    RE: Update Records

    I would make use of Update_recordset and/or Insert_recordset to update your data. It is both faster and more elegant.

    Something like:

       // Table to be updated/inserted

       CustTable               custTable;

      

       // TempDB temporary table that resemples the file columns

       ImportedRecordsTempDB   importedRecords;

      

       // Import all records from file to importedRecords

       // Code that import all records from til to importedRecords...

      

       // Update all existing records in CustTable from file records

       update_recordSet custTable

           setting custTable.DlvMode = importedRecords.DeliveryMode

       join importedRecords      

           where importedRecords.CustAccount == custTable.AccountNum;

      

       // Insert all non-existing records in CustTable from file records

       // Please not this is sample code and CustTable records need more fields set

       insert_recordSet custTable

       (

           AccountNum,

           DlvMode

       )

       select CustAccount, DeliveryMode from importedRecords      

           where importedRecords.CustAccount == custTable.AccountNum

       notexists custTable2

           where CustTable2.AccountNum == importedRecords.CustAccount

      

    Please note the ImportedRecords should be a Temp table of type TempDB to be able to efficiently use it with Update_recordset and Insert_recordset. 

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Update Records

    I mean in your Table

    static SycStagingTable find(

       LastName                _lastname,

       FirstName               _firstname,

       AccountNum              _accountNum,

       SycAccountNum_WEB       _webaccount,

       AddressStreet           _street,

       SycHousenumber          _housenumber,

       AddressZipCodeId        _zipcode,

       AddressCity             _addresscity,

       AddressCountryRegionId  _country,

       Email                   _email,

       Phone                   _phone,

       CustGroupId             _custgroupid,

       boolean                 _update = false

                                               )

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Update Records

    For that You have to write a find Method then in your class right ?

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Update Records

    Hi,

    You can make a check on the target table to see where record exists with the given criteria and trigger insert/update (or) use write() method instead of insert() method on the target table.

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

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,494 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,305 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans