Functions and Business Rules 
The gadget spec URL could not be found

OrangeScape provides a comprehensive set of built-in functions that can be used for performing simplest of data calculation to creating complex business rules. These functions can be directly typed in the public cells. Alternatively, the formula button "fx" can be used to list the functions and enter the functions.
Click on the "fx" button to view the list of available functions.
   
"Insert Function" dialog box with the list of functions grouped by category will be displayed.
   
Types of function:
  • Database
  • Text
  • Math and Trig
  • Informational
  • Logical
  • Lookup and Reference
  • Statistical
  • Financial
  • Data and Time
   
Type the formula and click on the "Insert Formula" button.
   

In addition to all the functions that are available in Excel, OrangeScape provides a unique set of database functions for working with database. Since only these functions are different from those of excel, the database functions in OrangeScape have been provided below for reference.


List of available database functions.
   
Hint/reference is provided for the function selected.
   
   


  Database Functions
  DGET: Returns value from the connected model
  • DGET(ReferenceCellName, ParentModelName.FieldName)

  DSUM: Returns sum of connected child values satisfying condition

  • DSUM(ChildModelName. ReferenceCellName, ChildModelName.FieldName) - without criteria

  • DSUM(ChildModelName. ReferenceCellName,ChildModelName.FieldName, ChildModelName.FieldName > 1000) - criteria with constant value

  • DSUM(ChildModelName. ReferenceCellName,ChildModelName.FieldName, ChildModelName.FieldName > ParentFieldName) - criteria with dynamic value

  DCOUNT: Counts the child records (Reference cell contains numbers in database) satisfying condition
  • DCOUNT(ChildModelName.ReferenceCellName,ChildModelName.FieldName) - without criteria
  • DCOUNT(ChildModelName.ReferenceCellName,ChildModelName.FieldName, ChildModelName.FieldName >1000) - criteria with constant value
  • DCOUNT(ChildModelName.ReferenceCellName, ChildModelName.FieldName,  ChildModelName.FieldName > ParentFieldName) - criteria with dynamic value
  DCOUNTA: Counts the child records (Reference cell contains string in database) satisfying condition
  • DCOUNTA(ChildModelName.ReferenceCellName, ChildModelName.FieldName) - without criteria
  • DCOUNTA(ChildModelName.ReferenceCellName, ChildModelName.FieldName,  ChildModelName.FieldName <>'AAA') - criteria with constant value
  • DCOUNTA(ChildModelName.ReferenceCellName, ChildModelName.FieldName,  ChildModelName.FieldName <> ParentFieldName) - criteria with dynamic value
  DCONCATENATE: Concatenate the specified string in child model satisfying condition
  • DCONCATENATE(ChildModelName.ReferenceCellName, ChildModelName.FieldName)  - without criteria
  • DCONCATENATE(ChildModelName.ReferenceCellName, ChildModelName.FieldName,  ChildModelName.FieldName <>'AAA') - criteria with constant value
  • DCONCATENATE(ChildModelName.ReferenceCellName, ChildModelName.FieldName,  ChildModelName.FieldName <> ParentFieldName) - criteria with dynamic value
  DMAX: Returns maximum value of the child satisfying condition
  • DMAX(ChildModelName.ReferenceCellName, ChildModelName.FieldName) - without criteria
  • DMAX(ChildModelName.ReferenceCellName, ChildModelName.FieldName,  ChildModelName.FieldName >1000) - criteria with constant value
  • DMAX(ChildModelName.ReferenceCellName, ChildModelName.FieldName,  ChildModelName.FieldName > ParentFieldName) - criteria with dynamic value
  DMIN: Returns minimum value of the child satisfying condition
  • DMIN(ChildModelName.ReferenceCellName, ChildModelName.FieldName) - without criteria
  • DMIN(ChildModelName.ReferenceCellName, ChildModelName.FieldName,  ChildModelName.FieldName >1000) - criteria with constant value
  • DMIN(ChildModelName.ReferenceCellName, ChildModelName.FieldName,  ChildModelName.FieldName > ParentFieldName) - criteria with dynamic value

  DSEARCH: Returns the SheetId of the instance satisfying the given criteria
  • DSEARCH(Criteria, Order By, Is descending boolean parameter)

  DFIND: Returns the SheetId of the instance satisfying the given criteria
  • DFIND(Criteria, Order By, Is descending boolean parameter, Sync boolean parameter, ReferenceCellName)


