Special variables in the template editor

In addition to the default variables (about default variables, check the help guide Creating and updating a Template), you can insert more variables in the template editor resulting from special calculations or hidden fields in KanbanBOX.

To set a special variable, first of all get in Print > New template, or get in Print > Template list, search the template to modify and click on Edit template and finally click on Edit Front to get in the editor page. For further details, check again the help guide Creating and updating a Template.

Now, let’s check all the special variables in details.

Partner color as the background of a cell

You can use partner color set for the supplier or customer as background color of the currently selected cell. First of all, you need to check or set partner color, available in the menu Master data > Partners > Partners list, under column Color.

Procedure:

1. Select the chosen cell;
2. Click on Background color field;
3. Type the following code {supplier_color} to set supplier color as the background color of the cell or type {customer_color} to set customer color as the background color of the cell;
4. Click on Save under Save cell.

Partner color as font color of a text

Likewise the background of a cell, you can use a partner color set for the supplier or customer as font color of the selected text. First of all, you need to check or set partner color, available on the menu Master data > Partners > Partners list, under the column Color.

Procedure:

1. Select the chosen cell;
2. Click on Font color field;
3. Type the following code {supplier_color} to set supplier color as font color of the text in the cell or type {customer_color} to set customer color as font color of the text in the cell;
4. Click on Save under Save cell.

Image

To insert an image in the kanban card, previously you need to insert it in the File manager.

Procedure:

1. Get in Print > File manager;
2. Click on the top right corner of the list Upload a new file;
3. In the new page, click on Select file;
4. Browse the directory to select the image to upload. Once selected, upload it by double-clicking the file or the Open button;
5. Once the file is uploaded, KanbanBOX displays the file name. To save it, click on Save image.

Once the image is loaded, you can set the image in the kanban card by the Edit Front page.

Procedure:

1. Select the cell where you want to include the image.
2. Click on the Cell content field.
3. Type the code {image(<file_name>,<max_width>,<max_height>)}, whereby:
   • <file_name> is the file name of the image (including the extension .png or .jpg), displayed in Print > File manager;
   • <max_width> (optional field) is the maximum width of the image (in millimeters);
   • <max_height> (optional field) is the maximum height of the image (in millimeters).
4. Click on Save under Save cell.

Total required capacity

The total required capacity is the result of processing time per unit (set in the kanban loop) multiplied by the quantity of the kanban card.

Procedure:

1. Select the cell where you want to include the result;
2. Select the variable Total required capacity from the dropdown menu under Insert a variable in the cell;
3. Click on Insert to include the formula into the cell;
4. Click on Save under Save cell.

Quantity (unformatted number)

Quantity (unformatted number) means that a particular data is not formatted as a number, thus keeping its original format.

For example, add 1000 as kanban quantity for a particular kanban loop. If you insert the variable Quantity, you will print in the kanban card the number 1.000 (the thousands separators set as default for the license), whereas if you insert the variable Quantity (unformatted number), you print the number 1000.

Procedure:

1. Select the cell where you want to include the data;
2. Select the variable Quantity (unformatted number) from the dropdown menu Insert a variable in the cell;
3. Click on Insert to include the code into the cell;
4. Click on Save under Save cell field.

Kanban total weight

The Kanban total weight is calculated as the sum of the product between the part weight (set in the parts master data) and the kanban quantity plus the bin weight (set in the bins master data).

Procedure:

1. Select the cell where you want to include the data;
2. Select the variable Kanban total weight from the dropdown menu Insert a variable in the cell;
3. Click on Insert to include the code into the cell;
4. Click on Save under Save cell field.

Number of multiples

As number of multiples we refer the number of times that the Multiple batch size is included in the Kanban quantity. So the result is the quotient of the division of Quantity by Multiple batch size.

Procedure:

1. Select the cell where you want to include the data;
2. Select the variable Number of multiples from the dropdown menu Insert a variable in the cell;
3. Click on Insert to include the code into the cell;
4. Click on Save under Save cell field.

Format a number - format_number()

You can format a number with the preferred number of decimals and separators for decimals and thousands.

