Thursday, March 3, 2011

Filter the Activity Form contact to only existing records on a Business Relationship by creating a Lookup Dynamically- AX 2009

Today a user came to me and asked me if I would be able to show only the contacts related to a Business Relationship when choosing a contact in the Activity form.


The following is the form:



 The original setup would show all the contacts available table wise. This was a problem for the users as it will take a lot of time for them to find the right contact.

To achieve this requirement I had to create 3 new public static methods in the smmBusRelTable, and one method in the ContactPerson table (lookupContactByBusRel - see last in this post) to create a new query to be return to the ContactPersonId control in the Activity Form.


The first method CustExistInContact determines if a business relationship has been converted to a customer.


If the Business Relationship was indeed converted to a customer, then the findCustFromBusRel public static method will retrieve the customer account. Otherwise the public static findContactDirFromBusRel will be called to etrieve the PartyId of the Business Relationship.


I also had to overrride the Lookup method in the ContactPersonId DataSource field (path is below) to first decide if the Business Relatioship contains a  Customer Account or not, and depending on the result it calls either the findCustFromBusRel  or the findContactDirFromBusRel methods.


Forms > smmActivities > DataSource > smmActivities > Fields > ContactPersonId > Methods > Lookup




Lookup Code:


public void lookup(FormControl _formControl, str _filterStr)
{
    smmBusRelAccount    busRelAccount = AGOCallReports_smmBusRelAccount.text();
    CustAccount         _custAccount;
    DirPartyId          _partyId;
    Boolean             CustExistsInContact;
    ;

    if(smmBusRelTable::CustExistInContact(busRelAccount))
    {
        _custAccount = smmBusRelTable::findCustFromBusRel(busRelAccount);
        ContactPerson::lookupContactByBusRel(_formControl, _custAccount);
    }
    else
    {
        _partyId = smmBusRelTable::findContactDirFromBusRel(busRelAccount);
        ContactPerson::lookupContactByBusRel(_formControl, '', _partyId);
    }

}


The above method either calls the findCustFromBusRel or the findContactDirFromBusRel based on an existing customer account in the Business Relationship record.


CustExistInContact Code:


public static Boolean CustExistInContact(smmBusRelAccount _busRelAccount, boolean _forupdate = false)
{
    smmBusRelTable  smmBusRelTable;
    ContactPerson   contactPerson;
    Boolean         ret = false;
    DirPartyId      partyId;
    ;

    if (_busRelAccount)
    {
        partyId = smmBusRelTable::find(_busRelAccount).PartyId;
       
        select firstonly contactPerson where contactPerson.OrgPartyId == partyId;
        if(contactPerson.CustAccount)
            ret = true;
    }

    return ret;
}



If the Business Relationship record has a customer account, the the following method is called:


findCustFromBusRel Code:



public static CustAccount findCustFromBusRel(smmBusRelAccount _busRelAccount, boolean _forupdate = false)
{
    smmBusRelTable  smmBusRelTable;
    ContactPerson   contactPerson;
    ;

    if (_busRelAccount)
    {
        smmBusRelTable.selectForUpdate(_forupdate);

        select firstonly smmBusRelTable join contactPerson
            where smmBusRelTable.BusRelAccount == _busRelAccount &&
                  smmBusRelTable.PartyId == contactPerson.OrgPartyId;
    }

    return contactPerson.CustAccount;
}


If the Business Relationship record does not have a customer account, then the following method is called:

findContactDirFromBusRel Code:

public static DirPartyId findContactDirFromBusRel(smmBusRelAccount _busRelAccount, boolean _forupdate = false)
{
    smmBusRelTable  smmBusRelTable;
    ContactPerson   contactPerson;
    ;
    if (_busRelAccount)
    {
        smmBusRelTable.selectForUpdate(_forupdate);
        select firstonly smmBusRelTable
            where smmBusRelTable.BusRelAccount == _busRelAccount;
    }
    return smmBusRelTable.PartyId;
}

Finally, I had to create a new method in the ContactPerson form to return only the contacts existing on a particular Business Relation. This method creates a new query to be return as the result data to the calling control, and has to optional parameters CustAccount and DirPartyId, which are passed from the Lookup method in the smmActivities form.

lookupContactByBusRel Code:

public static void lookupContactByBusRel(FormControl _formControlCalling, CustAccount _custAccount = '', DirPartyId _partyId = '')
{
    Query                   query;
    SysTableLookup          sysTableLookup;
    QueryBuildRange         buildRange;
    QueryBuildDataSource    dataSource;
    Boolean                 IsCustomer = true;
    ;
   
    if(_custAccount == '')
        IsCustomer = false;
   
    query = new Query();
    dataSource = query.addDataSource(tablenum(ContactPerson));
   
    if(IsCustomer)
    {
        buildRange = dataSource.addRange(fieldnum(ContactPerson, CustAccount));
        buildRange.value(queryvalue(_custAccount));
    }
    else
    {
        buildRange = dataSource.addRange(fieldnum(ContactPerson, OrgPartyId));
        buildRange.value(queryvalue(_partyId));
    }
   
    sysTableLookup = SysTableLookup::newParameters(tablenum(ContactPerson), _formControlCalling, true);
    sysTableLookup.parmQuery(query);
    sysTableLookup.addLookupfield(fieldnum(ContactPerson, Name));
    sysTableLookup.addLookupfield(fieldnum(ContactPerson, Title));
   
    if(IsCustomer)
        sysTableLookup.addLookupfield(fieldnum(ContactPerson, CustAccount));
   
    sysTableLookup.addLookupfield(fieldnum(ContactPerson, ContactPersonId), true);
    sysTableLookup.performFormLookup();
   
}



The final result is shown below:

1 comment:

  1. Another approach is to override the actual control Lookup field and have this code:

    smmBusRelAccount busRelAccount = AGOCallReports_smmBusRelAccount.text();
    CustAccount _custAccount;
    DirPartyId _partyId;
    Boolean CustExistsInContact;
    ;

    ContactPerson::lookupDirContactPerson(AGOCallReports_smmBusRelAccount, busReltable.PartyId, this,
    smmActivities.ContactPersonID);

    The problem with this is that if the data has been migrated from an earlier AX version (3.0 or 4.0) to AX 2009, the DirPartyId record will not be present and the whole thing will break.

    In addition to the code above, you will need to override the JumRef method and set the control, in our case, the smmActivities_ContactPersonId to something like this:

    public void jumpRef()
    {
    AGOCallReports_ContactPersonID.jumpRef();
    }

    ReplyDelete

Thank you for your thoughts. Your comment will appear in my blog shortly after review.

Have a great day!