Building business rules: Calculate amount based on price and quantity

Now we will implement a simple business rule to calculate the amount using spreadsheet formulas on public parameters for product price and quantity.
The product price must be fetched from the product master.

The quantity will be entered by the user.

The amount must be calculated as the product of price and quantity. This rule can be implemented  in the rules perspective as shown in the screen capture.

Enter the following in the "Amount" public cell:

=Product_Price*Quantity

OR

=F2*G2

In the data model,

F2 refers to ProductPrice
G2 refers to Quantity
   

Building business rules: Automatically populate today's date in the order date parameter 

Now we will implement a simple business rule to store today's date using spreadsheet functions on public parameters for order date. The order date must be automatically populated with the date when an order is created.
Select the "OrderDate" public cell. Enter the following rule in the cell.

=Today()
   
The namebox shows the name of the public cell as "OrderDate". The formula bar shows the corresponding rule.

The date function "TODAY" has been used to store today's date.


Building business rules: Calculate the total order amount, number of items ordered, max item price and min item price
"CustomerOrders" contains the order summary details such as order data, order fulfillment date, order status, order id, total amount, the number of items ordered, max item price and min item price, while "ProductOrders" contains individual product details such as product description, colour, size, quantity, price and amount. The order amount for all the line items in an order must be aggregated to calculate the total order amount for that order. Similarly, the number of items, max item price and min item price must be recorded in the order summary. Follow the directions given below implement the above business rule using spreadsheet functions on public parameters.
Open the "CustomerOrders" data model. Select the "OrderAmount" public cell. Enter the following rule in the cell.

=DSUM(ProductOrders.OrderID,
ProductOrders.OrderAmount)
   
The namebox shows the name of the public cell as "OrderAmount". The formula bar shows the corresponding rule.

The database function "DSUM" has been used to aggregate the order amount for the products in an order.


Select the "TotalItemsOrdered" public cell. Enter the following rule in the cell.

=DCOUNTA(ProductOrders.OrderID,
ProductOrders.OrderID)
   
The namebox shows the name of the public cell as "TotalItemsOrdered". The formula bar shows the corresponding rule.

The database function "DCOUNTA" has been used to count the number of products in a customer order.



Select the "Min_Item_Price" public cell. Enter the following rule in the cell.

=DMIN(ProductOrders.OrderID,
ProductOrders.ProductPrice)
   
The namebox shows the name of the public cell as "Min_Item_Price". The formula bar shows the corresponding rule.

The database function "DMIN" has been used to find the minimum item price from the items ordered.



Select the "MAX_Item_Price" public cell. Enter the following rule in the cell.

=DMAX(ProductOrders.OrderID,
ProductOrders.ProductPrice)
   
The namebox shows the name of the public cell as "Max_Item_Price". The formula bar shows the corresponding rule.

The database function "DMAX" has been used to find the maximum item price from the items ordered.


Building business rules: Accessing data in other models

When placing an order, the price of the selected item must be automatically populated. The price details are available in the product data model. Hence, we will have to do a look-up on the product model to fetch the product price based on the product selected. The DGET database function can be used to fetch the data from any of the connected data models. Since a connection has been already established between the ProductOrders and Product data model through the ProductDescription public cell, we can use this connection to retrieve the product price.

Select the "ProductPrice" public cell. Enter the following rule in the cell.

=ProductDescription.ProductPrice
   
The namebox shows the name of the public cell as "ProductPrice". The formula bar shows the corresponding rule.



Select the "ProductPrice" public cell. Enter the following rule in the cell.

=DGET(ProductDescription,
Product.ProductPrice)
   
The namebox shows the name of the public cell as "ProductPrice". The formula bar shows the corresponding rule.

The database function "DGET" has been used to fetch product price from the connected product model.