calculation fields

[krusader]

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).]

[Chuck]

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

[charper]

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?

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

[krusader]

Hi

thanks for the reply

the trouble is that user only enters in the interval and based on the value for the interval FM calculates the begin and end...

is that possible with calculations??

[krusader]

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

[krusader]

what I am trying to say is that can calculation fields look at a previous record and copy/paste the info from a previous record into the current record...

[BobWeaver]

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

[BobWeaver]

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.

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

[Woodelf]

I'm pretty sure you can't do this with just calculations because you would end up with a circular argument, scripting it would work tho'

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

Cheers

Coops

[LiveOak]

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

[BobWeaver]

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 . 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).]

[krusader]

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..:...