I have a DB for change orders and am trying to define fields so that the completion date of the project will always be automatically updated. In each record I have two date fields(CurrentCompletionDate) and (AdjustedCompletionDate) and a field in which I manually enter the number of days added to the contract for that particular change order. I have the correct calculation to auto-calculate the adjusted completion date from the current completion date, but my problem arises when I go to create a new record. I need the CurrentCompletionDate field on the new record to auto-enter the value from the AdjustedCompletionDate field from the previous record. I think it's an elementary solution, but I just don't see it. Any help?

Hello kshell,

You would probably be interested to take a look at a demo file available for download from:

http://www.nightwing.com.au/FileMaker/demos2.html#d5

- it illustrates a technique for accessing values from adjacent records.

Hi Ray,

Thanks for that link. That looks like it will work perfectly, and also shows me a technique for my next question, which was how to perform a similar operation to keep a running total of the contract value of the job.

kshell

Wow, I thought that would be easy, but I was wrong! I'm a contractor and Filemaker novice, and every job has anywhere from 1 to 15 Change Orders and I need to keep track of the constantly changing completion dates and contract amounts.

My database is called Change Orders, and I have a four fields in each record that need to work together: "Original Completion Date", "Current Completion Date", "Days Added To Project", and "Revised Completion Date". The value contained in "Original Completion Date" is from a related database, Jobs; that's no problem. "Days Added to Project" is a number, manually entered for each record, also not problem, and "Revised Completion Date" is a calculation of "Current Completion Date" plus "Days Added to Project", which works correctly and allows for weekend days. My problem is with getting "Current Completion Date" to contain the value from "Revised Completion Date" in the previous record. The link suggested before showed how I could access a text field from the previous record, but I don't see how to make it work with the result of a calculation. Likewise for contract amount: "Original...", "Current...", "Amount Added to Contract", and "Revised...", which I haven't yet tackled.

Any help with my question will be greatly appreciated!

Hello kshell,

The demo shows a method by which the recordID of the preceding (and subsequent) records can be determined and used as the basis of a relationship between them.

Once a valid relatiopnship has been established, any value can be passed between the related records, including text, number, time. container etc, and these values can be raw data, calculations or summaries and can be either stored or unstored - as with any other relationship.

The fact that the demo happens to use text values is purely by way of illustration.

You haven't exactly said so, but I gather that you are not merely talking about passing a calculated value via the relationship, but having the calculation reference its own value from the preceding record, and that that is (naturally) giving you a circular definition error. Calculations cannot normally reference themselves.

It may be that you would be able to achieve what you want by simply adding a summary field configured to deliver a running balance - or by referencing the field via a literal parameter within the GetField( ) function (a method by which the circular definition problem can be addressed, but one which will then not refresh reliably when values change).

However, ireespective of that, on the same page of demos that I referred you to previously, right above the one you downloaded, is one which shows how to run a calculation through the records of a database. The direct link to it is:

http://www.nightwing.com.au/FileMaker/demos2.html#d4

You will find that the demo is a little more complex, because it also includes techniques for limiting the cross-record calculation to the records that are in the found set. If you don't require that option then your implementation may be rather simpler.

kshell,

Here's a brief explanation of my file. I have an end-of-day reconcilliation that needs to take place, and since I won't be "zeroing out" my cash box, I need to keep a running total. So, I need to get the end of day balance from the prior day, and use that as a starting point for the current day.

Here's the script that I created to do this:

-Go to record/request/page [Previous]

-Copy [select, "endingTotal"]

-Go to record/request/page [Next]

-Paste [select, "beginningAmount"]

This script is set to run when a new record is created. However, as CobaltSky pointed out, this will not take into effect any changes that are made to a prior day (e.g. adjusting a sale) since the value is only generated when a new record is created. This may be an acceptable limitation, or even a desired feature (e.g. sales are adjusted with a return/adjustment form), but if not it sounds like CobaltSky's demo uses a true relationship which will update values if things change on a prior day.

Hope this helps. I'm thinking I need to check out the demo...

Hmmn...

I'd say it would be worth you:

1. having a look at the second demo to see if that gives you a further option, and

2. clarifying what the client/user needs and preferences are so that you are in a stronger position to know whether a script-based or calc-based solution will be the most suitable.

HTH