web
You’re offline. This is a read only version of the page.
close
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 / Finance | Project Operations, Human Resources, ... / How to validate the st...
Finance | Project Operations, Human Resources, ...
Suggested Answer

How to validate the staging data of data entities whose "Set based processing" flag is enabled

(1) ShareShare
ReportReport
Posted on by spidey1010 183
Hi ,
I have added two custom fields in my data entity - LogisticsAddressPostalCodeV3Entity. Since its set based processing flag is enabled the normal import doesnt work. So I added my code to copyCustomStagingToTarget. The records are imported successfully but i do want to validate them. I tried the relations on staging table, validateWrite method on staging table and parent table, but both didnt work.
 
Please suggest how to validate those 2 custom fields of staging table. 
I have the same question (0)
  • Suggested answer
    Mohamed Amine Mahmoudi Profile Picture
    Mohamed Amine Mahmoudi 26,431 Super User 2025 Season 2 on at
    Hi,
     
    I don't think that using the staging table to validate data is the right thing to do.
     
    I mean you can do it in the target table "LogisticsAddressPostalCode" with the validateWrite method.
     
    (Moved From Mirosoft Dynamics AX form)
  • spidey1010 Profile Picture
    spidey1010 183 on at
    HI Amine,
    I mentioned in my post that i have already tried writing the validate write method on both my parent and staging table but it didnt work.
  • spidey1010 Profile Picture
    spidey1010 183 on at
    I am already updating my custom fields in copyCustomStagingToTarget method. How can I validate them?
  • Anton Venter Profile Picture
    Anton Venter 20,346 Super User 2025 Season 2 on at
    Because the set based option is enabled in your case, for performance, the validateWrite method is not called by the system. You could validate the staging data  with a customisation before the copyCustomStagingToTarget method is called. How many rows are typically added per import?  What is the difference in duration between set based and row based?
  • spidey1010 Profile Picture
    spidey1010 183 on at
    If i validate my data before calling copyCustomStagingToTarget method, then on parent method it wil reset the transfer status. so should i write it as error ?
  • Anton Venter Profile Picture
    Anton Venter 20,346 Super User 2025 Season 2 on at
    I would go for status error because the data is invalid in that case.
  • spidey1010 Profile Picture
    spidey1010 183 on at
    Yes, even if i make the status as error, in the main table copyCustomStagingToTarget they will reset the status . How should I handle it?
  • spidey1010 Profile Picture
    spidey1010 183 on at
    what i did now is that I have added the same copyCustomStagingToTarget method after my next call by adding those 2 new fields which i need to import as well. But now what happens everything is going great, but the inserted value and updated vales doesnt get updated  so there  are no errors but the data doesnt import. Here is the code
     public static container copyCustomStagingToTarget(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)
    {
        container res;
        res = next copyCustomStagingToTarget(_dmfDefinitionGroupExecution);
        LogisticsAddressPostalCodeV3Staging staging;
        LogisticsAddressPostalCodeV3Staging stagingCountryRegion;
        LogisticsAddressPostalCodeV3StagingFormattedView stagingFormatted;

        LogisticsAddressCountryRegionId countryRegionId;

        LogisticsAddressZipCode addressZipCodeExisting;
        LogisticsAddressZipCode addressZipCodeInserted;
        LogisticsAddressZipCode addressZipCodeUpdated;

        container result;
        Int64 insertedCount, updatedCount;
        boolean successful;

        AddressFieldsHelper::resetPostalCodeTransferStatus(_dmfDefinitionGroupExecution);

        ttsbegin;

        // Validate and copy the formatted staging data by country region ID.
        while select CountryRegionId from stagingCountryRegion
                group by CountryRegionId
                where stagingCountryRegion.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
                    && stagingCountryRegion.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
        {
            successful = false;

            if (stagingCountryRegion.CountryRegionId)
            {
                countryRegionId = stagingCountryRegion.CountryRegionId;
            }
            else
            {
                countryRegionId = CompanyInfo::findDataArea(curext()).postalAddress().CountryRegionId;
            }

            // Set Default State
            AddressFieldsHelper::SetDefaultState(_dmfDefinitionGroupExecution);
            // Validate
            AddressFieldsHelper::validateStaging(_dmfDefinitionGroupExecution, countryRegionId);
            AddressFieldsHelper::validateCustomFieldStaging(_dmfDefinitionGroupExecution,countryRegionId);

            // Update the data in the physical tables.
            // Only certain fields may be updated based on the entity and entity key.
            addressZipCodeUpdated.skipDataMethods(true);

            update_recordset addressZipCodeUpdated
                    setting CityAlias = stagingFormatted.CityAlias
                    join stagingFormatted
                        where stagingFormatted.ZipCode == addressZipCodeUpdated.ZipCode
                            && stagingFormatted.CountryRegionId == addressZipCodeUpdated.CountryRegionId
                            && stagingFormatted.StateId == addressZipCodeUpdated.State
                            && stagingFormatted.CountyId == addressZipCodeUpdated.County
                            && stagingFormatted.CityId == addressZipCodeUpdated.City
                            && stagingFormatted.DistrictId == addressZipCodeUpdated.DistrictName
                            && stagingFormatted.StreetNumberMinimum == addressZipCodeUpdated.FromNum
                            && stagingFormatted.StreetNumberMaximum == addressZipCodeUpdated.ToNum
                            && stagingFormatted.StreetNumberValidity == addressZipCodeUpdated.EvenOdd
                            && stagingFormatted.Street == addressZipCodeUpdated.StreetName
                            && stagingFormatted.TimeZone == addressZipCodeUpdated.TimeZone
                            && stagingFormatted.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
                            && stagingFormatted.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
                            && stagingFormatted.TransferStatus == DMFTransferStatus::NotStarted
                            && addressZipCodeUpdated.CountryRegionId == countryRegionId
                            && stagingFormatted.Field2 == addressZipCodeUpdated.Field2
                && stagingFormatted.Field1 == addressZipCodeUpdated.Field1;

            if (addressZipCodeUpdated.RowCount() > 0)
            {
                // Only add records updated instead of -1 when no records returned.
                updatedCount += addressZipCodeUpdated.RowCount();
            }

            // Insert the staging records into the physical tables.
            addressZipCodeInserted.skipDataMethods(true);

            insert_recordset addressZipCodeInserted
                        (City, CityAlias, CountryRegionId, County, DistrictName, EvenOdd,
                        FromNum, ToNum, State, StreetName, TimeZone, ZipCode, Field2, Field1)
                    select CityId, CityAlias, CountryRegionId, CountyId, DistrictId, StreetNumberValidity, StreetNumberMinimum, StreetNumberMaximum,
                            StateId, Street, Timezone, ZipCode, Field2, Field1  from stagingFormatted
                        where stagingFormatted.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
                            && stagingFormatted.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
                            && stagingFormatted.TransferStatus == DMFTransferStatus::NotStarted
                            && stagingFormatted.CountryRegionId == countryRegionId
                    notexists join addressZipCodeExisting
                        where addressZipCodeExisting.ZipCode == stagingFormatted.ZipCode
                            && addressZipCodeExisting.CountryRegionId == stagingFormatted.CountryRegionId
                            && addressZipCodeExisting.State == stagingFormatted.StateId
                            && addressZipCodeExisting.County == stagingFormatted.CountyId
                            && addressZipCodeExisting.City == stagingFormatted.CityId
                            && addressZipCodeExisting.DistrictName == stagingFormatted.DistrictId
                            && addressZipCodeExisting.FromNum == stagingFormatted.StreetNumberMinimum
                            && addressZipCodeExisting.ToNum == stagingFormatted.StreetNumberMaximum
                            && addressZipCodeExisting.EvenOdd == stagingFormatted.StreetNumberValidity
                            && addressZipCodeExisting.StreetName == stagingFormatted.Street
                            && addressZipCodeExisting.TimeZone == stagingFormatted.TimeZone
                            && addressZipCodeExisting.CountryRegionId == countryRegionId
                            && addressZipCodeExisting.Field2 == stagingFormatted.Field2
                && addressZipCodeExisting.Field1 == stagingFormatted.Field1;

            if (addressZipCodeInserted.RowCount() > 0)
            {
                // Only add records inserted instead of -1 when no records returned.
                insertedCount += addressZipCodeInserted.RowCount();
            }

            // Update city recid
            LogisticsAddressCity addressCity;
                
            update_recordset addressZipCodeUpdated
                    setting CityRecId = addressCity.RecId
                    join addressCity
                        where addressZipCodeUpdated.City == addressCity.Name
                            && addressZipCodeUpdated.County == addressCity.CountyId
                            && addressZipCodeUpdated.State == addressCity.StateId
                            && addressZipCodeUpdated.CountryRegionId == addressCity.CountryRegionId
                            && addressZipCodeUpdated.CountryRegionId == countryRegionId
                    join staging
                        where staging.ZipCode == addressZipCodeUpdated.ZipCode
                            && staging.CountryRegionId == addressZipCodeUpdated.CountryRegionId
                            && staging.StateId == addressZipCodeUpdated.State
                            && staging.CountyId == addressZipCodeUpdated.County
                            && staging.CityId == addressZipCodeUpdated.City
                            && staging.DistrictId == addressZipCodeUpdated.DistrictName
                            && staging.StreetNumberMinimum == addressZipCodeUpdated.FromNum
                            && staging.StreetNumberMaximum == addressZipCodeUpdated.ToNum
                            && staging.StreetNumberValidity == addressZipCodeUpdated.EvenOdd
                            && staging.Street == addressZipCodeUpdated.StreetName
                            && staging.TimeZone == addressZipCodeUpdated.TimeZone
                            && staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
                            && staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
                            && staging.TransferStatus == DMFTransferStatus::NotStarted
                            && staging.CountryRegionId == countryRegionId
                            && staging.Field2 == addressZipCodeUpdated.Field2
                            && staging.Field1 == addressZipCodeUpdated.Field1;

            // Update district recid
            // district is unique in name, city, country/region, state, county
            LogisticsAddressDistrict addressDistrict;

            update_recordset addressZipCodeUpdated
                    setting District = addressDistrict.RecId
                    join addressDistrict
                        where addressZipCodeUpdated.DistrictName == addressDistrict.Name
                            && addressZipCodeUpdated.CityRecId == addressDistrict.City
                            && addressZipCodeUpdated.State == addressDistrict.StateId_RU
                            && addressZipCodeUpdated.County == addressDistrict.CountyId_RU
                            && addressZipCodeUpdated.CountryRegionId == addressDistrict.CountryRegionId_RU
                            && addressZipCodeUpdated.CountryRegionId == countryRegionId
                    join staging
                        where staging.ZipCode == addressZipCodeUpdated.ZipCode
                            && staging.CountryRegionId == addressZipCodeUpdated.CountryRegionId
                            && staging.StateId == addressZipCodeUpdated.State
                            && staging.CountyId == addressZipCodeUpdated.County
                            && staging.CityId == addressZipCodeUpdated.City
                            && staging.DistrictId == addressZipCodeUpdated.DistrictName
                            && staging.StreetNumberMinimum == addressZipCodeUpdated.FromNum
                            && staging.StreetNumberMaximum == addressZipCodeUpdated.ToNum
                            && staging.StreetNumberValidity == addressZipCodeUpdated.EvenOdd
                            && staging.Street == addressZipCodeUpdated.StreetName
                            && staging.TimeZone == addressZipCodeUpdated.TimeZone
                            && staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
                            && staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
                            && staging.TransferStatus == DMFTransferStatus::NotStarted
                            && staging.CountryRegionId == countryRegionId
                            && staging.Field2 == addressZipCodeUpdated.Field2
                            && staging.Field1 == addressZipCodeUpdated.Field1;


            // If the country region use zip plus 4 format, then update zipcode with the formatted zip code value in LogisticsAddressPostalCodeV3StagingFormattedView.
            if (LogisticsAddressZipCode::useZipPlus4(countryRegionId))
            {
                update_recordset addressZipCodeUpdated
                        setting ZipCode = stagingFormatted.ZipCodePlus4
                        join stagingFormatted
                            where stagingFormatted.ZipCode == addressZipCodeUpdated.ZipCode
                                && stagingFormatted.CountryRegionId == addressZipCodeUpdated.CountryRegionId
                                && stagingFormatted.StateId == addressZipCodeUpdated.State
                                && stagingFormatted.CountyId == addressZipCodeUpdated.County
                                && stagingFormatted.CityId == addressZipCodeUpdated.City
                                && stagingFormatted.DistrictId == addressZipCodeUpdated.DistrictName
                                && stagingFormatted.StreetNumberMinimum == addressZipCodeUpdated.FromNum
                                && stagingFormatted.StreetNumberMaximum == addressZipCodeUpdated.ToNum
                                && staging.StreetNumberValidity == addressZipCodeUpdated.EvenOdd
                                && staging.Street == addressZipCodeUpdated.StreetName
                                && staging.TimeZone == addressZipCodeUpdated.TimeZone
                                && stagingFormatted.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
                                && stagingFormatted.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
                                && stagingFormatted.TransferStatus == DMFTransferStatus::NotStarted
                                && stagingFormatted.CountryRegionId == countryRegionId
                                && staging.Field2 == addressZipCodeUpdated.Field2
                                && staging.Field1 == addressZipCodeUpdated.Field1;
            }

            if (LogisticsAddressCountryRegion::find(countryRegionId).isOcode == #isoNL)
            {
                update_recordset addressZipCodeUpdated
                        setting ZipCode = stagingFormatted.ZipCodeNL
                        join stagingFormatted
                            where stagingFormatted.ZipCode == addressZipCodeUpdated.ZipCode
                                && stagingFormatted.CountryRegionId == addressZipCodeUpdated.CountryRegionId
                                && stagingFormatted.StateId == addressZipCodeUpdated.State
                                && stagingFormatted.CountyId == addressZipCodeUpdated.County
                                && stagingFormatted.CityId == addressZipCodeUpdated.City
                                && stagingFormatted.DistrictId == addressZipCodeUpdated.DistrictName
                                && stagingFormatted.StreetNumberMinimum == addressZipCodeUpdated.FromNum
                                && stagingFormatted.StreetNumberMaximum == addressZipCodeUpdated.ToNum
                                && staging.StreetNumberValidity == addressZipCodeUpdated.EvenOdd
                                && staging.Street == addressZipCodeUpdated.StreetName
                                && staging.TimeZone == addressZipCodeUpdated.TimeZone
                                && stagingFormatted.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
                                && stagingFormatted.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
                                && stagingFormatted.TransferStatus == DMFTransferStatus::NotStarted
                                && stagingFormatted.CountryRegionId == countryRegionId
                                && staging.Field2 == addressZipCodeUpdated.Field2
                                && staging.Field1 == addressZipCodeUpdated.Field1;
            }

            // Update the staging table to specify records were successfully processed.
            update_recordset staging
                    setting TransferStatus = DMFTransferStatus::Completed
                    where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
                            && staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
                            && staging.TransferStatus == DMFTransferStatus::NotStarted
                            && staging.CountryRegionId == countryRegionId;
        }

        successful = true;

        ttscommit;

        return [insertedCount, updatedCount];
    }
    Please suggest what am I missing?
  • Anton Venter Profile Picture
    Anton Venter 20,346 Super User 2025 Season 2 on at
    It should work if you throw an exception.

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

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 451 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 428 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 239 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans