Solved

How to Replace field with results of calculation

Posted on 2010-11-14
2
581 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:Alex Campbell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

623 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