There's a simple function to do what you want, but I'm concerned by your report that using List didn't work. Defining:
RightValues ( List ( Inspection::InspectionDate ) ; 1 ) as a calculation in the Unit table,
should have returned the correct date.
So should Last ( Inspection::InspectionDate ) or even Max ( Inspection::InspectionDate )
so if you still can't get this to work, put an unfiltered, unsorted portal to inspections on your unit layout. both the Last and RightValues methods should return the date from the record found in the last row of this portal. Max will return the largest (latest) value of all those listed in the portal.
List sort of works but seems to update all units to the date of the last unit inspected. At least I now know that I'm not completely off base and can put a little more effort into finding my problem or using one of your other suggestions.
List sort of works but seems to update all units to the date of the last unit inspected.
And as I said before, this should not be the case. Something isn't set up correctly. It sounds like you have a relationship that matches to all inspection records instead of just those for the current unit record. Perhaps you have used the X operator to match to all records in inspections but then used a portal filter to limit the records to those for the current unit. This would match what you describe and this is why I suggested using an unfiltered and unsorted portal to check and see what records are being matched up by your relationship.
If this is the case, you need to change your relationship to not use the X operator.
Unit::__pkUnitID = Inspections::_fkUnitID
is a relationship that needs no portal filter and which should work to support what you need here--including but not limited to accessing the most recent inspection date.
I think I have found the problem.
My relationship is:
Inspection numbers are actually a field from the company table that increases by 1 for each new inspection for that company. When I List the inspection numbers I get the full company list. I imagine the solution is to List the inspection numbers and then filter by the unit number, then Right value the filtered list?
The relationships that you show should work just fine IF you have set up the correct match field values for your relationship and set up this calculation to evaluate from the Unit table and not the Company table. A field from the company table that increases by one for each inspection is NOT the match field to use to link a unit record to its inspections. You'd use a field in Unit that uniquely identifies each unit.
I think I have done what you are suggesting. __pkUnit ID is matched to _fk Unit ID on the Inspection. I populate the _fkUnitID with values from a Unit value list.
Screen shot attached and a couple to follow.
Second screen shot
Final screen shot.
Your calculation evaluates with a context that refers to the wrong occurrence of unit. The Inspection table occurrence is linked to company and not to Unit.
Your list function should specify Unit 3 in the context drop down at the top of the Specify Calculation dialog. This is because it is the relationship from Unit 3 to Inspection that actually matches records by unit ID. There is not such relationship between unit and Inspection.
BTW, I see no purpose to linking inspection directly to company.
Thanks. That worked.
I alway thought my relationship should be:
Company----<Unit----<Inspection but for some reason it didn't work quite right. (So long ago I can't remember what the problem was)
I'd like my data base to be built properly so might go back to that if you agree it should work that way.
Getting back to your comment about no purpose linking inspection directly to company. I think I did it because I had problems seeing the Units owned by the company when I was starting a new inspection.
I am having a similar problem now with another three tables. I started with ( Mach. Category------<Mach. Type-----<Unit ) When I was on a Unit based layout and wanted to see the various Mach.Type but only the related values from Mach. Category I get a "No Defined Values" message. I finally switched to the relationship Mach.Type>-----Mach.Category-----<Unit and now when I am on the Unit based layout I can see the Category related Type's. Screen shot attached.
Does that make sense? I would have thought the related values should work thru the Category-----<Type----<Unit relationship.
Your relationships functioned exactly as you designed them to do, just not as you expected them to do.
When you have these one to many relationships:Mach. Category------<Mach. Type-----<Unit
When you are on unit, there can only be (at most) one related record in Mach. Type and at most one related record in Mach. Category.
So "I wanted to see the various Mach.Type but only the related values from Mach. Category" isn't going to work they way you wanted. If you were on a Unit layout and created a new record, there would, by definition be NO related records in Mach. Category as the new record would not yet link to any records in that table. Once you entered an ID linking the new Unit record to a Mach. Type record, you would then have exactly one Mach. Category record linked to it (or no records linked from that table if the related Mach. Type record isn't so linked.)
But keep in mind that this is not an "only one or the other" set of options. You can set up both sets of relationships using different occurrences of the same table to get one set of relationships such that you can select a category and see all related Mach. Type records and yet also have a layout based on Mach. Categories that lets you see either a list of all related Mach. Type records and/or a list of all related Units for that category in portals placed on that layout.
It sounds like I have been thinking the relationship worked in the reverse order to what it really does. Seems you have to start at the one and work toward the many. I have also been trying to minimize multiple table occurrences thinking it would make for a sloppy disorganized database.
Once again thanks for your help and patience as I struggle thru this process.
It's quite possible to start from the many and reference the one, but first make sure that your current "many" record is correctly linked to a record in the "one" table.