Tool Mastery | Multi-Field Formula

[ymajid]

I'm trying to write a formula using 2 columns of data as well whole numbers.

Example)

Fields I'm using: Gross Revenue, Commission Rate

Trying to calc ("Gross revenue" *(1-"Commission Rate"))*12

Error message: "Formula: invalid type in subtraction operator"

Am I entering the formula incorrectly? Please advise. Thank you in advance!!

[NicoleJohnson]

Hi!
Try wrapping ToNumber() around the fields containing numbers in your formula, or placing a Select tool in front of the Formula tool and changing those fields used in number calculations to a number type. That should help!

(ToNumber([Gross Revenue])*(1-ToNumber([Commission Rate])))*12

NJ

[ymajid · Accepted solution]

Hi Nicole!

Thank you for your insight!! I think I've almost got it but I'm stuck on one more issue...now I'm getting an error that reads "The field "" is not contained in the record. (expression #1)". Could this have to do with the data in the columns "Gross Revenue" and "Commission Rate"? 

[NicoleJohnson · Accepted solution]

Nope, that particular issue means that you didn't give a name to your new field in the formula tool :) Add a name in the name field (in the upper left corner of the formula section you're working in) - you can either give your field a new name or click an existing field to replace its contents with the formula you've created!

NJ

[ymajid]

That worked perfectly - thank you SO much!!

[NotQuiteClueless]

That's been bugging me for the last couple of days. Many thanks