Solved

How to Replace field with results of calculation

Posted on 2010-11-14
2
576 Views
Last Modified: 2012-05-10
I need to update a field with the results of a calculation every time I enter a record.

The destination field cannot be a calculated field because FMP does not allow indexes on calculated fields.

The destination field must be indexed because it is used for a portal.

Example:

Original entry $234.37 into Amount Paid.
Calculation Results will be 2 into Amount Category field since the amount is between $200 and $299.  

The portal will show all payment processing rules for a payment in that amount range.
0
Comment
Question by:Alex972
2 Comments
 
LVL 24

Expert Comment

by:Will Loving
ID: 34132416
Actually, FMP does allow indexing on a calculation field as long as the fields referenced in the calculation are themselves indexed and in the local table. Check to see that your Amount Paid field is indexed. You should then be able to use an Auto-Enter calculation on the Amount Category field. You could also create a relationship from the Amount Paid field to the Rules table using to predicates and greater than and less than operators.

For each rule define a minimum amount and a maximum amount that that rule applies to, then create the relationship such that:

Amount Paid >= Minimum Amount
Amount Paid <= Maximum Amount
0
 
LVL 2

Accepted Solution

by:
ErikCayre earned 500 total points
ID: 34135594
Assuming you prefer not to change field definitions and/or DB structure, you can use Script Triggers for this.

Make a script which updates the field in question with the current calculation result

Go to the layout, on which you need the field updated "every time I enter a record."
Go to Layout Mode
Go to Layout Setup...
Select the tab Script Triggers
Check OnRecordLoad and select the newly created script

The effect will be that whenever you access any record using this layout, the script will fire and update the contents of the calculated field.

This assumes the field in question is in the table which the layout is based on. If it is in a related table, you will need extra trickery in the script to ensure the correct related record is updated...
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now