very newbie: sub-summary for latest record

[NCE]

hello all. i'm trying to do a report to show the latest date (or record) only for each product number. what i did was create a sub-summary based on the product number and tried to sort the date in descending order so that only the records by product number with the latest dates would show. but it's still showing the first records per product number.

what gives? or do i have it all wrong?

thanks guys.

[comment]

Do your report from the Products table (i.e. the layout of the report is showing records from the Products table). Place any required fields from the related table on the layout. Make sure the relationship is sorted on the related table's side by Date, descending. This will make the latest related record become the first related record, so that's what the related fields will show.

[NCE]

got that. i did it as a sub-summ also so that only the last records (latest date) per product will show. worked, but now. . .let me explain.

im working on 2 tables products and transmittals where transmittals shows the records of where the products were sent and their current location. i'd like the report to show where the products are currently at, and the total number of each product.

from the pt of view of products, i could not get the products to summarize and show only the latest date per product record. even if i sort descending. i only get the first record to show (which is the earliest date). but i do get a correct count of the products. i did a calculation field to count product numbers and this is ok from this pt of view.

other way around from the pt of view of transmittals, i get the correct summary where it shows the latest dates per product (where it's currently at), but i could not get it to count the total products correctly. i inserted the summary field to count the product numbers from the products table but it always gives me a count of 1 per product type. remember this is from pt of view of transmittals.

sounds so simple and yet. . .

[comment]

from the pt of view of products, ... i only get the first record to show (which is the earliest date).

Then your relationship is not sorted the way I suggested. Go to Define Database... Relationships, and double-click the = sign between Products and Transmittals. At the bottom of the Edit Relationship window, you will see two 'Sort records' boxes. Click the one that's under Transmittals and select date, descending.

[NCE]

thanks much, comment. it worked. but shouldn't it work equally when being sorted from the records menu?

btw, can anybody recommend a good fm7 book for me?

[comment]

shouldn't it work equally when being sorted from the records menu?

No. The Sort Records... command under Records sorts records in the found set of the current window. You can open another window and have the records sorted in a different order there.

A relationship ignores found set/s.