Can Filemaker do this? Auto-enter data from a previous RELATED record

[Beladebz]

Here is the situtaion that has me stumped. (sorry it's kinda long)

I have 2 files--one that houses the Store information and one that has the Maintenance Certifications for 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 related Maint Certs.

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 (Maint Cert) data into a record in the MC database, and it will gernerate the new blank MC with the all the previous records data that needs to be carried over to the new one I will mail out.
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.

Hope that make senses.

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

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 #914
Date - 11-1-06
Current Usage - 2500
Previous Month - 500
Total usage - 2000

Record ID 2
Store #251
Date - 11-2-06
Current Usage - 15000
Previous Month - 11000
Total usage - 4000

Record ID 3
Store #914
Date - 12-1-06
Current Usage - 5000
Previous Month - 2500 (from record #1's "Current" field)
Total usage - 2500


In my mind, it's just a simple "find the last record for said store number, where the visit type is "monthly"....look at the "current" number and enter it in the new records "previous field"

Here is how I tried to solve
it........................................................

I set up 2 relationships in the Maint. Cert db. based on a
recommendation. One of them worked 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).

So I worked on the second self-joining relationship and I thought I got it to work.

I created a new record for store 914 (obviously the first one one have any previous data so the "previous" field was blank) and all the calucations were correct when figuring out the total water usage. Then I created a new record for that same store and all the correct data automatically entered into the right fileds just like I wanted them to.........BUT if I created a new record with a different store, then just like the first record, it was a new blank
record for that store and if I entered information and created a new one for that store--data was correct.

After I entered 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.

After being stumped for a couple of weeks now (and trying everything I can think of) I am turning to you guru's for help

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! I am working on FMP 6, and I am willing to send the file if needs be.
Deb

 

[CryoGen]

I know this doesn't help you right now, but any chance you could upgrade to FMP 8? If so you can store the values you want from the previous record as variables and use the set field script step to populate those fields after your new record has been created.

For FMP6, you can create global fields and store the carry-over values in them, then use those values after you've created your new record (also using the set field script).

As an FYI, FileMaker will stop supporting FMP6 early in 2007.

 

[JeanW]

It's not because FMI will stop the support that FM6 will stop working...

What you want is finding the previous reading of a meter, whereever that is in your db, or whenever it took place, so you can compare it to the new reading and calc the difference.

If you track only 1 meter, this is not so difficult to implement.

Make sure you have an auto enter serial number field "serial" in your file, if you don't, make one, and reserialize it.
Make an extra unstored calculation field "serial_previous", that says "serial - 1".

Make a new relation "record_previous" to your own file.
The "left" key is your "serial_previous", the "right" key is your "serial" field.

We're almost done. Make a calculation field "meter_reading" that says "reading - record_previous::reading".

That should work as well.

If you keep more than 1 meter, it becomes a bit more complicated but is better I think in some ways as the auto-enter Max() solution, since you cannot directly enter the meter ID on record creation (afterthought - this works from a portal).
Using an self-link lookup technique has some "features" a direct aggregate calculation does not have, which allows us to track multiple meters.


More than one meter.

The serial number is the same
serial, type number = auto-enter Serial (1,2,3...)

A new field: meterID, make your meter ID's numbers like 1000 for meter 1, 2000 for meter 2, etc... this is crucial - and you'll find out why in a sec.
meterID, type number

Another new field: meterSerial
meterSerial = meterID + serial

it's about the same technique as in the one meter example, but slightly different:
meterSerial_previous = (unstored) meterSerial -1

this one we know already:
reading, type number

now make a selflinkjoininternalyouknow relation from meterSerial_previous to meterSerial and call this relation "record_previous" although this is probably a bad name for it, since the relation does not exactly point to the previous record for that meter, if any record at all. You'll see.

and here it finally comes: make the field reading_previous, and make an auto-enter lookup on the "record_previous" relation, BUT BEWARE -> use "if no exact match, copy next lower value" in the lookup.

That's it. give it a try.

What happens? The secret is in the lookup feature "copy next lower value", an old feature that cannot be beaten by a direct relational calc.
The record with f.i. meterSerial 1008 looks for 1007, doesn't find it, and goes for the next lower meterSerial, which is 1005, and that's the reading we want.

Sounds and looks complicated, but it only sounds and looks complicated, try it out on a separate file to get hold of the technique....

 

[Beladebz]

Thanks to both of you for responding.

Right now I am programming on 6 because I haven't convinced the company to buy 8 yet. If I can get this auto-enter thing to work for me, then I am sure they will purchase it (at least I hope so, it would make my life so much easier here at work ) I will have to learn 8 though and that's a little scary for me.

With response to JeanW's post...I am still finding that it's not working--I'm sure it's a user error and was wondering if I could send you the file to see what I am doing wrong. I am using the mulitple meters since we have about 75 stores. In the store db I created a field called MeterID for each store. Since we read the meters monthly, I used a 10,000 as my base number instead of 1,000 so I will never run into the problem of more than 1000 meter readings. Also, when I enter the store number, the MeterID is automatically populated along with the store address and some other data. Thus the field MeterID in the certs db is a look up based on the store (that way I don't have to remember each individual store's MeterID)

In the following statement, I am lost on which field reading-previous is looking up. I created the field, created the relation, but I don't know which field on the relationship it's looking to for the answer--that if there is no match, it will copy the next lower value.

"make the field reading_previous, and make an auto-enter lookup on the "record_previous" relation, BUT BEWARE -> use "if no exact match, copy next lower value" in the lookup."

I tried both reading, and reading previous, but one of them made all the records have the same previous meter reading even if it wasn't for the store.

If you are willing to take a look at it, just let me know and I will send the two files.
Thanks
Debi

 

[JeanW]

According to forum policy it is not allowed to mention addresses.

It-s kind of hard to exchange files, although that would be an easy way to look and show you the way, rather than going into lenghtly text.

See if you can use jrraid at the well known ya hoo period com provider. Give then a day or so to implement the formula.