About creating formulas
When you create a new inquiry
column, you can either add it from the predefined list of columns,
or 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). If needed, you can also click the Properties
button on the Inquiry toolbar and set conditions on the formula.
When you create a formula, you can use other existing formulas in the inquiry. These appear in the Available Columns area of the Formula Editor. The Available Columns area also contains the Current User Key, which allows you to display inquiry data pertaining only to the user.
You can create the following formulas in Inquiry:
 Basic formulas: You can create many inquiry formulas using basic mathematical operators. For example, here are formulas for two new columns on the Project inquiry:
[Billed]  [Cost] ([Billed] + [WIP])  [Cost]
 Advanced formulas: When you write an advanced formula, you create a new column by defining
relationships between columns that already exist in the inquiry. 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  All inquiry.
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:
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 uptodate 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 inquiries or tables within the database.
For example, you want a column for the project manager’s phone number
on the Project inquiry. You create a custom formula on the Project inquiry
referencing the phone number field from the Employee database table:
 SELECT vecPhone1 FROM AxVec WHERE vecKey = [Project Manager Key]
Note:   If the formula uses the SQL Min/Max function, you can apply a date
range to it. The function must contain these dates: 01011900 and 12312078.
For example, on the Project inquiry, add the following formula column:
SELECT sum(tcostamount) FROM axtransaction LEFT OUTER JOIN axproject on tproject = prjkey WHERE prjproject = [Project Key] AND tdate BETWEEN '01011900' and '1231'2078' You must close the Properties window and reopen it for the Date Range field to appear.  If the
formula contains an alias name, the alias name cannot contain brackets.

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 inquiry may cause performance issues.


