This can be done but to give specific advice requires more information about the stucture of your database and to more precisely define which record is the "most recent record".
I think you want the "most recent" record of all those visible in the portal, not the "most recent" of all those in the table from which the portal records are drawn, correct?
If so, then you can set up a self join relationship that sorts by date and use that relationship look up or auto-enter information in the way you want. I'd need to know what field or fields are used to control which records are visible in the portal before I can be more specific.
Please describe the table used in your layout and the related table that supplies the records you see in the portal. Please describe the key fields used in this relationship.
Thanks so much PhilModJunk!
And I'm sorry if my explanation is a bit limited, due to my limited FM experience.
Okay, yes, I'm pretty sure the record I want is the most recent visible in the portal. Let me try to explain the structure a bit...
So, I actually oversimplified earlier. First, there is a client table. Then for each client (we're a legal aid nonprofit), there can be one or more case records (via a case table). Then for each case, there are multiple activity records, via a "case history" table. Relationships between two numerical fields--Client and Case ID--link the three tables together. When a new activity (i.e. case history) record is created, it is assigned (via script) the same Case ID # as the case record to which it is linked. Last, the portal, located on a case table page, is just set to show "related records" from Case History.
Does that help?
So what you need is to add a "self join" relationship to the Case History table.
Enter Manage | Database | Relationships
Drag from Case History::CaseID out a inch or two and then back to the same field. A dialog box will pop up asking you to name a new "table instance". Name this new table instance "SameCase".
Double-Click the new relationship line linking Case History and SameCase to bring up the Edit Relationship Dialog.
On the SameCase side of this dialog, click the sort records check box and select the date field that tells you this is the "most recent" record.
Click the Fields tab.
For each field in Case History where you want to look up data from the most recent record of the same CaseID,
Double Click it to bring up the field options dialog box
Click the Auto-Enter tab
Click the Looked-up value check box
In the "lookup from related table" menu, select SameCase
In the Copy value from field: box click the field that you want to copy from.
Once you've done this for each field where you want this feature, Click OK until you've closed all the dialog boxes and try it out.
Thank you so much! That worked perfectly!
PS I apologize if that was something I should have been able to figure out on my own. But, as I mentioned, I work at a small nonprofit, so I'm actually just a staff attorney, doing my best to maintain our database on the side :).
No apologies needed. This wasn't a trick that just anybody can figure out.
This forum draws together both "Newbies" and experienced developers into the same community.
I'm just glad it worked for you.