Pages

Monday, May 11, 2015

How the phone numbers relate to customers/vendors and how to search/enumerate them [AX 2012]

The customer/vendor phone number relation is not terribly complex, but there don't seem to be very many clear-cut examples of sample code on how the joins work.  So I typed up a few sample scenarios.

How to find all of a customer/vendor phone numbers:


    // Find all of the customer's phone numbers
    while select dirPartyLocation
        where dirPartyLocation.Party            == dirPartyTable.RecId      &&
              dirPartyLocation.IsPostalAddress  == NoYes::No
    join logisticsElectronicAddress
        order by logisticsElectronicAddress.IsPrimary desc    
        where logisticsElectronicAddress.Location == dirPartyLocation.Location
    {
        info(strFmt("All Numbers\t%1 [Primary == %2]", logisticsElectronicAddress.Locator, logisticsElectronicAddress.IsPrimary));
    }

How to find all of a customer/vendor phone numbers with the "Business" role type:

    // Find all of a customer's phone numbers with the purpose of "Business"
    while select dirPartyLocation
        where dirPartyLocation.Party                            == dirPartyTable.RecId      &&
              dirPartyLocation.IsPostalAddress                  == NoYes::No
    join logisticsElectronicAddress
        order by logisticsElectronicAddress.IsPrimary desc
        where logisticsElectronicAddress.Location               == dirPartyLocation.Location
    join logisticsElectronicAddressRole
        where logisticsElectronicAddressRole.ElectronicAddress  == logisticsElectronicAddress.RecId
    join locationRole
        where logisticsElectronicAddressRole.LocationRole       == locationRole.RecId       &&
              locationRole.Type                                 == LogisticsLocationRoleType::Business
    {
        info(strFmt("All Business Numbers Only\t%1 [Primary == %2, Role == %3]", logisticsElectronicAddress.Locator, logisticsElectronicAddress.IsPrimary, locationRole.Name));
    }

How to find all of a customer/vendor phone numbers and all marked roles:

    // Find all of a customer's phone numbers and all of the marked purposes for each number
    while select dirPartyLocation
        where dirPartyLocation.Party                    == dirPartyTable.RecId      &&
              dirPartyLocation.IsPostalAddress          == NoYes::No
    join logisticsElectronicAddress
        order by logisticsElectronicAddress.IsPrimary desc
        where logisticsElectronicAddress.Location                == dirPartyLocation.Location
    {
        purposes = conNull();
        
        while select logisticsElectronicAddressRole
            where logisticsElectronicAddressRole.ElectronicAddress   == logisticsElectronicAddress.RecId
        join locationRole
            where logisticsElectronicAddressRole.LocationRole        == locationRole.RecId
     
        {       
            purposes += locationRole.Name;
        }
        
        info(strFmt("All Numbers and all roles\t%1 [Primary == %2, Roles == %3]", logisticsElectronicAddress.Locator, logisticsElectronicAddress.IsPrimary, con2Str(purposes)));
    }

And if you are only looking for a primary contact, that information/link is also directly stored on DirPartyTable for the primaries only and you can query it this way:

    // Alternative method for finding the primary locator contact
    // Can see used here \Classes\DirParty\primaryElectronicAddress
    select firstonly logisticsElectronicAddress
        exists join dirPartyTable
            where dirPartyTable.PrimaryContactPhone == logisticsElectronicAddress.RecId
            && dirPartyTable.RecId == custTable.Party;
    
    info(strFmt("Alternative method\t%1 [Primary == %2]", logisticsElectronicAddress.Locator, logisticsElectronicAddress.IsPrimary));

Here are all of the snippets in one large job you can copy & paste and run (after changing to your own customer)

static void FindPhoneExample(Args _args)
{
    CustTable                           custTable = CustTable::find('100013'); // TODO - Change to one of your customers
    DirPartyTable                       dirPartyTable = DirPartyTable::findRec(custTable.Party);
    DirPartyLocation                    dirPartyLocation;
    LogisticsElectronicAddress          logisticsElectronicAddress;
    LogisticsElectronicAddressRole      logisticsElectronicAddressRole;
    LogisticsLocationRole               locationRole;
    container                           purposes;

    
    info(strFmt("%1", custTable.phone()));
    setPrefix("Finding Phone Numbers");
    
    // Find all of the customer's phone numbers
    while select dirPartyLocation
        where dirPartyLocation.Party            == dirPartyTable.RecId      &&
              dirPartyLocation.IsPostalAddress  == NoYes::No
    join logisticsElectronicAddress
        order by logisticsElectronicAddress.IsPrimary desc    
        where logisticsElectronicAddress.Location == dirPartyLocation.Location
    {
        info(strFmt("All Numbers\t%1 [Primary == %2]", logisticsElectronicAddress.Locator, logisticsElectronicAddress.IsPrimary));
    }
    
    // Find all of a customer's phone numbers with the purpose of "Business"
    while select dirPartyLocation
        where dirPartyLocation.Party                            == dirPartyTable.RecId      &&
              dirPartyLocation.IsPostalAddress                  == NoYes::No
    join logisticsElectronicAddress
        order by logisticsElectronicAddress.IsPrimary desc
        where logisticsElectronicAddress.Location               == dirPartyLocation.Location
    join logisticsElectronicAddressRole
        where logisticsElectronicAddressRole.ElectronicAddress  == logisticsElectronicAddress.RecId
    join locationRole
        where logisticsElectronicAddressRole.LocationRole       == locationRole.RecId       &&
              locationRole.Type                                 == LogisticsLocationRoleType::Business
    {
        info(strFmt("All Business Numbers Only\t%1 [Primary == %2, Role == %3]", logisticsElectronicAddress.Locator, logisticsElectronicAddress.IsPrimary, locationRole.Name));
    }
    
    // Find all of a customer's phone numbers and all of the marked purposes for each number
    while select dirPartyLocation
        where dirPartyLocation.Party                    == dirPartyTable.RecId      &&
              dirPartyLocation.IsPostalAddress          == NoYes::No
    join logisticsElectronicAddress
        order by logisticsElectronicAddress.IsPrimary desc
        where logisticsElectronicAddress.Location                == dirPartyLocation.Location
    {
        purposes = conNull();
        
        while select logisticsElectronicAddressRole
            where logisticsElectronicAddressRole.ElectronicAddress   == logisticsElectronicAddress.RecId
        join locationRole
            where logisticsElectronicAddressRole.LocationRole        == locationRole.RecId
     
        {       
            purposes += locationRole.Name;
        }
        
        info(strFmt("All Numbers and all roles\t%1 [Primary == %2, Roles == %3]", logisticsElectronicAddress.Locator, logisticsElectronicAddress.IsPrimary, con2Str(purposes)));
    }
    
    // Alternative method for finding the primary locator contact
    // Can see used here \Classes\DirParty\primaryElectronicAddress
    select firstonly logisticsElectronicAddress
        exists join dirPartyTable
            where dirPartyTable.PrimaryContactPhone == logisticsElectronicAddress.RecId
            && dirPartyTable.RecId == custTable.Party;
    
    info(strFmt("Alternative method\t%1 [Primary == %2]", logisticsElectronicAddress.Locator, logisticsElectronicAddress.IsPrimary));
    
    info("Done");
}

No comments:

Post a Comment