Auto Enter Value from previous record

[Baldrick]

Hi! This is probably a very stupid question, but how can I Auto Enter a value in one record based on the value of a previous record. What I'm trying to do is produce a milage book, where the end milage on one record (Day) is the start milage on the next. Look Up's seem to only work with other tables and I'm less than useless with scripts....

Any suggestions would be greatly appreciated,

Ben.

[Deep Thought II]

this questions might concern two problems, first is how would u list or sort the records, since it will determine which is the previous record and which is the next.

the second problem may be solved by using a global field which stores previous record's end milage...

then what's left is how u would implement to control ur program flow to make it work...

[Baldrick]

I have 365 records which are sorted by date. Each record represents a day in 2003 and contains fields for Start and End milage (currently empty), Work milage and Private milage.

My Work data is already in the database, as is the start milage on 1/1/03. I need to be able add Private milage to pad the total so it matches the vehicles actual milage at the end of the year. I then need to be able to print a report for each month with a total of Private and Work miles.

I'm sure this must be really easy, but apart from copying and pasting 365 times, I don't know how.

Cheers, Ben.

[transpower]

Create another table occurrence of the first table. Relate the two by record number--the record number minus one from the second table to the record number of the first table. Then place the beginning mileage field of the second table as the ending mileage on the layout of the first table. All of this assumes, of course, that the records are contiguous.

[Baldrick]

Transpower! Thanks for your suggestion.

Unfortunately I couldn't figure how to sort the Record number increment with just one table, but managed it with two. The problem now is that after editing a number, I have to Relookup Field Contents on three different tables. This only updates the entry immediately after the changed value. To make a change in January I'd have to cycle through the 3 tables and Relookup 1050+ times!

Would writing a script for this be a decent solution(I'm not sure how fast FM will do 1050 consecutive Relookups), or is there a better way of doing this?

Cheers, Ben.

[Baldrick]

Great - I just threw my first script together to do this and it worked. Shame that the Relookups take so long, and that you have to hit the OK button before each one.

Arrrggh!! I think that the End Loop if Record Number = 365 was a bit optimistic.

[LaRetta]

Baldrick, there may be a simpler way for you. You don't need a script at all - or even repeated relookups, if I understand your situation correctly. All you need is to capture the prior Ending Balance from the last record. Then why are you sorting them? You won't need to sort them if the relationship looks one day backwards and not one record backwards.

I've attached a simple (demo) process which is very easy to implement. I have added field comments to hopefully clarify it. It requires one self-join from your Day (date field) to Day - 1 (date field). It also uses auto-enter serial number to increment the date by one each time a new record is added. If I were attacking this problem, this is how I would handle it. I hope you will find it easier than relookups or scripts.

There are no records but I've pre-filled the starting global with 500 (indicating your starting mileage) to prime the process so replace that figure on the first record with your real start figure. You will notice that the starting 'serial date' is 731216 - which is 1/1/2003. Add a record, fill in your mileage, add another record and see how it works for you. The auto-enter incrementing date is unnecessary of course; but I liked it and thought you might find it useful. If you ever go back and change/add Personal Mileage - or even add all personal mileage once at year end, script with only one Relookup Field Contents [ No dialog; Day ] will suffice.

LaRetta

Mileage.zip

[LaRetta]

Baldrick, I might explain further how to incorporate what you have into this. I was very short on time yesterday ...

Start with no records in this demo file. Be sure your start SerialDate is 731216 (check Auto-Enter serial). Import your work mileage directly into this as new records. No need to import your date because it will be created (I assume your work miles are in order). Be sure 'Perform Auto-Enter' is clicked because this will increment your serial and write your date. Then set your global Start Mileage to your real figure.

To pad your personal mileage in this process, you can run Replace Contents through it. What figure you use is up to you, but you probably don't want the same day mileage every personal day. Your real ending mileage - (starting mileage + Work mileage) will give you total personal. You could then determine the percentage difference of personal to work mileage (PersonalMileage divided by WorkMileage). If you don't care if the same figure PersonalMileage lists every day, then just divide by 365.

Then place your cursor in personal mileage, select Records > Replace Field Contents and enter calculation similar to: WorkMileage * .04 (or the percentage difference). This will insert PersonalMileage through all the records. Then perform Relookup on Day (your date field) and everything will adjust for you - line by line.

LaRetta

[MoonShadow]

"Relate the two by record number--the record number minus one from the second table to the record number of the first table. " Transpower, it is never recommended to relate on record number. I assume you meant RecordID? If related on Record Number, any sort or find will mess it up. And the -1 would not work on RecordID anyway. Not a good recommendation, if that is what you meant. Relating on date makes more sense here anyway.

[Baldrick]

LaRetta - You are an absolute star , Thankyou!

You've come up with exactly what I needed, and it works. I'm in a bit of a mad rush at the moment but I just wanted to let you know you've reduced my stress by about 95%.

Thanks again, Ben.