Solved

How to Replace field with results of calculation

Posted on 2010-11-14
2
577 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Multiple statements in a calculation 4 742
Exporting data from FileMaker Pro 7.5 6 315
Change Filemaker Server 14 https port 3 121
url for a container field 1 356
Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 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

18 Experts available now in Live!

Get 1:1 Help Now