Monday 11 October 2021

cannot create a record in customers (custtable the record already exists)

Go to SQL as Admin, AXDB database:

pass your table(Custable) and run below query:

DECLARE @MaxRecID BIGINT
DECLARE @NextVal BIGINT
 
SELECT @MaxRecID = MAX(RECID)
FROM custtable
 
SELECT @NextVal = NEXTVAL
FROM SYSTEMSEQUENCES
INNER JOIN SQLDICTIONARY
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = 'custtable'
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID
 
IF (@NextVal > @MaxRecID)
BEGIN
PRINT 'custtable did not need to be updated.'
END
ELSE
BEGIN
PRINT 'Updated custtable from ' + CONVERT(VARCHAR(MAX), @NextVal) + '' to '' + CONVERT(VARCHAR(MAX), @MaxRecID + 1)
UPDATE SYSTEMSEQUENCES
SET NEXTVAL = @MaxRecID + 1
FROM SYSTEMSEQUENCES
INNER JOIN SQLDICTIONARY
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = 'custtable'
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID
END

Sunday 10 October 2021

Creating new D365FO Business Event for Customer & CRUD notifications using Power Automate.

Main contract class: 

// This is a BusinessEventContract class for Customer

/// </summary>
[DataContract]
class VKCustTableBusinessEventContract extends BusinessEventsContract
{
    SysAppCRUDOperation                action;
    CustAccount                        custAccount;
    DataAreaId                        dataAreaId;
 
    private void initialize(CustTable _custTable, SysAppCRUDOperation _action)
    {
        custAccount    = _custTable.AccountNum;
        dataAreaId    = _custTable.DataAreaId;
        action        = _action;
    }
 
    /// <summary>
    /// Create new VKCustTableBusinessEventContract from CustTable and action
    /// </summary>
    /// <param name = "_custTable">CustTable table</param>
    /// <param name = "_action">Action</param>
    /// <returns>a new VKCustTableBusinessEventContract</returns>
    public static VKCustTableBusinessEventContract newFromCustTable(CustTable _custTable, SysAppCRUDOperation _action)
    {
        VKCustTableBusinessEventContract    contract    = new VKCustTableBusinessEventContract();
        contract.initialize(_custTable, _action);
        return contract;
    }
 
    protected void new()
    {
    }
 
    [DataMember('CustAccount'), BusinessEventsDatamember("CustAccount")]
    public CustAccount parmCustAccount(CustAccount _custAccount = custAccount)
    {
        custAccount = _custAccount;
        return custAccount;
    }
 
    [DataMember('Action'), BusinessEventsDatamember("Action")]
    public SysAppCRUDOperation parmAction(SysAppCRUDOperation _action = action)
    {
        action = _action;
        return action;
    }
 
    [DataMember('DataAreaId'), BusinessEventsDatamember("DataAreaId")]
    public DataAreaId parmDataAreaId(DataAreaId _dataAreaId = dataAreaId)
    {
        dataAreaId = _dataAreaId;
        return dataAreaId;
    }
 
}

Business event class:

/// <summary>
/// This is a BusinessEvent class for Customer
/// </summary>
[BusinessEvents(classStr(VKCustTableBusinessEventContract),
    "Customers CU",
    "Event for create, update",
    ModuleAxapta::General)]
class VKCustTableBusinessEvent extends BusinessEventsBase
{
    private CustTable custTable;
    SysAppCRUDOperation action;
 
    /// <summary>
    /// Create new VKCustTableBusinessEvent from CustTable and action
    /// </summary>
    /// <param name = "_custTable">CustTable table</param>
    /// <param name = "_action">Action</param>
    /// <returns>a new VKCustTableBusinessEvent</returns>
    public static VKCustTableBusinessEvent newFromCustTable(CustTable _custTable, SysAppCRUDOperation _action)
    {
        VKCustTableBusinessEvent businessEvent = new VKCustTableBusinessEvent();
        businessEvent.parmAction(_action);
        businessEvent.parmCustTable(_custTable);
        return businessEvent;
    }
 
    private CustTable parmCustTable(CustTable _custTable = custTable)
    {
        custTable = _custTable;
        return custTable;
    }
 
    private SysAppCRUDOperation parmAction(SysAppCRUDOperation _action = action)
    {
        action = _action;
        return action;
    }
 
    protected void new()
    {
    }
 
    [Wrappable(true), Replaceable(true)]
    public BusinessEventsContract buildContract()
    {
        return VKCustTableBusinessEventContract::newFromCustTable(custTable, action);
    }
 
}

Extension to CustTable table to trigger insert/update events:

/// <summary>
/// Extension class for Custtable table
/// </summary>
[ExtensionOf(tableStr(CustTable))]
final class VKCustTable_Extension
{
    public void insert(DirPartyType _partyType, Name _name,boolean _updateCRM)
    {
        next insert(_partyType, _name, _updateCRM);
 
        if (BusinessEventsConfigurationReader::isBusinessEventEnabled(classStr(VKCustTableBusinessEvent)) && !CustTable::vkSkipByUserFilter())
        {
            VKCustTableBusinessEvent::newFromCustTable(this, SysAppCRUDOperation::Create).send();
        }
    }
 
    public void update(boolean _updateSmmBusRelTable, boolean _updateParty)
    {
        next update(_updateSmmBusRelTable, _updateParty);
 
        if (BusinessEventsConfigurationReader::isBusinessEventEnabled(classStr(VKCustTableBusinessEvent)) && !CustTable::vkSkipByUserFilter())
        {
            VKCustTableBusinessEvent::newFromCustTable(this, SysAppCRUDOperation::Update).send();
        }
    }
 
    /// <summary>
    /// Whether to skip sending business event or not
    /// </summary>
    /// <returns>true to skip sending BE</returns>
    public static boolean vkSkipByUserFilter()
    {
        boolean ret = false;
 
        // Use there real user id, which will be used for integration to avoid triggering update business event when external system updates the entity
        if ('Admin' == curUserId())
        {
            ret = true;
        }
 
        return ret;
    }
 
}

Configuration:

- Build
- System administration / Setup / Business events / Business events catalogue
- Manage / Rebuild business event catalogue
You should be able to find your Business Event in the list in specified category 

Power Automate:

- Create new
- Automated cloud flow
- Set Flow name and click Skip button
- In Search input type "Dynamics"
- Select Fin & Ops Apps
- Select Whan a Business Event occurs
- Select three dots in the right top corner and in My connecions section select "Add new connection"
- Select Connect with service principal
- Fill in Connection name, Client ID, Client Secret and Tenant (which is Licensed to in D365FO About)
- Select three dots in the right top cornerand choose corrrect connection
- Select right Instance
- Select General as Category
- Select Customer CU as Business event

- Add new step
- Type "Parse JSON" in search input
- Select "Parse JSON"
- Set body of previouse step as a Content
- Click Generate from sample button and pase there Schema from Business event (System administration / Setup / Business events / Business events catalogue / find the record / click Download schema button)

- Add additional steps with required actions


Troubleshooting:

There is a bug in Power Automate that it is not possible to select your business event if you place it in a category with existing standard business events, for example "Accounts Receivable". It shows duplicating record in the category dropdown and then there is no option to select your newly created business event:
Category dropdown:





POSTMAN D365

  Postman is useful to test the behavior of different OData class from/to D365FO. In this post we will see the steps to setup Postman with D...