Formula 3

Operators/Functions Used

Concatenate (+), ToText, ToNumber, Multiply (*), Make Range (to), Subscript [], Parentheses ()

Formula Purpose

A sail maker, as part of his loan agreement with his bank, has to submit a detailed inventory analysis monthly. The analysis must include the cost of fabric in raw material inventory and the cost of fabric by item number for each item in finished product inventory.

The company uses one fabric for all of the sails it produces, and it uses the 5th and 6th characters in the item number for each product to represent the number of meters of material (rounded to the nearest meter) necessary to make that item.

In the form letter the manager sends to his banker each month, he wants the computer to automatically insert the quantity on hand, the item number, and the dollar value of the fabric for each item number.

Formula

ToText({file.QUANTITY}) + " each, Item " + {file.ITEM} +
", $ " + ToText({file.QUANTITY} * ToNumber({file.ITEM}[5
to 6]) * {file.FABRIC COST})

Result

With a fabric cost of $14.88/meter, the formula delivers the following result:

Quantity	Item	Letter Text
46	4423141006	"46 each, Item 4423141006, $ 9582.72"
27	4423081009	"27 each, Item 4423081009, $ 3214.08"

Explanation

• This equation uses ToText to convert the value of {file.QUANTITY} (a numeric field) to text so it can be used as part of a concatenated text string.
• It then uses the Concatenate operator (+) to join the text version of {file.QUANTITY} with the string " each, Item ".
• It again uses the Concatenate operator (+) to join the resulting string with the item number, stored as the value of the text object {file.ITEM}.
• It uses the Concatenate operator (+) one final time to join the resulting text string to the calculated fabric cost (expressed as text).
• To calculate that fabric cost:
  • _        The formula uses the Subscript operator [] to extract the 5th and 6th elements (meters of material used) of {file.ITEM} (a text object).
  • _    The Make Range operator (to) is used to establish the range 5 to 6.
  • _    The ToNumber function converts those elements to a number so it can be used in a numeric calculation.
  • _    It uses the Multiply operator (*) to multiply that number by the value of {file.FABRIC COST} by the number of units in inventory ({file.QUANTITY}) to arrive at the total price.
  • _    Finally, it uses the ToText function to convert the total price (a number) into text that can be used in the concatenated string.

Related topics

Formulas In Action