Procedure:

1. Select the cell where you want to insert the number.
2. Click on the Cell content field.
3. Type the code {format_number(<number>,<decimals>,<decimals_separator>,<thousands_separator>)}, whereby:
   • <number> is the number to format. Important: the input number must be unformatted!. For example, for card quantity, use Quantity (unformatted number):
   • <decimals> is the number of decimals;
   • <decimals_separator> the character to be used as decimals separator. In the case of a comma, put curly brackets around it {comma};
   • <thousands_separator> the character to be used as the thousands separator. In the case of a comma, put curly brackets around it {comma};
4. Click on Save under Save cell field.

Extra Kanban – if_extra_kanban()

If the printed card is an extra card, you can add a specific text in the kanban card to describe that:

• This card is an extra card;
• Another attribute of the card, set by the user according to the needs.

The text is going to be printed in the card only if the card is an extra card.

Procedure:

1. Select the cell where you want to include the data.
2. Click on the Cell content field.
3. Type the code {if_extra_kanban(<content>)}, whereby:
   • <content> is the text printed in the card if it’s an extra kanban. For example, write EXTRA or Extra Kanban.
4. Click on Save under Save cell field.

Kanban – if_not_extra_kanban()

If the card is not an extra card, you can add a specific text in the kanban card to describe that:

• This card is a simple kanban card;
• Another attribute of the card, set by the user according to the needs.

The text is going to be printed in the kanban card only if the card is a simple kanban card.

Procedure:

1. Select the cell where you want to include the data.
2. Click on the Cell content field.
3. Type the code {if_not_extra_kanban(<content>)}, whereby:
   • <content> is the text printed in the card if it’s not an extra kanban. For example, write Kanban.
4. Click on Save under Save cell field.

Required date

You can add in the kanban card the required date in which the supplier must replenish the kanban supply, as displayed in the kanban board.

Procedure:

1. Select the cell where you want to include the data.
2. Click on the Cell content field.
3. Type the code {required_date(<format>)}, whereby:
   • <format> set date and time format to visualize. You can add these variables:
     • d: day
     • m: month
     • Y: year
     • H: hour
     • i: minute
     • s: second.

For example, a possible format is the following m/d/Y H:i:s, that displays this result 10/09/2017 13:16:13.

This field is optional. The default data format is set in the menu Settings > Company settings, if nothing is included inside the brackets.

1. Click on Save under Save cell field.

Offset before the required date

It is possible to add an offset to the required date to print to the kanban card an earlier date than the required date. This possibility is used especially when the kanban card requires two processing steps: this feature lets you anticipate the required date for the first processing step in order to have time for the second step.

Procedure:

1. Select the cell where you want to include the date.
2. Click on the Cell content field.
3. Type the code {offset_before_required_date(<offset_in_working_days>[,<format>,<calendar>])}, where:
   • <offset_in_working_days> is the number of working days to be removed from the required date. For example, setting the offset to 3, if the required date is 19 nov 2020, in the kanban card the printed date will be 16 nov 2020
   • <format> is an optional field for date formatting. To set it, please refer to section Required date
   • <calendar> is an optional field to choose with which working days calendar the system calculates the earlier required date. By default it is the loop owner’s calendar. Otherwise, you can write:
     • owner to calculate the date from the loop owner’s calendar.
     • supplier to calculate the date from the loop supplier’s calendar.
     • customer to calculate the date from the loop customer’s calendar.
4. Click on Save under Save cell field.

Print date

It is possible to add the print date and time in the corresponding kanban card. The print date and time will be the instant when the user opens the printing queue.

Procedure:

1. Select the cell where you want to include the date.
2. Click on the Cell content field.
3. Type the code {print_date(<format>)}, whereby <format> is an optional field. To set it, please refer to section Required date.
4. Click on Save under Save cell field.

Barcode – Code 128 font as a text

It is possible to print out the text entered in a cell as a Barcode code 128. The result is a textual barcode.

Procedure:

