Views:

Scenario Overview

The customer's requirement in this article was to have a clickable link that would show the oldest unpaid Invoice on that Account.  This article can also be acheived using the SmartFlow function - see this knowledge base article


   

N52 Formula Manager Solution

The formula manager solution works like this:
  • The formula will use Fetch-XML to find all the unpaid Invoices on that account in order of oldest due date
  • We select the first record found and create a hyperlink for it
  • If no records found we just return a message saying No Unpaid Invoices

Formula

  • Create a new formula, setting the following values in the Formula Guide
    • Source Entity set to Account
    • Set Formula Type to ClientSide - Calculation
    • Select the Decision Table editor
  • Change the Name of the formula to Account - Show Link to Oldest Unpaid Invoice
  • Click Save

Create the Formula Detail record

  • Select the Fetch-Xml tab, and click the + button to create a new Formula Detail Record
  • Set the name to FindOldestInvoice
  • Click on the Advanced Find button:
    • Set the Look For to Invoices
    • Set query criteria:
      • Status Equals Acitve
      • Customer Equals [select any Account]
    • Click on Edit Columns
      • Configure the columns so that only Total Amount and Due Date are the only columns shown (by selecting only the columns you needs makes the query more efficient).
      • Configure the sorting so the Due Date is Ascending (oldest due Invoice will be first in the list)
      • Click OK
    • Click on the Download Fetch XML button
  • Open the downloaded Fetch-Xml file in Notepad (not a browser like Internet Explorer)
  • Copy all the Fetch-Xml text
  • Paste the Fetch-Xml into the Fetch Xml Query section
  • Replace the value="{GUID HERE}" with value="@accountid@" - this means that when the Fetch-Xml query is executed it will replace @accountid@ with the GUID of the Account which is calling the formula
    • For tidyness you can remove the uiname and uitype parameters as they are not needed as below:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="invoice">
    <attribute name="totalamount" />
    <attribute name="invoiceid" />
    <attribute name="duedate" />
    <order attribute="duedate" descending="false" />
    <filter type="and">
      <condition attribute="customerid" operator="eq" value="@accountid@" />
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>
  • Click Save & Close to return to the formula 

Configure the Decision Table

  • Right-click in column A, select Insert > Insert Inline Calculation
  • Replace the Set Name text with All Invoices and press enter to move to cell A4
  • Select the Functions tab, type findrecords in the search field and press enter
    • Shift-click the FindRecordsFD function to open the function wizard
    • Leave the Friendly Name field blank as we are going to insert the function directly into the cell 
    • Click the Search Icon for the Formula Detail (Query) field and select the FindOldestInvoice option
    • Leave the other function parameters as their defaults
    • Click Generate
  • Right-click in column B, select Insert > Insert TableCalculation
  • Replace the Set Name text with Oldest Invoice and press enter to move to cell B4
  • Select the Functions tab, type findrecords in the search field and press enter
    • Shift-click the FindRecordsValue function to open the function wizard
    • Leave the Friendly Name field blank as we are going to insert the function directly into the cell 
    • Click inside the Entity Collection field and then select the Source tab
      • Expand the Decision Table Calculations node
      • Click on All Invoices
    • Click inside the Field Name field and then select the Fetch-Xml tab (we are going to specify that the ID of the Oldest Invoice is returned from our query)
      • Expand the Query Details node
      • Expand the FindOldestInvoice node
      • Click on invoiceid
    • Set the Default Value field to ? (representing empty/null)
    • For Row Number either leave blank or set to 0 (the default)
    • Click Generate
  • Right-click in column A, select Insert > Insert Table Calculation
  • Replace the Set Name text with All Invoices and press enter to move to cell A4
  • Select the Functions tab, type findrecords in the search field and press enter
    • Shift-click the FindRecordsFD function to open the function wizard
    • Leave the Friendly Name field blank as we are going to insert the function directly into the cell for this example
    • Click the Search Icon for the Formula Detail (Query) field and select the FindOldestInvoice option
    • Leave the other function parameters as their defaults
    • Click Generate
  • Click on cell C2
  • Select the Source tab, and from the Decision Table Calculations node select Oldest Invoice
  • Click on cell C4
  • Select the Functions tab, type contains in the search field and press enter
  • Ctrl-click the ContainsData function to add it to cell C4
  • Click on cell D4
  • Select the Functions tab, type hyperlink in the search field and press enter
    • Shift-click the LinkHyperLinkUrl function to open the function wizard
    • Leave the Friendly Name field blank as we are going to insert the function directly into the cell for this example. 
    • Enter the Base URL of your CRM organization e.g. https://n52training.crm11.dynamics.com (without a trailing /)
    • Enter invoice into the Entity Name field
    • Click inside the Record ID field, select the Source tab and from the Decision Table Calculations node select Oldest Invoice
    • Enter Oldest Unpaid Invoice in the Display Text field
    • Click Generate
  • Click on cell D5
  • Enter 'No Unpaid Invoices' (this will be the default text if not invoices are found in the query)
  • Click Save

Adding the Button to the Account Form


 
  • Open the Form editor
  • Select Insert -> Web Resource.
  • Choose the north52_quick_button_hyperlink.htm web resource
  • Add the Custom Parameters
    • The shortcode of the formula you want to use
    • (Optional) How many pixels from the left you want to move the text
  • On the Formatting tab
    • Set Number of Rows to 1
    • Scrolling to Never
    • Uncheck Display Border
If you have never used Quick Button before you can see more detailed documentation at this link

Did you know?

Over 300 customers use North52!

Join over 300 organizations worldwide who trust North52 to drive their digital transformation into the future:

Serious Power without Code:  Easily build business rules, tests and data packages using our point-and-click editors. Even the most complex business rules are easily configured.

Build Fast:  We’ve built the North52 tools with our user’s productivity in mind – up to 10x faster than writing custom code.

Awesome Support:  We take pride in providing world class support to our customers and partners, ensuring you are successful with North52.

Future Proof:  We ensure your business rules and tests that you configure today, will continue to work with the ever changing Dynamics 365 platform.

Learn more about the North52 Decision Suite