Thank you for all your help with my project to create a location history of objects: location, duration, etc. I set up a Location History Table and a new record is created each time an object moves. The portal located in the bottom the new location record shows the entire history of location moves for that object. The portal is sorted by "location begin date" (desending) so that the most recent location is in the first line of the portal.
In the Objects Table/layout, I created a portal from the Location History Table which just shows the first line of the portal. Thus, only the object's most recent/current location appears on the objects record layout. It has been working great. UNTIL, I needed to find all objects currently in a particular location. I used the LH portal located on the Objects layout to search the "Building Location" field. The result (and problem) is that EVERY object that was EVER in that location is included in the found set, which makes the "find" useless to me. I have been racking my brain for a solution and am afraid my very limited experience with FMP limits my ability to even imagine a solution. Your thoughts or ideas would be welcome. Thank you.
I see two ways to solve this issue: add information to the location history table; add the location information to the object table.
The portal points to the current and past locations, regardless whether one or multiple portal rows are visible. As you saw, searching it does not limit the search to the first row. You could add a "most recent location" field to the location history table and ensure that only the most recent location record has information in it (1/true or empty are good values). That way, your search in the portal is now "find all object in location X whose most recent location is true". Of course, you would need to clear the most recent location field of the previous location history record every time you move an object.
You could also add the current location information in the object table. This is a great example of selective duplication of data. This means that, when you move an object, you create a record in the location history table AND you modify the object's current location information with the new location information. You would then search for that location in the object table's fields, not the location history fields, when you want to find all objects in a particular location.
There are a lot of ways to handle this. Here is one:
From the objects layout, search in the location field in the portal to discover all objects that have been in that location. That is probably not every object, so you'll have a found set. Now you want to test the location in row 1 of the portal. If it's not the location you are searching for, omit the record.
You should be able to turn this into a script that looks a bit like this:
enter find mode [pause: on]
# enter the location to search for in the location field
# you'll manually have to press the continue button in the toolbar, or create a button on the layout which continues the script
set variable [$location ; location_portal::location ]
perform find
if [ Get( LastError) ]
# nothing found
show custom dialog ["nothing found"]
exit script
end if
loop
go to portal [ location_portal ]
go to portal row [1]
if [ location_portal::location = $location ]
set variable [ $counter ; $counter + 1 ]
end if
go to record [ next ; exit after last ]
end loop
# if $counter is greater than zero then we actually have records in that location
# so we'll start isolating them. We're currently on the last record
set variable [ $foundCount ; Get (FoundCount) ]
set variable [ $counter ; 0 ]
Loop
set variable [ $counter ; $counter + 1 ]
go to portal [ location_portal ]
go to portal row [1]
if [ location_portal::location = $location ]
go to record [ previous ; exit after last ]
else
omit record
end if
exit loop if [ $counter ≥ $foundCount ]
End Loop
Thanks Malcolm. The first option (most recent location field) could be managed. However, adding and removing “most recent location” indicators feels cumbersome and invites errors. My searches include not just an object’s location, but often several other parameters as well, resulting in several hundred objects with many locations.
I tried using the "filtered" option in the portal without success, likely because I don’t understand filters in portals, or my calculation is bad, or both. My thought was to filter out all records with a “location end date” leaving only records without a "location end date" -- since an object’s current location will not have an end date. I tried filtering with “IsEmpty ( TABLE LOCATION HISTORY::Location End Date )” This did not work.
“You could also add the current location information in the object table. This is a great example of selective duplication of data. “ I like the simplicity of this approach. To that end, I tried using 'lookups' based on the object’s unique ID to duplicate location information in the Object Table when a new location record in the LHT is created, but that was also unsuccessful.
Is there a script that would populate the corresponding location fields in the Object Table (there are 8 different location fields representing levels of specificity) when a new location record is created in the LHT? Thank you for your consideration and assistance, Malcolm.
Malcolm, unfortunately, all 1200 objects have been in most locations so they all come up when searching one location. I plan to create corresponding location fields in the Object Table which, using a calculation or script (I'm clueless about calculations and scripts) can populate those fields when a new location record is created in the Location History Table - negating the need for a LHT portal in the Object Table altogether. Thank you for taking time to propose a solution. Even when I don't understand 9/10ths of fmsoup's responses to my questions, I do discover a different approach to thinking about the problem.
This is a typical scenario for a field being denormalized.
Place an auto-enter calc in the parent that pulls the field from the child table sorted by most recent through the relationship. Then that field will always be just the one record that is at the top of the sort order but is located in the parent so has no other records to contend with.
If you want the last, a new child TO sorted in reverse chronological order, and repeat above for the same field but from the new TO
I'm going to try taking a little liberty with the logic here, and use:
"an object's current location will not have an end date"
to inspire a guess that the following may also be true:
"only current location records will have an empty end date".
Of course, logically, my conclusion need not be true -- it doesn't follow logic, rather, it follows a hunch.
If the hunch is true:
Instead of just searching in the portal for a particular value in the Building Location field, you could try augmenting that find criterion by also restricting the find to portal records where the End Date field is empty.
More specifically:
Use the same layout with the portal
Enter Find mode
Enter the desired value in the Building Location field in the portal
Enter the equals sign, i.e., '=' (but without quotes) into the End Date field in the same portal row
Perform the find
See if it returns the desired records
Note that the above requires no portal filter calculation to be in place.
If I have understood this correctly, and if my hunch is correct, then I believe that the above should help return what you are looking for.
That said, I might prefer other solutions in this thread over the portal searching approach because this approach of searching through the portal is likely to become less performant over time. I think it's a good approach to understand, and I'd reach for it in a heartbeat if it were just a one-off need to perform the find. But for a more long term approach, I'd have to weigh the ease of implementing this against the inconvenience of a potentially slow find.
HTH
Edit: I just re-read one of your replies and see that you are leaning to doing away with the portal. As such, what I mentioned above is unnecessary (though hopefully still educational). I like better the approach that you are looking at w.r.t. having a dedicated field (or fields) in the Object table.
Filtering a portal does not change a portal's data. Same for searching a portal. It only limits what is displayed in a portal.
You would need to create this script. If it were me, I would use the link between the location history and the object table when creating the location history record. That way, you could modify the related object record's location fields as you are creating the location history table.
Thanks to all for considering and offering approaches to my 'sorting in portal delema'. Because I had a pressing deadline with many, many reports to generate, I successfully utilized steve_ssh suggestion to search both location and "=" in the end date field. Thank you! That worked very well for my immidiate needs. I would like to write a calculation tied to the creation of new records in the LHT that automatically updates the location in the Object Table, but until then I've settled on the following approach: import location changes to the LHT from an excel doc. Then, with only the most recent location change records showing, import them into the Object Table. Not terribly cumbersome but I look forward to learning about parent and child tables, auto-enter calc etc. Again, thank you!
I think I've found a solution to updating object locations (while maintaining a history of the changes) that is so simple that even after several tests, I'm hesitant to memorialize it. Would someone please take a look at my solution and critique?
Solution:
With bdbd's suggestion in mind, I added the sort option to the Location History Table sorting by Unique ID then Location Begin Date (descending) so the most recent location change record is prioritized.
The Location History Table and Object Table are related via their Unique ID. In the Object Table layout I am using the location fields (Location Building, Location Room, Location Status, etc) from the LHT. Now, the most recent location change record appears in the Object Table layout location fields. And when I add a new location change record to the LHT, the most recent/current location is updated in the location fields in the Object Table layout.
Thoughts? Thank you.
