The String Operator function is a powerful tool that can be used to manipulate values from dynamic elements. They can be used in both standard Reports and Financial Reports.
There are different types of String Operators available:
- String Mapping
- Character Range
- Line Range
- String Compare
Note that String Operators can only be used with data, i.e. dynamic elements prefaced with “D:” in the Elements/Options column.
To use, open the element Edit window and click the green plus icon next to “String Operators” to create a new one and select a type. Once selected, parameters for that type can be set. The new String Operator will appear in the list for that element and can be edited by clicking on it.
String Operators can be copied and pasted to either the same element or into another element where the same or similar operator is needed.
Access Copy/Paste using the menu icon to the right of the “String Operators” label.
String Operators can also be switched on or off by using the checkbox next to each or completely deleted by clicking and dragging into the appearing trash bin.
Note that the combination and order of String Operators is important and impacts the resulting value.
String Mapping: This operator type can be used to replace certain words, phrases or characters with any other words, phrases or characters that you choose.
Example - In a report, you want to change the way dates are displayed. Instead of seeing 1-12-2012 > 04-10-2012, you want to see the word “to” instead of the arrow (“>”) character. Add a new String Operator and select String Mapping. Next, for “Find:” enter “>” and for “Replace with:” enter “to'. Confirm “Condition: Replace If Match” then click “OK” and “Apply.”
Character Range: Use to show only a certain range of characters from an element, specifying each by number. Character numbering starts at 0.
Example - In a report, you have an element called “Weekday”. The value shows “Tuesday,” and you want to show only the first 3 letters (“Tue”). Add String Operator type “Character Range.” Then for “From Index:” enter “0” and for “To Index:” enter “2.” Click “OK” and “Apply.”
Line Range: Similar to Character Range, use to show only specified lines from an element. Line numbering starts at 0. Example - To show only the first line of Address element, select operator type 'Line Range' and enter From Index: “0” and To Index: “0.”
Split: An element’s value can be split at a specified character, displaying either what comes before or after it. By copying an element and then splitting, you can show different characters/values from the same element in different places in your report.
Example - In a report, you want to display the Date separated into day, month, and year.
- In the element Edit window, click “Copy” button 3 times to create 3 new elements with the same name. Rename each copied element (to easily identify each, e.g. Day, Month, Year).
- Next, click the plus icon to add String Operator, type Split. In the “Split Character” field enter “–” (the date separator character). The entry for “Use Element with Index” varies depending on which part of the date is being used: for Day enter “0,” for Month enter “1,” for Year enter “2” and click “OK,” then “Apply.” Each value can be moved independently in the report.
Note that this Split could be used to display the date in a different order, e.g. Month, Day, Year.
String Compare: This is the most flexible of all the String Operators as it allows for a great variety of comparisons, each of which can be based on the current element’s value/input, another element’s value, or a value typed by the User.
Example - Services for which you are not charging your client, you want to show as free of charge (F.O.C.) in a Project Report.
- In the “Element Row” section, click Edit button for “Sell” element
- add a new String Operator type String Compare, then right-click in pop up window and select “New”
- in Comparison window, select/enter the following:
- Compare what: “(Input)”
- How: “Equals”
- With: “Value…” then “0.00”
- Return: “Value…” then “F.O.C.”
- Click “OK”, then select:
- Else Return: “Input”
- click “OK” and “Apply”
Basically, this tells farmerswife that if the Input equals 0.00, display F.O.C., otherwise display the original Input.
These are all of the comparison options available:
- Equals/Doesn’t Equal
- Starts With/Doesn’t Start With
- Ends With/Doesn’t End With
- Contains/Doesn’t Contain
- Is Less Than/Is Not Less Than
- Is More Than/Is Not More Than
Concat: The Concat (concatenate) operator is used to link together two or more elements/values to create a single, unified output. The Separator String allows you to add text and or spacing between the values or input.
Example - In a Production Plan, you want to make clear any notes added to the Booking.
- Edit the Details element and add a new String Operator type Concat
- right-click in the pop up window, select “New,” then select “Input” and click OK
- right-click and again select “New,” then find and select “Note” in the drop down menu and click OK
- in the Separator String field add a carriage return and type “*NOTE: ” then click OK.
Any notes added to Bookings will be displayed under the Booking Name. If there are no notes for a Booking, farmerswife will not add the Separator String text “*NOTE: ”. With this method, you avoid having the text “*NOTE: ” appear when there are no notes to display, which would be the case if adding a separate Text element to display the word “Note” under each Booking Name.
Math: It can be useful to apply the Math operator to existing values to add, subtract, multiply, or divide them by each other, or a specific number, etc.
These are the math operations available:
- Add (+)
- Subtract (-)
- Multiply (x)
- Divide (/)
- Modulo (%)
The resulting number can be refined in the following ways:
- Rounding the number - Standard Rounding (Nearest Integer), Rounding Up, Rounding Down
- Specify the number of decimal places to show
- Format - Number or Money
Example - You want an internal report to show the profit expected from a Project, which is basically showing the Sell amount minus the Buy amount.
- In the Edit window for the “Sell (Budgeted)” element, click Copy button
- In the Edit window for the new copied element, rename it “Profit,” then add a new String Operator type Math
- Right-click in pop up window, select “New,” then select “Sell (Budgeted)” from the drop down
- Right-click again in pop up and this time select “Buy (Budgeted)”
- In the Math Operator window, choose the Operation “—” (minus sign) and Format “Money.' Click OK and Apply.
Fields can be combined, so if you need a complex math operation, you can split it into multiple fields that each have math operations, and then combine those fields in another math operation.
Example 2 - Continuing with the above example, you may want to display the gross profit margin as a percentage. This will require the following calculation:
(Sell – Buy) ÷ Sell x 100
This can be achieved with multiple math operations applied to the element. As above, make a copy of the 'Sell (Budgeted)' element, rename it “Profit Margin %” and add the following three Math operators:
- Operator 1 - (Input) – Buy (Budgeted)
- Operator 2 - (Input) ÷ Sell (Budgeted)
- Operator 3 - (Input) x 100. Here, also set Rounding to 'Standard (Nearest)”, set Decimals to the number of decimal places that you want to see, and set Format to 'Number.”
- Add a fourth String Operator type “Concat” to display “%” after the calculated number. For the Concat, select “(Input)” first, then select “Value…” and enter “%”
The Modulo (%) operation finds the remainder after division of one number by another, e.g. 10 ÷ 3 = 3 with a remainder of 1. So if you used the Modulo operator in this example, the result shown would be 1.
- Although String Operators can only be added to dynamic data elements (D:), you can copy any dynamic element, rename it and use it for generating different information, as shown in the above examples.
- When setting the parameters for a String Operator and you want to select input from the list of all elements available, instead of scrolling through the list, you can select “Show Options In List” located at the top. It is then possible to filter the list to show the element that you want by typing either a part or the full name.