Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

How to Replace field with results of calculation

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
Alex Campbell
Asked:
Alex Campbell
1 Solution
 
Will LovingPresidentCommented:
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
 
ErikCayreCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now