Jump to content

calculation fields


This topic is 8547 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi

this is what I am trying to achieve

I have three number fields "begin" "end" and "interval"

I want the values of begin and end to be based upon interval so the user sees this

all in years...

Begin End Interval

0 1 1

1 5 4

5 10 5

10 15 5

I know it can be easily done with a script but I want the user to see the values being entered automatically for begin and end

any help is greatly appreciated!!!

THANKS

[This message has been edited by krusader (edited December 28, 2000).]

Link to comment
Share on other sites

I'm not sure which it is that you want so I'm going to give you the calcs for all three possibilities (at least that I see).

1: begin and end are entered by the user and you want to calculate the interval. Set interval to a number calculation with the following equation:

end - begin

2: begin and interval are entered by the user and you want to calculate the end. Set end to a number calculation with the following equation:

begin + interval

3: end and interval are entered by the user and you want to calculate the begin. Set begin to a number calculation with the following equation:

end - interval

Chuck

Link to comment
Share on other sites

Calculations can do just about anything except read your mind. You've stated that you want both begin and end to be calculated from one number: the interval. But the pattern you have shown us does not seem to imply any discrete rules for this calculation. For example, according to the your number series, its possible for an interval value of 5 to result in two different begin and end values.

5 10 5

10 15 5

Interval=5 results in begin=5 and end=10

Interval=5 results in begin=10 and end=15

So which is it? The first one or the second one?

------------------

Link to comment
Share on other sites

Hi

it is a list view

record 1 the user enters 5 as the interval

begin=5 and end=10

record 2 the user enters 5 as the interval

begin=10 and end=15

record 3 the user enters 4 as the interval

begin=15 and end=19

record 4 the user enters 1 as the interval

begin=19 and end=20

and so on...

the field begin is the field end from the previous record except in the case of the first record where begin is equal to the field interval...

think calc can do this???

THANKS

Link to comment
Share on other sites

If you want to do this without a script, you will need a self-join relationship in this file. Create a field called SerNo which is an autoenter serial number. Then create a calculation field called SerNoMinusOne which is equal to SerNo - 1. Set up a relationship on the current file (self-join sort of) called LastRecord. Then link the relationship with SerNoMinusOne on the left side and SerNo on the right side. Now you can make the Begin field autoenter calculation which is equal to LastRecord::End and the new end value will be calculated as LastRecord::Start + interval

Link to comment
Share on other sites

Just an afterthought:

I hope you don't intend to allow the user to go back and change the interval in record 20 and have it automatically update records 21 through 500. If so, there is special software for doing this. It's called a spreadsheet. smile.gif

[This message has been edited by BobWeaver (edited January 03, 2001).]

Link to comment
Share on other sites

Done in the normal way this does generate a circular reference error. I've done a multi-level marketing data base this way. I avoided the circular reference error by using a second file mirror file (my term) with an A = B, B = A to fool the check. The problem is that FM does not reliably update the calculation all the way up the chain. No syntax errors are generated, it just doesn't reliably update. -bd

Link to comment
Share on other sites

It does work as I described, but there are limitations. First, it's not a circular relationship. If it was, it would definitely not work. It is a long multi-linked (my term) relationship. It will work as long as you use look-ups and auto-enters. You can't just display related records on your layout and have it work.

It will correctly calculate each new record as you add them, but if you want to go back and change one record, then you will have to create a script to update everything.

FileMaker never looks past one record in a chain when updating. I think thats why LiveOak's mirror file has a problem too. The trick to get around this is to use lookup fields, and do several Relookups. Of course that involves a script frown.gif. If you know that the chain of linked records is n links long, then if you do n relookups, it will correctly update the field info. I've used this technique to traverse tree structures without any problems once I figured what the heck was going on.

[This message has been edited by BobWeaver (edited January 03, 2001).]

Link to comment
Share on other sites

thanks guys...

I do have the option to use scripts which would work perfectly...but I don't want the user to click on any buttons...

I think using a loop and pausing the script every 1 sec might do the trick but during the pause time the user can't enter anything which would not look good on the program...

is there a way to set up a field so upon exiting the field the script would run automatically..:...

Link to comment
Share on other sites

This topic is 8547 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.