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