Adding a formula to a widget

If you want to display more specific information on your widgets, you can create your own column by defining a formula for it.

You can often write the formula you need by entering simple mathematical equations. However, to enter more complicated formulas, you must either understand how to use Microsoft SQL commands or request the services of an Ajera consultant (to learn more about Microsoft SQL, search the Microsoft website).

Video: Table widgets: formulas and formatting (4:38)

Before you begin

To add a formula to a widget

  1. Click > to open Design mode.
  2. On a widget you want to add a formula to, click to open the widget menu and click Edit.
  3. Click
  4. Click

The Formula Builder opens.

Types of formulas that can be created

  • Basic formulas: You can create formulas using basic mathematical operators. For example,

[Total Contract Amount] - ([Billed] + [WIP])

  • Advanced formulas: When you write an advanced formula, you create a new column by defining relationships between columns that already exist on the widget. To create advanced formulas, you do not need to know the structure of the Ajera database. To write advanced formulas, you use the Microsoft SQL functions. Some commonly used functions in advanced formulas include:
    • LEFT
    • RIGHT
    • SUBSTRING
    • DATEDIFF
    • DATEADD
    • MONTH
    • UPPERCASE
    • Here is an example of a formula for the Transaction base.
    Case [Activity Type]WHEN 'Labor'THEN [Employee]
     WHEN 'Expense'THEN [Activity]
     WHEN 'Consultant'THEN [Vendor]
     WHEN 'None'THEN 'Invoice Adjustment'
    END  
    • This formula uses the SQL function, CASE, to populate the new column as follows:

    If the activity type is

    Then the formula returns

    Labor

    the employee

    Expense

    the activity

    Consultant

    the vendor

    None

    the text "Invoice Adjustment"

  • Advanced custom formulas: You may choose to write an advanced custom formula when you need to create an entirely new column that is not based on a relationship between existing columns. To write advanced custom formulas, you must know the structure of the Ajera database and use Microsoft SQL functions. For up-to-date database schema information, refer to the AxSchema spreadsheet in your Ajera\Program folder. Some commonly used functions in advanced custom formulas include:
    • SELECT
    • FROM
    • WHERE
    • A good use of an advanced custom formula is when you want to display static columns from different widgets or tables within the database. For example, you want a column for the project manager’s phone number on the project widget. You create a custom formula on the project widget referencing the phone number field from the Employee database table:
    • SELECT vecPhone1 FROM AxVec WHERE vecKey = [Project Manager Key]

    Caution:

    • If Ajera’s database structure or logic changes, advanced custom formulas may cease to work correctly. These formulas may produce an error or return incorrect results without alerting you of any problems. For example, if you create an advanced custom formula that calculates an activity type total using the current activity types, and then a future version of Ajera’s database includes new activity types, that formula then produces an inaccurate amount because it is calculated without the new activity types.
    • Be aware that multiple custom formulas on one base may cause performance issues.
  • Special Tags:
    • HUB_KEY_TAG = "{HubKey}";
    • LINK_KEY_TAG = "{LinkKey}";
    • START_DATE_TAG = "{StartDate}";
    • END_DATE_TAG = "{EndDate}";
    • CURRENT_USER_TAG = "{CurrentUser}";
    • FROM_PARENT_TAG = "{FromParent}";
    KeyDefinition
    HubKey

    The current Page’s primary key.

    Example: If you are creating a new widget on the Project page, the HubKey would be the Project key.

    LinkKeyThe ID for the tab that this widget is running on.
    StartDate

    The starting date calculated off of an assigned date range.

    Note: Unless you assign a date range to this column this will be set to the Ajera default system minimum date.
    EndDate

    The ending date calculated off of an assigned date range.

    Note:

    Unless you assign a date range to this column this will be set to the Ajera default system maximum date.

    CurrentUser

    The employee key for the end user using the widget.

    FromParentThe parent key that a parent widget has provided. If this is not a child widget than this has no meaningful value.
  • Formula as a percent: You may choose to have formulas show as a percent.
  1. In the Formula Builder window, check the Percent check box.
  2. Enter a numerator.
  3. Enter a denominator.
  4. Change the multiplier if needed. It defaults to 100.
  • Formulas referencing other formulas: You may choose to have a formula reference an already existing formula:

Example:

§ Formula A = [Billed] * 1.5

§ Formula B = [Formula A] / [Hours worked]