You can't use a portal in Table View as you have discovered, but you can set that same sort order in Manage | Database | Relationships such that the most recent related record is the first record and then you can include fields from that related table in your view.
But you may find that using List view--where your portal will work, BTW, may produce a much more user friendly way for general users to work with your data.
Thanks, had a quick look, but wasn't quite sure how to set it up. I then thought there might be neater ways to achieve the desired result.
I'd like to make clearer my intention for the user ...
Most of the shops need to be revisited on a regular basis: some 6 monthly, some annually, some every 2 years, but some never again.
We need a function that can search through the bunch of individual records for the shops (there's a separate record for each visit to each shop), determine the most recent visit and make a sub-set - in Table View , preferably, due to ease of scrolling through multiple records; but not essential. This needs to be an active, editable, sortable layout rather than a report.
This needs to be an active, editable, sortable layout rather than a report.
sounds made to order for a list view layout. List view layouts support portals, buttons and other layout objects not visible in a table view. They also allow you to use layout text to label your columns instead of internal field names that can be cryptic.
To specify a sort order for a relationship, go to manage | database | relationships and find the relationship line between the layout's table occurrence and an occurrence of the related table. Double click that relationship line, then click the sort button for the related table and you'll get a dialog where you can specify a sort order for the relationship where you can sort your related records in descending order by a date, timestamp or auto-entered serial number field.
Then you can use the field tool to add fields from this related table to the body of your list view and the fields will show data from the most recent related record.
OK, I guess I could create a list view where the relevant fields are contained on 1 line for quick scrolling, then include buttons or check boxes to refine or re-sort results.
I'll try that, thanks. Also hope I have success with the latest date.
Yes, created a new layout in List View, which nicely displays all the shops, but I haven't been able to work out how to get the latest date displayed, rather than the earliest.
Here is comment from you from an earlier query long ago (had to put database mods on hold):
If you have a relationship:
Then you can define a calculation in Table1 as Max ( Table2::DateField ) to get the most recent date of the set of records in Table2 that are related to Table1. You can also define a "maximum of" summary field in Table2 to get the same date--just place that summary field from table2 ond table1.
And summary fields also max dates for a found set of for sorted groups within the found set in a summary report.
So I created a date field in a new layout (data from Table named Shops) named LastVisit with the calculated value: Max(Shops::Date1). What's wrong? The result in the displayed LastVisit field is: "0/00/0" ????
Since there are no dates in your new Date field, You are taking the Max of an empty field.
There is no need for any calculation field you can sort the relationship to show the most recent related record as the first related record and then you can add fields from that table directly to your layout. This was spelled out earlier in this thread.
I've been trying the Manage/Database/Relationship sorting without success. I now believe I know why.
To recap: I have a set of records which includes one record for every visit to every shop. With your p[ast help I created a separate Table, then used a Unique Field in order to copy in only one record for each shop. I then set up the portal. All good. Works nicely with the portal displaying all the visits to each shop.
My problem is that the original filtering grabbed the shop record with the earliest date as the only copy, hence my problem with latest dates.
So now it seems I need to be able to filter the original table using the unique ShopID field, but selecting only the shop record with the latest date. Is that possible/practical?
There's a further complication ... The display of latest visits needs to be regularly updated. This means, I think, that I need a script that will hunt through the original table ("Table") and separate out one record for each shop, being the latest visit. This needs to be done frequently as any new records (still one per visit per shop), including any unique new shops are added to the original Table("Table").
Perhaps I should have started a new thread???
As I answered in your new thread, you need at least a relationship to the original table of shop visits so that you can use either a sorted relationship or a sorted portal to access the most recent visit. This data will not be present in your table of one record for each shop--nor should it be.