1. Select the cell where you want to include the barcode.
2. Write in the Cell content field the text or variables to be converted into a barcode.
3. Click on Make into a barcode, or insert the code {barcode_code128(<content>)}, whereby <content> is the text or variables to be converted.
4. Other special characters that could be inserted in the <content>:
   • {tab} to insert a tab character.
   • {lf} to create a new line, it corresponds to the character \n (line feed).
   • {ret} to return to the beginning of the line, it corresponds to the character \r (carriage return).
5. Click on Save under Save cell field.

Barcode – Code 128 font as an image

It is possible to print out the text entered in a cell as a Barcode code 128. The result is a barcode in graphic format.

With the graphic format you have:

• a good resolution of the barcode after printing;
• the possibility to size its width and height.

Procedure:

1. Select the cell where you want to include the barcode.
2. Click on the Cell content field.
3. Type the code {code128(<content>[,<max_width>,<max_height>])} where:
   • <content> is the text or variables to be converted.
   • <max_width> is the maximum width of the barcode (optional). Usually, it matches cell width.
   • <max_height> is the maximum height of the barcode (optional). Usually, it matches cell height.
4. Click on Save under Save cell field.

Barcode – Code 39 font as an image

In addition to Code 128, it’s also possible to print out the text entered in a cell as a Barcode Code 39. The result is a barcode in graphic format.

Procedure:

1. Select the cell where you want to include the barcode.
2. Click on the Cell content field.
3. Type the code {code39 (<content>[,<max_width>,<max_height>])}, whereby:
   • <content> is the text or variables to be converted.
   • <max_width> is the maximum width of the barcode (optional). Usually it matches cell width.
   • <max_height> is the maximum height of the barcode (optional). Usually it matches cell height.
4. Click on Save under Save cell field.

Barcode – Format EAN-13

You can add to the card the barcode of an EAN-13 number in the standard format.

Procedure:

1. Select the cell where you want to include the barcode.
2. Click on the Cell content field.
3. Type the code {ean13(<content>[,<scale>])} where:
   • <content> the numerical EAN code of 12 or 13 digits to be converted into a barcode (if absent, the thirteenth digit, or check digit, is automatically added);
   • <scale> the scaling size (optional). By default, the value is 100, which indicates the nominal size. The EAN standard allows using factors between 75 and 200. KanbanBOX accepts any number bigger than zero.
4. Click on Save under Save cell.

Data Matrix

It is possible to print out any text or variable entered in a cell as a Data Matrix, which is a two-dimensional matrix barcode.

Procedure:

1. Select the cell where you want to include the data matrix.
2. Click on the Cell content field.
3. Type the code {datamatrix(<content>[,<max_width>,<max_height>])}, whereby:
   • <content> is the text or variables to be converted.
   • <max_width> is the maximum width of the data matrix (in millimeters).
   • <max_height> is the maximum height of the data matrix (in millimeters).
   • <max_width> and <max_height> are optional field.
4. Other special characters that is possible to insert in the <content>:
   • {tab} to insert a tab character.
   • {lf} to create a new line, it corresponds to the character \n (line feed).
   • {ret} to return to the beginning of the line, it corresponds to the character \r (carriage return).
5. Click on Save under Save cell field.

QR code

It is possible to print out any text or variable entered in a cell as a QR code, which is a two-dimensional matrix barcode. I

Procedure:

1. Select the cell where you want to include the QR code.
2. Click on the Cell content field.
3. Type the code {qrcode(<content>[,<max_width>,<max_height>])}, whereby:
   • <content> is the text or variables to be converted;
   • <max_width> is the maximum width of the QR code (in millimeters);
   • <max_height> is the maximum height of the QR code (in millimeters).
   • <max_width> and <max_height> are optional field.
4. Other special characters that is possible to insert in the <content>:
   • {tab} to insert a tab character.
   • {lf} to create a new line, it corresponds to the character \n (line feed).
   • {ret} to return to the beginning of the line, it corresponds to the character \r (carriage return).
5. Click on Save under Save cell field.

Carriage return

It is possible to insert in a cell a special character which represents the carriage return. For example, it is useful when you need to insert two information in the same cell.

Procedure:

