Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

Import Validation

(0) ShareShare
ReportReport
Posted on by 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 

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

    Thanks for your time . its working as expected. :-)

  • Verified answer
    GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Import Validation

    Try below code. Also please check the Datas given in the excel present in the LogisitcsAddressCity table.

    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.CountryRegionId == range.get_Item(rowNo,2).Value
                && addressCity.Name == range.get_Item(rowNo,3).Value
            
            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));
            }
        }

    Thanks,

    Girish S.

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

    My excel format below like:-

    Without validation excel format works only issue accept all name in the country and city.

    Grp id           | Country | City

    International | SAU      | Madina

    International | IND    | mumbai

    International | PAK    | mumbai  // inserted mumbai pakistan against in this cases this only only tell not exist PAK against city remaining two line should be insert.

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Import Validation

    What about the county then?

    If you want to insert based on the country and city, where will you get country? Is it coming from excel?

    Thanks,

    Girish S

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

    I removed 15 line and i checked table level there is available IND against city there.

    Even if exists country and city why error thrown. only if not exist and any other name(random name) will give that the cases only error i want otherwise insert based on the country and city.

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Import Validation

    Its little confusing for me.

    Line number 15 seems to be wrong. Where will you get the country region id?

    You are trying to assign the Country region id of destinationGroupLine buffer which is wrong.

    Remove that line and check.

    Also please open the table browser (LogisticsAddressCity) and check whether the given combination exists in the table like City and county.

    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.

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 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.

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 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.

  • 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.

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…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,868 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans