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 / Finance | Project Operations, Human Resources, ... / Import Validation
Finance | Project Operations, Human Resources, ...
Answered

Import Validation

(0) ShareShare
ReportReport
Posted on by Lakshmi 13

Hi all,

DestinationGroupLine  destinationGroupLine;

select firstonly destinationGroupLine
	where destinationGroupLine.DestinationGrpId ==  destinationGroup.DestinationGrpId
	&&  destinationGroupLine.Country ==  range.get_Item(rowNo,2).Value
	&& destinationGroupLine.City ==  range.get_Item(rowNo,3).Value;

if(!destinationGroupLine.RecId)
{
	//LogisticsAddressCity logisticsAddressCity;
	//LogisticsAddressCounty logisticsAddressCounty;

	//select firstonly logisticsAddressCity
	//    where logisticsAddressCity.Name ==  range.get_Item(rowNo,3).Value;

	//select firstonly logisticsAddressCounty
	//    where logisticsAddressCounty.Name ==  range.get_Item(rowNo,2).Value;

	destinationGroupLine.clear();
	destinationGroupLine.DestinationGrpId = destinationGroup.DestinationGrpId;
	destinationGroupLine.Country = range.get_Item(rowNo,2).Value;
	destinationGroupLine.City = range.get_Item(rowNo,3).Value;
	if(destinationGroupLine.validateWrite())
	{
		destinationGroupLine.doInsert();
	}
}

As i mentioned above code works fine for me. now, if i add country IND and city (Madina) like example.

I want to warning IND not available "madina".

1.Only if already exist country against the city only insert otherwise error thrown. 

2. same country only if exists country will insert otherwise error thrown.

Two validation i want to do while importing excel so that what to be done ?

Thanks 

I have the same question (0)
  • GirishS Profile Picture
    GirishS 27,825 Moderator on at
    RE: Import Validation

    Hi Lakshmi,

    You can use "LogisticsPostalAddress" table to validate city against country and vice versa.

    LogisticsPostalAddress will have this information against city, district, state, county and country.

    Thanks,

    Girish S.

  • Lakshmi Profile Picture
    Lakshmi 13 on at
    RE: Import Validation

    Thanks for your replay Girish,

    Can u give me a example for this validation ?

  • Verified answer
    GirishS Profile Picture
    GirishS 27,825 Moderator on at
    RE: Import Validation

    You can try like something below.

    LogisticsPostalAddress postalAddress;
//validation of city against country
select firstOnly * from postalAddress
    where postalAddress.Country == range.get_Item(rowNo,2).Value
    && postalAddress.City == range.get_Item(rowNo,2).Value;
if(postalAddress)
{
   // insert records
}
else
{
    //throw warning.
}

    Thanks,

    Girish S.

  • Lakshmi Profile Picture
    Lakshmi 13 on at
    RE: Import Validation

    Thanks for prompt response :)

  • Lakshmi Profile Picture
    Lakshmi 13 on at
    RE: Import Validation

              if(!destinationGroupLine.RecId)
            {
                LogisticsPostalAddress postalAddress;
                
                select firstOnly * from postalAddress
                where postalAddress.County == range.get_Item(rowNo,2).Value
                && postalAddress.City == range.get_Item(rowNo,3).Value;

                if(postalAddress)
                {
                    destinationGroupLine.clear();
                    destinationGroupLine.DestinationGrpId = destinationGroup.DestinationGrpId;
                    destinationGroupLine.Country = range.get_Item(rowNo,2).Value;
                    destinationGroupLine.City = range.get_Item(rowNo,3).Value;
                    if(destinationGroupLine.validateWrite())
                    {
                        destinationGroupLine.doInsert();
                    }
                }
                else
                {
                    warning(strFmt("Country and city does't exist %1",desGrpId));
                }
        }

    Hi Girish, i have debugged above code if(postalAddress) not come else part warning thrown even if country and city available.

    What could be the reason ? 

  • GirishS Profile Picture
    GirishS 27,825 Moderator on at
    RE: Import Validation

    On line number 6 it should be CountryRegionId right not county.

    Thanks,

    Girish S.

  • Lakshmi Profile Picture
    Lakshmi 13 on at
    RE: Import Validation

    select firstOnly * from postalAddress
    where postalAddress.CountryRegionId == destinationGroupLine.Country  //Country region id EDT 
    && postalAddress.County == range.get_Item(rowNo,2).Value
    && postalAddress.City == range.get_Item(rowNo,3).Value;

    You mean like : if so, its also not working else part thrown again.

  • GirishS Profile Picture
    GirishS 27,825 Moderator on at
    RE: Import Validation

    No, where you will get the CountryRegionId.

    You need to validate Country with City and Country with County right?

    Just open the LogisticsPostalAddress in table browser and see your values are there against City, County and CountryRegionId.

    Thanks,

    Girish S.

  • GirishS Profile Picture
    GirishS 27,825 Moderator on at
    RE: Import Validation

    If you want to validate Country with City and County use LogisticsAddressCity table.

    LogisitcsAddressCity addressCity;
// I am assuming the country region id as "IND"  and validate city and county against CountryRegionId             
select firstOnly * from addressCity
    where addressCity.County == range.get_Item(rowNo,2).Value
    && addressCity.Name == range.get_Item(rowNo,3).Value
    && addressCity.CountryRegionId == "IND";
if(addressCity)
{
    //insert code
}
else
{
    throw error
}

    Thanks,

    Girish S.

  • Lakshmi Profile Picture
    Lakshmi 13 on at
    RE: Import Validation

    DestinationGroupLine  destinationGroupLine;

    select firstonly destinationGroupLine
        where destinationGroupLine.DestinationGrpId ==  destinationGroup.DestinationGrpId //header grpid
        &&  destinationGroupLine.Country ==  range.get_Item(rowNo,2).Value
        && destinationGroupLine.City ==  range.get_Item(rowNo,3).Value;
   
    if(!destinationGroupLine.RecId)
    {
        LogisticsAddressCity addressCity;
        
        select firstOnly * from addressCity
        where addressCity.CountyId == range.get_Item(rowNo,2).Value
        && addressCity.Name == range.get_Item(rowNo,3).Value
        && addressCity.CountryRegionId == destinationGroupLine.Country;

        if(addressCity)
        {
            destinationGroupLine.clear();
            destinationGroupLine.DestinationGrpId = destinationGroup.DestinationGrpId;
            destinationGroupLine.Country = range.get_Item(rowNo,2).Value;
            destinationGroupLine.City = range.get_Item(rowNo,3).Value;
            if(destinationGroupLine.validateWrite())
            {
                destinationGroupLine.doInsert();
            }
        }
        else
        {
            warning(strFmt("Country and city does't exist %1",desGrpId));
        }
    }

    Same thing not working. same else part error thrown.

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…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

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

#1
André Arnaud de Calavon Profile Picture

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

#2
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 743

#3
Sumit Singh Profile Picture

Sumit Singh 551

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans