BigChange provides the ability to customise Job Cards and Financial Document PDFs using templates.
These templates can be created using the online editor or Microsoft Word. While static text can be manually typed, specific job or financial details require Keywords.
Keywords, selected via the BigChange Add-in, are replaced by the system when the template is generated in the back office. For further customisation, Formula Keywords can be used.
This guide details the types of formulas available and how to utilise them.
This is an advanced guide requires an understanding of managing templates. If applicable, please ensure the add-in is downloaded.
Core Knowledge Requirements
Getting Started with Templates
Installing the BigChange Add-in
Selecting and Recognising the Formula Keyword
Locating the Formula Keyword within the Online Editor
Navigate to:
- [Top meu] Account Settings > Settings [Side menu] Account > Templates > Add > Insert Keyword.
Locating the Formula Keyword within the Word Document
- Select the 'Insert Keyword or Formula' button within the Word Add-in, then navigate to the sub-tab Miscellaneous and select 'Formula'
Types of Formula Keyword
There are six components of the Formula Keyword:
|
Condition Formula
What is a Condition Formula?
A condition formula is a tool that allows you to display different text or outcomes based on specific conditions you set. It works by comparing two values, known as comparison values, to determine whether the condition is true or false.
-
Comparison Values: These are the values you want to compare in your condition. For example:
- Checking if a number is greater than another number.
- Verifying if a text matches a specific word.
- Seeing if a date is before or after another date.
- Comparison Types: This defines how the two values are compared. Common comparison types include:
-
If the Condition is True:
- If the comparison between the values meets your specified condition, the formula displays the result you set for a true outcome.
-
If the Condition is False:
- If the comparison does not meet the condition, the formula displays the result you set for a false outcome.
How does the Condition Formula Work?
-
Choose the Formula Type
- Select "Condition" as your formula type from the list of available options.
-
Add a Condition
- Select "Add a condition" to begin setting up your condition.
-
Define the Condition
- Expression 1: Choose the first value you want to compare.
- Comparison Type: Select how you want to compare the values (e.g., equals, greater than, less than).
- Expression 2: Choose the second value to compare against the first.
- After setting these, click "OK" to confirm.
-
Set the Output for True or False
-
If Condition is True:
- In the "Expression if condition true" box, enter the text or keyword you want to display when the condition is met.
-
If Condition is False:
- In the "Expression if condition false" box, enter the text or keyword you want to display when the condition is not met.
-
If Condition is True:
- Select Save to confirm your Formula
Leave these boxes blank if you prefer no output for either result.
Operation Formula
What is an Operation Formula?
An Operation Formula allows you to perform calculations using two expressions.
These expressions are simply numbers or number-based keywords that serve as inputs for your math operations.
How does an Operation Formula Work?
-
Choose the Formula Type
- Select "Operation" as your formula type from the list of available options.
-
Choose an Operation Type
- Select the mathematical operation you wish to perform
- Addition
- Subtraction
- Multiplication
- Division
- Select the mathematical operation you wish to perform
-
Choose the Expression's the Operation will be performed on
- These expressions must be numbers or number-based keywords used as inputs for calculations.
- Enter these into box 'Expression 1' and 'Expression 2'
- Select Save to confirm your Formula
Rounding Formula
What is a Rounding Formula?
A rounding formula lets you enter a number or expression and choose how many decimal places you want it rounded to.
How does a Rounding Formula Work?
-
Choose the Formula Type
- Select "Rounding" as your formula type from the list of available options.
-
Choose an Expression
- This expression must be a number or number-based keyword used as inputs for rounding
- Enter this into the box 'Expression'
-
Choose the Number of Decimal Places
- Enter this value as a whole number (1 or higher)
- Select Save to confirm your Formula
Format Formula
What is a Format Formula?
A Format formula lets you customize how numbers, dates, and times are displayed.
How does a Format Formula Work?
-
Choose the Formula Type
- Select "Format" as your formula type from the list of available options.
-
Choose an Expression
- This expression must be a number or date, either typed in or provided through a keyword used as an input for formatting.
- Enter this into the box 'Expression'
-
Choose the Format Type
- Enter the formatting you wish to apply to your expression (Guidance on this below)
- Select Save to confirm your Formula
What Formatting Options are available?
This section explains the formatting options available. These will need to be entered in the 'Format' box.
Date Formatting (Only Works with Dates)
To use these date formatting symbols, simply combine them in any order to display the date in the format you prefer. Any spacing or symbols you use are maintained after the symbols in the below box are converted.
Example symbols: Slashes (/), Dashes (-), or Commas (,). For example:
- "dd/mm/yyyy" would show a date as 23/09/2024.
- "mmm d, yy" would show it as Sep 23, 24.
- "yyyy-mm-dd" would show it as 2024-09-23.
Symbol | How It Appears |
d | Shows the day as a number (1 to 31) |
dd | Shows the day as a number with a zero in front for single digits (01 to 31) |
w | Shows the day of the week as a number, where Sunday is 1 and Saturday is 7 |
ww | Shows the week number of the year, starting with the first week in January |
m | Shows the month as a number (1 to 12) |
mm | Shows the month as a number with a zero in front for single digits (01 to 12) |
mmm | Shows a shortened version of the month name (Jan, Feb, Mar, etc.) |
mmmm | Shows the full name of the month (January, February, etc.) |
y | Shows the number of the day in the year (1 to 366) |
yy | Shows the last two digits of the year (23 for 2023) |
yyyy | Shows the full year (for example, 2023) |
Time Formatting(Only Works with Dates)
To use these date formatting symbols, simply combine them in any order to display the date in the format you prefer. Any spacing or symbols you use are maintained after the symbols in the below box are converted.
Example symbols: Slashes (/), Dashes (-), or Commas (,).
For example:
- "hh:mm" would show a time as 06:04 (4 past 6 in the morning)
- "hh-mm" would show a time as 06-04 (4 past 6 in the morning)
- "hh , mm" would show a time as 06 , 04 (4 past 6 in the morning)
To Force 12-Hour Format use "A/P" to indicate you would like AM/PM
For example:
- "hh:mm" would would show as 16:05
- "hh:mm A/P" would show as 04:05 A/P
See the bellow table for which symbols can be used with a following A/P
Symbol | How It Appears |
h | Shows the hour of the day (1 to 12), and can be used with "A" or "P" to indicate morning or afternoon. |
hh | Shows the hour with a zero in front if it's a single-digit number (01 to 12), and can also be used with "A" and "P" |
n | Shows the minute as a number without a zero in front if it's less than 10 (0 to 59) |
nn | Shows the minute as a number with a zero in front for single digits (00 to 59) |
m | Shows the minute without a zero in front if it's less than 10, but only when used with hours (h or hh). To use hh you must use mm next to it. e.g. hh:mm. |
mm | Shows the minute with a zero in front for single digits, but only when used with hours |
s | Shows the second as a number without a zero in front if it's less than 10 (0 to 59) |
ss | Shows the second with a zero in front for single digits (00 to 59) |
Example of "hh:mm:ss a/p dd/mm/yyyy" within an Online Editor Formula: The Expression used is a Keyword Formula from a Worksheet 'Date' Question: Example Output within a JobCard: |
Number Formatting (Only Works with Numbers)
When formatting numbers, you can use the digit 0 as a placeholder to set how many digits you want to see. This allows you to control the appearance of numbers, including whether to show decimal places or add leading zeros to shorter numbers.
-
How It Works:
- 0 acts as a guide for each digit. If you want to show a number with two decimal places, you would enter 0.00. This ensures that even if the number doesn’t naturally have two decimal places, it will still display as such.
- If the number you are formatting has more decimal places than you specify, the number will be rounded to match the format you’ve entered.
-
Example 1
- Expression: 123
- Format Entered: 00.00
- Output: 123.00
-
Example 2
- Expression 123.458
- Format Entered 0.00
- Output: 123.46
Placeholder | How it Works |
00 | The output must have 2 digits minimum |
0.00 | The output must have 2 decimal places and be 3 digits total. |
Date Operation Formula
What is a Date Operation Formula?
A Date Operation Formula lets you perform arithmetic against time or dates
How does a Date Operation Formula Work?
-
Choose the Formula Type
- Select "Date Operation" from the list of available formula types.
-
Choose an Expression
- Enter the date expression in the format dd/mm/yyyy or mm:hh.
- Note: You may use dd-mm-yyyy, but it will be displayed as dd/mm/yyyy within the system.
- Example: For December 31, 2024, enter 31/12/2024.
- To enter the time 4pm. Enter 16:04.
- Input this date into the 'Expression' box.
- Enter the date expression in the format dd/mm/yyyy or mm:hh.
-
Choose the Operation Type
- Select the type of Date Operation from the 'Operation Type' dropdown menu.
- Refer to the list of available operations provided below.
-
Enter a Quantity of Time
-
- Specify a quantity of time in whole numbers (integers).
- The unit of time (minutes, hours, or days) will depend on the selected Operation Type
- Decimals will be ignored by the system
-
- Select Save to confirm your Formula
Available Operation Types for Date Operation Formula
The following Operation Types require a Quantity of Time for arithmetic:
|
The following Operation Types DO NOT require a Quantity of Time for arithmetic:
|
Expression Formula
What is an Expression Formula?
An Expression Formula let's you complete work with functions, perform comparisons, and utilise symbols to perform math.
Additionally, you can use HTML within Expression Formula, for more details on this please visit the 'Expression Formula - HTML' Support Article (This article is a WIP and the link will be added soon)
How does an Expression Formula Work?
-
Choose the Formula Type
- Select "Expression" as your formula type from the list of available options.
-
Choose an Expression
- This can include a combination of functions, comparisons, and Keywords. (More guidance on this below)
- Select Save to confirm your Formula
What Functions, Comparisons, and Symbols Can I Use?
Functions are simple tools that help you add, subtract, or change text. You can use symbols like + and -, or special commands like Len and Trim to work with numbers and words. Always remember to put words inside 'quotes'.
Using Keywords in Expressions
You can use keywords whenever you need them in an expression. To use a keyword as a number, click Keyword and choose one that gives you a number.
If you need a keyword to act like a word (also called a "string"), select Keyword and choose one that provides text. When using a keyword as a word, remember to put it inside quotation marks like this: '[[Keyword]]'.
What are Strings and Characters?
Strings are sequences of letters, numbers, or symbols that form words or phrases, like "Hello" or "123".
Chars, short for characters, are the individual letters, numbers, or symbols that make up a string. For example, in "Hello", each letter is a char.
Available Operators
This table shows the available Operators, how they can be used with examples of them within an IIF Statement.
Operators | Description | Example | Result |
< | Checks if the first value is less than the second value | IIf(5 < 10, 'True', 'False') | TRUE |
> | Checks if the first value is greater than the second value | IIf(15 > 10, 'True', 'False') | TRUE |
<= | Checks if the first value is less than or equal to the second value | IIf(10 <= 10, 'True', 'False') | TRUE |
>= | Checks if the first value is greater than or equal to the second value | IIf(20 >= 15, 'True', 'False') | TRUE |
<> | Checks if the first value is not equal to the second value | IIf(10 <> 15, 'True', 'False') | TRUE |
= | Checks if the first value is equal to the second value | IIf(10 = 10, 'True', 'False') | TRUE |
In | Checks if a value is part of a list or group | IIf(5 In (5, 10, 15), 'True', 'False') | TRUE |
Like | Checks if a value matches a pattern, like parts of a word or character | IIf('abc' Like 'a%', 'True', 'False') IIf('abc123' Like 'a%123', 'True', 'False') IIf('test' Like '_e_t', 'True', 'False') |
True True True |
Accepted Symbols
This table shows the available Symbols, how they can be used with examples of them performing math.
Function | Description | Example | Result |
Addition (+) | Add two numbers together | 5+5 | 10 |
Subtraction (-) | Subtract one number from another | 10-4 | 6 |
Multiplication (*) | Multiply two numbers together | 6*7 | 42 |
Division (/) | Divide one number by another | 20/4 | 5 |
Modulo (%) | Find the remainder when dividing one number by another | 10%3 | 1 |
What Functions Can I Use?
Function | Description | Example | Result |
IIf(condition, if true, if false) | Output a value based on a condition |
IIf(15 > 10, 'Greater', 'Smaller or Equal') |
Greater |
Len(string) | Output the amount of letters in a word | Len('Test') | 4 |
IsNull(expression,if true) | Return the first value if not null; otherwise, return the second value | IsNull('BigChange','if true') IsNull(Null,'if true') |
BigChange if true |
Trim(string) | Remove extra spaces from the beginning and end of a string | Trim(' Hello, World! ') | 'Hello, World!' |
SubString(string,start,end) | Extract part of a string starting from a specific position | SubString('HelloWorld',1,5) | Hello |
TimeToMinute | Convert a time to the total number of minutes | TimeToMinute(15:56) | 956 |
MinuteToTime | Convert total minutes into time in hours and minutes | MinuteToTime(956) | 15:56 |
Nested IIF Statements
Nested IIF statements in UK English work similarly to how they do in any other variant of English. They are used to evaluate multiple conditions in sequence, allowing you to decide between different outcomes based on those conditions.
For Example:
-
Basic IIF Statement:
- IIf(condition, if true, if false)
- This checks a single condition. If the condition is true, it returns the "if true" value. If the condition is false, it returns the "if false" value.
-
Nested IIF Statement:
- IIf(condition, if true, IIf(condition, if true, if false))
- This checks the first condition. If it is true, it returns the first "if true" value.
- If the first condition is false, it evaluates a second IIF statement.
- The second IIF checks another condition. If true, it returns the second "if true" value. If false, it returns the second "if false" value.
Example:
- IIf(number > 10, "Large", IIf(number > 5, "Medium", "Small"))
- If number > 10, it returns "Large".
- If not, it checks number > 5. If true, it returns "Medium".
- If both conditions are false, it returns "Small".
Conclusion
This guide provides an overview of customising Job Cards and Financial Documents using BigChange Formula Keywords. By understanding and applying these formulas, you can tailor your documents to meet specific needs. For further assistance, refer to the provided links or contact support.
Related to
Comments
0 comments
Please sign in to leave a comment.