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


   

North52 Decision Suite Solution

The North52 Decision Suite 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