Views:

Scenario 

Some businesses have a requirement to create Quotes from previous Invoices. Doing this can be time consuming, especially if the invoice has many product items - copying recreating these can be a task in itself. 

With North52, you can generate a Quote from an invoice with the click of a button. Whats more, using our Decision tables, you can easily change parts as you see fit.

Note that below we delve into how to create this Decision Table from scratch for educational purposes. However if you wish to quickly set it up, you can copy and paste the Formula text file. The text file can be found at the bottom of this article with instructions. 

N52 Formula Manager Solution 

Create Formula 

  • Create a new Decision Table of type ClientSide Calculation
  • Set the Source Entity to Invoice
  • Right-click on the Decision Table and select Insert > Insert Decision Table 
  • Right-click on the Decision Table and select Insert > Insert Decision Table 
  • Rename the 3 sheets to Create Quote , Create Quote Details and Open Quote
    • You can rename the sheets by double clicking on the sheet label 

Create Quote Sheet

This sheet will create the Quote using the Invoice fields. As an Invoices Potential customer can be a Contact or an Account, we will need to two different rows - one for Contact, one for Account

  • Right-click and select Set Sheet Options > Use Create Record
  • Select columns C to N on the Decision Table
  • Right click cell B2 and select Insert > Insert Action
    • Note that if you have a build of North52 BPA prior to 562, the Actions may be inserted to the right and you will need to select Delete > Delete Column to remove the space 
  • Select cell A2
  • Go to the Source tab and expand Source
  • Find Customer Type and click on it
  • Click on cell A4
  • Type 'account', make sure to include the single quotes. 
  • Select cell B2, go to the Explore tab, expand Entities and find Quote
  • Expand Quote and select Bill to Street 1
  • Click on B4 
  • Go to the Source tab and click on Bill to Street 1
  • Click on B5
  • Go to Source tab and click on Bill to Street 1 
  • Repeat this process : matching the Quote fields to their corresponding Invoice fields as shown in the screenshot below. 
    • Note that Potential Customer is different; Type in cell N4: 'account|'+[invoice.customerid] and N5: 'contact|'+[invoice.customerid]

Create Quote : Initial view 

 

Create Quote : Scrolled to the right

Create Quote Details

This sheet will create the Quote Details from the Invoice Details. There are two types of Product: Existing or Write in - We will need a row for each. 

  • Select the Create Quote Details tab
  • Right-click and select Set Sheet Options > Use Create Record
  • Select Columns C to K on the Decision Table sheet
  • Right-click cell B2 and select Insert > Insert Action
    • Note that if you have an older version of North52, the Actions may be inserted to the right, you will need to select Delete > Delete Column to remove the space  
  • Select cell A2
    • Right click and select Insert > Insert Foreach Record
    • Clear Set name, leaving cell A2 selected and go to the Functions tab
    • Search for FindRecords and shift-click the function to open up its wizard
      • For Friendly Name type Find All Invoice Product 
      • For Entity Name select Invoice Product
      • For Where Attribute select Invoice ID
      • For Equals Value, select the Source tab, search for invoice and then select Invoice from the Source node
      • Leave Select attribute as * 
      • Leave the other parameters as defaults
      • Click Generate
  • Click on cell B2 and type Select product
    • This Condition column will distinguish between Existing and Write In Products
  • Right-click and select Operations > Toggle Advanced Mode
  • Select cell B3 and go to the Functions tab
    • Search for current and click the CurrentRecord function to add it to cell B3
    • Delete the placeholder text 'fieldname' including the quotes
    • Go to the Explore tab
    • Search for Invoice Product
    • Expand the Invoice Product node
    • Locate the Select Product (Value) node and ctrl-click to add 'isproductoverridden' inside the CurrentRecord function brackets
  • Right-click and select Operations > Toggle Advanced Mode
  • Select cell B4 and click on Write In from under Select Product (Value) on the Explore tab
  • Select cell B5 and click on Existing 
  • Select cell C2
  • Go to the Explore tab search for product (clear the existing search first), locate Quote Product and expand the node
  • For C2 click on Quote (Value) and for C4, go to Source > Decision Table Calculations and select 'Create Quote'
    • Repeat for cell C5
  • For D2 go back to the Explore tab and click on Write-in Product
    • Select D4, then from the Functions tab click on the CurrentRecord function
    • Delete the placeholder text 'fieldname' including the quotes
    • On the Explore tab locate the Invoice Product node and expand
    • Locate Write-In Product and ctrl-click to add 'productdescription', the function should look like CurrentRecord('productdescription')
  • For E2 expand the Quote Product node and click on Existing Product (Value)
    • Select E5, then from the Functions tab click on the CurrentRecord function
    • Delete the placeholder text 'fieldname' including the quotes
    • On the Explore tab locate the Invoice Product node and expand
    • Locate Existing Product (Value) and ctrl-click to add 'productid', the function should look like CurrentRecord('productid')
  • Note that D5 and E4 are empty as they are do not exist for the different types of Products. 
  • For F2, click on Unit (Value) from the Quote Product node and for F4 and F5, follow similar steps as above to create the function: CurrentRecord('uomid')
  • For G2, click on Price Overridden {Value} from the Quote Product node and for G4 and G5, create the function CurrentRecord('ispriceoverridden')
  • For H2, click on Price per Unit from the Quote Product node and for H4 and H5, create the function CurrentRecord('priceperunit')
  • For I2, click on Quantity from the Quote Product node and for I4 and I5, create the function CurrentRecord('quantity')
  • For J2, click on Extended Amount from the Quote Product node and for J4 and J5, create the function CurrentRecord('extendedamount')
  • For K2, click on Amount from the Quote Product node and for K4 and K5, create the function CurrentRecord('baseamount')
  • For L2, click on Currency (Value) from the Quote Product node and for L4 and L5, create the function CurrentRecord('transactioncurrencyid')
  • For M2, click on Description from the Quote Product node and for M4 and M5, create the function CurrentRecord('description')
  • For N2, click on Exchange Rate from the Quote Product node and for N4 and b, create the function CurrentRecord('exchangerate')

Create Quote Details : Initial View

Create Quote Details : Scrolled to the right

Create Quote Details : Scrolled more to the right

Open Quote

This sheet will open up the Quote once it has been created. 

  • Select cell A2 
  • Right-click and select Delete > Delete Columns as there are no conditions for this sheet
  • Click cell A4
  • Select the Functions tab and type OpenEntity in the Search functions input field and press enter
  • Shift-click the OpenEntityForm function to open its wizard
    • Ignore the Friendly Name field as we are going to insert the function directly into the cell
    • For Entity Name select Quote from the search list
    • For ID, select the Source tab and expand the Decision Table Calculation node, then click on Create Quote
    • Leave the Parameters field blank
    • Click Generate
  • Click Save

Quick Set-up

To set up this formula from a template file:

  • Create a new Decision Table of type Clientside Calculation
  • Set the Source Entity to Invoice
  • Select Commands > Toggle Editor
  • Paste into the editor the formula that is in the text file under Related Attachments below
  • Click Save and once completed, refresh the Formula page

North52 Quick Button

Add a Quick Button to your Invoice form to trigger the formula. See Quick Button set up article for details on how to do this.