# Percent Formula Help

I have a table to track parcel values. Each parcel will/can have 1 record for each year. I am trying to calculate the percent change for each parcel between years. I have the following fields:

Parcel # - look up from another table

Current Year - number field to enter year

Previous Year - formula field to calculate previous year

CY Key - formula field to combine Parcel # and Current Year

PY Key - formula field to combine Parcel # and Previous Year

Value - numeric field to enter parcel value for the year

PY Value - formula field that returns the Value of the record where the CY Key equals the PY Key

I'm trying to create a % Change field formula: ((PY Value-Value)/PY Value)*100

The issue is, I think, the field types. The "Key" formula fields are Text fields, but the values I want to return are/should be numeric. So my formula for the PY Value is:

ToText(GetFieldValues(GetRecords("{143.EX.'"&[PY Key]&"'}"), 11)).

This formula correctly returns the PY Value (yay), but it is in a text field type. So in my % Change formula field I know I need to convert the values back to numbers but the formula below returns no results:

(ToNumber([PY Value])-[Value])/(ToNumber([PY Value]))*100

When I try just:

(ToNumber([PY Value])-[Value])

I do correctly get the difference between the two value fields, but as soon as I attempt to add any additional operations, the field returns no results.

Any idea of what I'm doing wrong in my formula so that I can get the percent change between the Value and PY Value fields?

I figured this out on my own. :) I couldn't understand why a similar use of the GetFieldValues formula worked on a different number field but wouldn't work on a currency field. I could only get the formula to work on the currency field if it was put into a text formula field. All attempts to simply convert the displayed value in the text field to a number resulted in displaying 0 or was blank.

I did notice that the text formula field would correctly display the located value but the GetFieldValues must act more like a snapshot of the matched field vs pulling the raw value. So what I was getting as a result was the number preceded by a $ (since my source field is formatted as currency it displays with the $) vs the raw actual number. So...

I searched for a way to remove the leading $ and updated my formula text field. The results was the raw number that then I could successfully convert to a number field. Since I like to have as few fields as necessary I then played with the formula so that I could place it into a numeric formula field and save myself a step.

Below is the final formula I used for any who are interested or may run into this issue themselves.

Formula - numeric field formatted as Currency:

var text PYValue =

ToText(GetFieldValues(GetRecords("{143.EX.'"&[PY Key]&"'}"), 11));

ToNumber(Left($PYValue,"$")& Right($PYValue,"$")))