1. Select the cell where you want to insert the carriage return.
2. Click on the Cell content field.
3. Click where you want to insert the carriage return (for example, between two variables, or between a variable and a text).
4. Insert the special character {br} to add the carriage return.

Multiplication between values

It is possible to multiply different values and include the product in the kanban card.

Procedure:

1. Select the cell where you want to include the product;
2. Type the code {product(<value_1>,<value_2>[,<value_3>...])}, with <value_1>, <value_2> etc as variables or numbers to multiply and to display the product;
3. Click on Save under Save cell field.

Fill on the left - fill_left()

It is possible to format Cell content filling some numbers or letters on the left of the text. It could useful when you need to format a barcode as input for specific software.

Procedure:

1. Select the cell where you want to include the formatted text.
2. Click on the Cell content field.
3. Type the code {fill_left(<content>,<filling>,<total_length>)}, whereby:
   • <content> is the text or variables to be formatted;
   • <filling> is the text or numbers to fill on the left;
   • <total_length> maximum final total length of the string with the filling. If the string is longer than the total length set, the formula gives back several digits equal to starting from the right of the string;
4. Click on Save under Save cell field.

Examples:

• {fill_left(1234,0,6)} gives back as result 001234;
• {fill_left(1234,0,3)} gives back as result 234.

Extract a portion of a string - substring()

It is possible to extract a specific number of characters from a text string or a variable starting from a specific position.

Procedure:

1. Select the cell where you want to include the formatted text.
2. Click on the Cell content field.
3. Type the code {substring(<content>,<offset>,<lenght>)}, whereby:
   • <content> is the variable or the text to transform;
   • <offset> is the position of the first character that has to be extract from the text.
     • If <offset> is non-negative, the returned string will start at the offset'th position of the string, counting from zero. For instance, in the string 'abcdef', the character at position 0 is 'a', the character at position 2 is 'c', and so forth.
     • If <offset> is negative, the returned string will start at the offset'th character from the end of string. For instance, in the string 'abcdef', the character at position -1 is 'f', the character at position -3 is 'd', and so forth.
   • <lenght> is the number of characters that will be given back. If empty, all the characters positioned after the inserted "offset" are given back.
4. Click on Save under Save cell field.

Examples:

• {substring(abcdef,2,3)} gives back as result cde.
• {substring(abcdef,-3,2)} gives back as result de.
• {substring(abcdef,1)} gives back as result bcdef.

Text converted in columns and rows

If you insert a field separated by commas and semicolons, you can convert this text and split it into different rows.

Procedure:

1. Select the cell where you want to include the converted text.
2. Click on the Cell content field.
3. Type the code {comma_explode_row(<text_with_commas>,<element_index>,<row_index>)}, whereby:
   • <text_with_commas> is the text or variables to be converted;
   • <element_index> index of the text separated by semicolons;
   • <row_index> index of the text separated by commas;
4. Click on Save under Save cell field.

For example, with the text A,B,C;D,E,F:

• Text included in the kanban card is A if you insert in the cell content field {comma_explode_row(text,1,1)};
• Text included in the kanban card is E if you insert in the cell content field {comma_explode_row(text,2,2)}.

Text converted in columns

If you insert a field separated by commas (not semicolons), you can convert and split it into different cells.

Procedure:

1. Select the cell where you want to include the converted text.
2. Click on the Cell content field.
3. Type the code {comma_explode(<text_with_commas>,<element_index>)}, whereby:
   • <text_with_commas> is the text or variables to transform;
   • <element_index> index of the text separated by commas;
4. Click on Save under Save cell field.

For example, with the text A,B,C:

• Text included in the kanban card is A if you insert in the cell {comma_explode(text_with_commas,1)};
• Instead, text included in the kanban card is C if you insert in the cell {comma_explode(text_with_commas,3)}.

Text converted in columns (with delimiter)

If you insert a field separated by a delimiter, you can convert and split it into different cells.

Procedure:

1. Select the cell where you want to include the converted text.
2. Click on the Cell content field.
3. Type the code {explode_on(<text>,<delimiter>,<element_index>)}, whereby:
   • <text> is the text or variables to transform;
   • <delimiter> is the delimiter character
   • <element_index> index of the text separated by commas;
