Taking value from last related record

[proton]

I have two related databases (a master database and a related database). The related database has many records that are related to records in the master database. I have a field in the master database in which I want to put a value from the LAST related record in the related database. So each record in the master database will show the value from it's last related record in the related database. I did this once before and I've forgotten now. Please help. Thanks.

[LiveOak]

I don't know how to do this completely automatically, but you can create a another relationship with the related file, call it:

LastRecRel

match

Last with Record No.

Last is a new field in the master. Record No. is a record number field in the related file. Setup the second relationship with a script which sets Last = Max(FirstRelationship::Record No.)

then display LastRecRel::Field.

I don't know of a way to do this without a script step without a index based upon a relationship (which you can't do). -bd

[proton]

quote:

---

Originally posted by LiveOak:

I don't know how to do this completely automatically, but you can create a another relationship with the related file, call it:

LastRecRel

match

Last with Record No.

Last is a new field in the master. Record No. is a record number field in the related file. Setup the second relationship with a script which sets Last = Max(FirstRelationship::Record No.)

then display LastRecRel::Field.

I don't know of a way to do this without a script step without a index based upon a relationship (which you can't do). -bd

---

Thanks LiveOak,

I did it once before but I had forgotten how. I managed to get my hand on that old database and checked it out. The old database was an inventory system. It had an inventory database, which contained a list of products, product numbers (code for each product), last date purchased, last cost price and so on. It also had a purchases databases, where I created records for each time I bought a product. It contained product number, product (lookup from product database), cost price and so on.

Now in the inventory database the last cost price field, I wanted it to show the last cost price paid for the product, which is in the purchase database. So what I did, I created a field in the purchase database called date_Product. It is a calculation field and it just merely combines the product number with the date purchased as such:

=ProductNo&DatePurchased

Simple. Now I have a relationship between the two databases called Product, which relates the ProductNo fields in the both databases. The last purchase date field in the inventory database is a calculated field, which has the formula

=Max(Product: :DatePurchased)

This gets the last date purchased into the inventory database last date purchased field. Then I created a field (inventory database) called Product_date which is a calculation field which does the same thing as in the Purchases database:

=ProductNo&Last Date Purchased

So I have a field in the inventory database with the product number and last date purchased, and I have a field in the purchases database with the same thing. I then created a relationship in the inventory database called Productdate, and this was between the two fields:

Product_date: :Date_Product

So right there I formed a relationship based on both Product number AND date...then the last cost price field, I made it a calculation based on the above relationship as follows:

=Max(Productdate::cost price)

Now this works..and as it is not a lookup it automatically updates whenever I add a new record in the purchases database for the product. I've done the same thing with my new database but it is NOT working!! I think part of the problem is that the data I want to pull (from the last record) is text in my new database, and you know how Max adjusts to text...so I think that is the problem, but when I made it just numbers, it still didn't work..Frustrating. Hope ya'll can help me. Thanks again though LiveOak. Hope this helps you to.

[This message has been edited by proton (edited October 24, 2000).]

[LiveOak]

The problem is the same one I mentioned in my first response. You can't base a second relationship on a field calculated from a related field. Related fields or fields calculated from related fields can't be indexed. In inventory, your relationship is based upon the ProductNo & LastDatePurchased calculation or ProductNo & Max(Product::Last Date Purchased). A relationship based upon a related field can't be indexed and won't work. Whatever you had working before wasn't this. If you copy the result of the calculation to a real number field using a script, it will work. I'll have to stand by my original comments. -bd

[proton]

quote:

---

Originally posted by LiveOak:

The problem is the same one I mentioned in my first response. You can't base a second relationship on a field calculated from a related field. Related fields or fields calculated from related fields can't be indexed. In inventory, your relationship is based upon the ProductNo & LastDatePurchased calculation or ProductNo & Max(Product::Last Date Purchased). A relationship based upon a related field can't be indexed and won't work. Whatever you had working before wasn't this. If you copy the result of the calculation to a real number field using a script, it will work. I'll have to stand by my original comments. -bd

---

LiveOak,

You are right..hahahahaha..Sorry, I checked it back, and I did have a script running with that one. But the thing is, I really want to have to avoid running a script with this one. I want it as automatic as possible...update instantly..so I went back and thought about it..and I realised that the whole solution was in that long post I put. I just had to change one thing...this part:

"So right there I formed a relationship based on both Product number AND date...then the last cost price field, I made it a calculation based on the above relationship as follows:

=Max(Productdate::cost price)"

hahahahah...I just cut that out and went into layout mode...and using the relationship between the product number and date, pulled the cost price field (i.e. the cost price field from the purchases database) into the inventory database and it worked!!! hahahaha...the beauty of portals/related fields!! it worked!! check it..same relationships and everything..and just pull the field with the data you want into the master database and it will show you the value from the last record..thanks lots of your help though. It forced me to go back and think it through. And sometimes that is the most important thing of all. Thanks lots man.