Hi, I'm new to FM so please bear with me.

I have a flat database which contains information about vehicles, specifically when they were recieved and shipped. What I need to find out is the number of days between the date recieved and date shipped. The table looks like this:

VIN Trans_Type Date

1 ARR 1/1/2005

1 xxx 1/2/2005

1 OUT 1/3/2005

2 ARR 1/2/2005

...ect...

Where ARR is the code for when the vehicle arrived, OUT is when it shipped and xxx is any number of other Transactions (Trans_type) that occured in between, meaning that there could be upwards of 20 - 30 records between the ARR and OUT.

The nice thing is that when the data is sorted by VIN, it places the Trans_Type in cronological order, thus ARR is the first record for each specific VIN and OUT is the last.

The database is quite large, over 1 million records, and I'm stuck (since I know squat and this project was dumped on my desk). I run FM pro 7 on Mac OS panther.

Any help is greatly appreciated.

Brad

Welcome Brad,

You could do a self relationship between the first record and the last record.

In the table define a field calculation text result with the word "OUT"

The relationship would be a mult-predicute relationshp between the vin number and the status & the constant key.

See attached

VIN.fp7.zip

Edited July 22, 200520 yr by Guest
changed file

Thanks Ocean but I need a bit more detail...

I can't for some reason download the example you uploaded, the error I get is:

Parse error: parse error, unexpected T_STRING in /home/fmforums/public_html/uploads/1121985808-VIN.fp7 on line 9

So I'm not sure what you mean by a relationship between the first record and the last. How do I do that for each VIN? (there are more than 100,000 of them, each with an ARR, an OUT and any number of other Trans_types stored in separate records)

My guess is that you are suggesting that I should define a self-join relationship as follows

new_relationship::VIN=VIN

&

new_relationship::Trans_type=Trans_type

&

new_relationship::Constant_key=Constant_key

where constant_key is the new calculation field with the word "OUT"

how does this help me calculate the time elapsed between the record for a Trans_Type=ARR and Trans_type=OUT for the same VIN? (sorry if this is a dumb question, or if it is obvious from the file you uploaded, but I'm new to all of this, including relational databases)

Thanks

Brad

Sorry about the download I forgot to stuff it first.

I think this will help better explain it.

(I re attached the file to the first post.)

Actually, you could simply make the relationship

yourTable::VIN = sameVIN::VIN

Then a calc of:

Max ( Same VIN::Date ) - Min ( Same VIN::Date )

--

Note that both solutions will have refresh problems, due to double reference to related fields.

Edited July 22, 200520 yr by Guest

Similarly, you could sort the relationship by ascending Date and use

Last(Same VIN::Date) - Same VIN::Date

or vice versa. The speed difference in this case is probably negligible though.

You might also add a test for an 'out' record before the calc evaluates.

Let( L = Last(Same VIN::Trans_Type);

If( L = "out"; L - Same VIN::Date )

)