Auto-enter data from related previous record

[beladebz]

Ok maybe the solution is super simple, but I am stumped.

Here is the situation (working in FMP6)

I have 2 files--Stores and Maintenance Certifications of each store.

We are tracking the water usage at each store so each store has it's own set of Maint. certs.

The store db houses information about the store, manager, hours, dates of install and so on.

The Maint. Certs database tracks any visit made to the store--either for monthly billing, emergency or QA visits. (we only bill on data from the Monthly visits)

Each record in the "Stores" db has a unique recordID that links the "Maintenance Certs" to the individual store.

Currently I have a layout in the Stores db that is a portal of the Maint Certs that are related to it. My thought was that I could enter certain data (like a date to generate a new Maint cert on that store) then set up a button to take me to the Maint cert db and fill in the rest of the data there.

I don't know if this is the best way to create the records in the Maint. Cert. I thought this would be the easiest way to generate the store information at the top of each Maint. Cert.

Any ideas?

Right now the procecss is: I get a completed MC back in the mail, I enter the data into an excel spreadsheet, then I go to the "form" (a Word Perfect document) and overwrite all the old information with new data from the sheet in my hand. I print the new blank certs with a new date, mail them to our service techs, when I get it back, it starts over.

It's such a pain to double enter the data--especially in Word Perfect.

What I want to happen is enter the MC data into a record in the MC database, and it will gernerate the new blank MC based with the appropriate data that would carry over to the new one I will mail out. (then in the stores database, I will track if that stores new monthly cert has been mailed).

Each Maint. Cert. will have a unique id, so when it comes back in to be entered, I can just search for that cert # and enter the data and be able to track which MC's still haven't been returned to me.

Does that make sense?

Each Cert has a field for current months usage, previuos months usage, and then a total gallons used (equals "current" minus "previous")

Now to the question.

Rather than enter "current" data into the new record as the "previous" data, I want to have FMP automatically input the "current" number from that stores most recent MC into the new records "previous" field.

So essentially it would look like this...

RecordID 1

Store #100

Date - 11-1-06

Current Usage - 2500

Previous Month - 500

Total usage - 2000

Record ID 2

Store #200

Date - 11-2-06

Current Usage - 15000

Previous Month - 11000

Total usage - 4000

Record ID 3

Store #100

Date - 12-1-06

Current Usage - 5000

Previous Month - 2500 (from record #1's "Current" field)

Total usage - 2500

I hope this makes sense. Any info would be great and if you need me to clarify more, I will.

Debi

[Søren Dyhr]

You need to combine these two templates:

http://www.filemakerpros.com/LULAST.zip

http://www.filemakerpros.com/PORTFILL.zip

--sd

[beladebz]

Thanks for the reply....I combined the two examples as best I could and have almost got it figured out.

I set up 2 relationships in the Maint. Cert db. One from the Portals/Rows example and one from the LULast Example.

The one that is the LULast works just great.....IF I was trying to look up the previous records data.

But unfortunately I am not. I need to look up a previous related records' data (auto-enter the data from store #914's last record).

I have been stumped for a couple of weeks now, trying any possible change that I can.

I thought I got it to work. I entered a new record (obviously the first one one have any previous data so the "previous" field was blank) and the calucations were correct when figuring out the water usage. Each time I created a new record for the same store # as the record right after the one I just entered, the auto-enter/lookup data is entered beautifully.

Here is the part where I pull my hair out.

If I enter a new record with a different store number (lets say 251) and then start creating more records for store 251, the data sill enters correctly, but if and try to create a new record for store #914, all the data resets to blanks.

I set up a script to generate a new record for the current store # (this will be the new cert that I will mail out). On each record, the "current" field is entered by hand (me).

Then the "previous" field is actually the "current" field defined in the relationship of "match last prev. = match last" (Portals/Rows relationship - 000001 {key-store key] 00000034[serial number] = 0000010000033)

Again this works just great as long as the record it's pulling the information from is just before it. If I enter a new store #, it starts blank and only pulls data from that stores records that are sequential before it. But the minute I create a record with a store that has already had data entered, it somehow can't look that far back.

I don't know what I am doing wrong...any insight would save me from being a bald woman LOL. Now I gotta throw a twist into it too. There are thre different service types...monthly, emergency & QA...I only want the information to be pulled from the last MONTHLY visit (because that is the only meter readings we bill) So not only does it need to look to the previous stores record--it needs to find the previous stores MONTHLY record.

Ok thanks for any help!

Deb:)