4. Click on Save under Save cell field.

For example, with the text A|B|C:

• Text included in the kanban card is A if you insert in the cell {explode_on(text,|,1)};
• Instead, text include in the kanban card is B if you insert in {explode_on(text,|,2)}.

Print content if two values are equal or not - if_equal()

It is possible to insert conditional text in the Cell content, checking if two values are equal. In case the function parameters contain commas, the parameters can be escaped by enlcosing them with curly brackets {...}.

Procedure:

1. Select the cell where you want to include the conditional text.
2. Click on the Cell content field.
3. Type the code {if_equal(<first_term>,<second_term>,<text_if_true>,<text_if_false>)}, where:
   • <first_term> is the left term in the comparison;
   • <second_term> is the right term in the comparison;
   • <text_if_true> is the text that will be output if the comparison result is true;
   • <text_if_false> is the text that will be output if the comparison result is false;
4. Click on Save under Save cell field.

Example:

• {if_equal({type_of_kanban},purchase,{supplier_number},NO)} prints the supplier number if type of kanban is purchase, prints NO otherwise

Print content if a values is numeric or not - if_numeric()

It is possible to insert conditional text in the Cell content, checking if a value is numeric. In case the function parameters contain commas, the parameters can be escaped by being enclosed by curly brackets {...}.

Procedure:

1. Select the cell where you want to include the conditional text.
2. Click on the Cell content field.
3. Type the code {if_numeric(<term>,<text_if_true>,<text_if_false>)}, where:
   • <term> is the term to be checked to be numeric or not;
   • <text_if_true> is the text that will be output if the term is numeric;
   • <text_if_false> is the text that will be output if the term is not numeric;
4. Click on Save under Save cell field.

Example:

• {if_numeric({part_custom_field_1},{part_custom_field_1},---)} prints the part custom field if it is numeric, otherwise prints ---

Print different content if the environment is production or test - if_production_env()

It is possible to print different content in the Cell content, checking if printing is done from the production environment or from the test environment. In case the function parameters contain commas, the parameters can be escaped by being enclosed by curly brackets {...}.

Procedure:

1. Select the cell where you want to include the conditional text.
2. Click on the Cell content field.
3. Type the code {if_production_env(<production_environment_text>,<test_environment_text>)}, where:
   • <production_environment_text> is the text that will be output if printing is done from the production environment;
   • <test_environment_text> is the text that will be output if printing is done from the test environment. If empty, the output is blank.
4. Click on Save under Save cell field.

Examples:

• {if_production_env(ABCD,1234)} prints ABCD if printing is done from the production environment, 1234 if printing is done from the test environment.
• {if_production_env(ABCD)} prints ABCD if printing is done from the production environment, leaves the cell empty if printing is done from the test environment.
• {if_production_env(,DO NOT USE)} leaves the cell empty if printing is done from the production environment, prints DO NOT USE if printing is done from the test environment.

Insert parenthesis, braces and commas as text inside special variables

The formulas that allow the insertion of special variables contain parenthesis/braces to determine their start or end and commas to separate parameters.

For this reason, if you need to use parenthesis, braces, or commas inside the text of a formula, these have to be substituted by the following variables:

Variable	Text shown
{opening_parenthesis}	(
{closing_parenthesis}	)
{opening_braces}	{
{closing_braces}	}
{comma}	,

Subtraction between two values ​​within a cell - subtract()

It is possible to insert a subtraction between two numbers within a cell. They can be two loop variables, a variable and a constant value, or two constants.

Procedure:

1. Select the cell where you want to include the subtraction.
2. Click on the Cell content field.
3. Insert the code {subtract({number1},{number2})} with:
   • number1 subtraction's minuend. it can be a loop field (such as kanban_quantity) or a constant value.
   • number2 subtraction's subtrahend. it can be a loop field (such as kanban_quantity) or a constant value.
4. Click on Save under Save cell field.

Example:

• {subtract({kanban_quantity},1)} prints the kanban card quantity - 1.