12 Replies Latest reply on Apr 1, 2011 12:02 PM by RickWhitelaw

# Need help calculating data entered into field

### Title

Need help calculating data entered into field

### Post

I'm sure this is an obvious question, but I haven't worked in FileMaker for a while. :) I'm trying to figure out how to take the data entered in a field and divide it by 100 automatically. Here's the details: I have a PMT calculation in the final field, but the user enters the amount, number of payments and interest rate in other fields. I have the interest rate field formatted as a percentage. As it is now, the user has to enter ".08" for it to show up as "8%". If they enter 8 in the field, it shows as "800%".

Any suggestions? Thanks!!

• ###### 1. Re: Need help calculating data entered into field

This can be done, but you don't want users who enter the data in decimal form to also have their data divided by 100. If 100 is the largest possible value that they can enter, use this auto-enter calculation on this number field:

IF ( self > 1 ; self / 100 ; self )

and clear the "do not replace existing value..." option.

• ###### 2. Re: Need help calculating data entered into field

Perfect! Thank you!

• ###### 3. Re: Need help calculating data entered into field
Phil, How would you then return 1%? The only way is to enter .01. Entering 1 would return 100% (as it should), 11 would return 11%. i find it, although I do like the calculation, best to attach a tool tip to the field or simply suggest the entry format in the field label.     That said, the calculation does what it's supposed to . It, however, would need to be accompanied by some kind of instruction regarding values of 1% or less, and take up as much screen real estate as the methods above. Rick.
• ###### 4. Re: Need help calculating data entered into field

Good catch!

Since 1 entered for 100% and 1 entered for 1% would look exactly the same no calculation will be able to discriminate. You'd have to decide which is better for your particular situation, assume 1 is 100% or assume it is 1%. The tool tip is a very good idea. Using a script trigger that pops up and asks for clarification might also be an option.

• ###### 5. Re: Need help calculating data entered into field

What if the calculation is IF ( self ≥ 1 ; self / 100 ; self )? I know that doesn't address the issue if the user enters a value less than 1, but these are calculations for financing interest rates, so it is unlikely that someone will enter a value that is less than 1%. In my situation, I think I should assume 1 is 1%. I suppose I could use a tool tip for those situations, though I don't think they will come up. Thanks for all your feedback! It is appreciated!

• ###### 6. Re: Need help calculating data entered into field

Well that illustrates my point. Changing the expression to greater than or equal assumes 1 to be 1% rather than assuming 1 is 100%. If that's the best fit for your solution, use it.

• ###### 7. Re: Need help calculating data entered into field

This calculation as Auto-enter will work no matter what the user enters (decimal or whole number):

If ( PatternCount ( Self;"." )>0;Self;Self/100 )

It's still not perfect. If the user enters "100." it returns 10000%.Enter "100" returns 100%. "1" returns 1%. "1." returns 100%.

This could be made into a Case statement to trap for the rare occasion when more than one decimal got entered.

• ###### 8. Re: Need help calculating data entered into field

That calculation doesn't seem to work if the user wants to specify something like 5.5%. The result becomes 550%.

• ###### 9. Re: Need help calculating data entered into field

CarrieB,

Exactly. If the number contains a decimal, it stays what it is (Self). "5.5" is 550%. ".055" is 5.5%. As I've said, I simply have the user enter the decimals. The Calc in my last post is convenient for when "55" (example) is entered, as it will return 55%. In the end I don't believe this is worth the trouble, but out of curiosity I'll see if I can get "5.5" to return 5.5% by using Position.

RW

• ###### 10. Re: Need help calculating data entered into field

CarrieB,

This should work as an auto enter:

If ( PatternCount ( Self;"." )>0 and Position ( Self; "." ; 1 ; 1 ) = 1;Self;Self/100 )

RW

EDIT: This could be simplified:

If (Position (Self; "." ; 1 ; 1 ) = 1;Self;Self/100 )

• ###### 11. Re: Need help calculating data entered into field

That seems to work, though it doesn't resolve the problem if the user enters a value less than 1.

• ###### 12. Re: Need help calculating data entered into field

A value less than 1 is a value that starts with a decimal point, so yes, if you want .5% you must enter ".005". However, entering "1" or ".01" will return 1.00%. ".2" and "20" return 20% etc. Either method of entry will return a correct result, and the data storage maintains integrity and can be used in calculations, so it's not solely for display.

It's very important to un-check "Do not replace existing values . . . "

RW