You can add a second pair of match fields to limit the records shown to those that are "current".
Artifact::ArtifactID = Location::ArtifactID AND
Artifact::Current = Location::constOne
Where Current is your field with the boolean value of 1 if the artifact is current and constOne is a calculation that returns the number 1.
I see how this could work, but I'm not sure if it will do what I need it to.
To explain further: artifacts are sometimes moved to off-storage locations, or sometimes they are moved from one box to another, and that relocation needs to be tracked in FM. When looking at an Artifact record, the current location is shown, but it would be great to also have a portal that lists the previous locations. If my Artifact table is connected to the Location table based on both of those parameters being true, how can I show the Artifact's previous locations?
I'm also unsure as to why I would need a calculation field, but perhaps I'm mis-understanding something.
My understanding from your original post was that you wanted to see the location of every "current" artifact. I see now that I had that backwards. You want to see every artifacts "current" location. So the match fields would need to be swapped:
Artifact::ArtifactID = Location::ArtifactID AND
Artifact::constOne = Location::Current
To Show all locations, you'd use two relationships by setting up two table occurrences of Location. Link one by just ArtifactID and the other as as shown above and you have one relationship for showing the current location and one for showing all locations.
But there's a different approach that might be easier. Since an artifact can only have one current location. Just add a new occurrence of Location and link it like this:
Artifact::_fkLocationID = Location|Current::__pkLocationID
You can then refer to Location|Current to refer to the artifacts's current location. _fkLocationID can be formatted as a drop down list for selecting the current location for an object. Also, a script for changing an artifact's current location can add a new record to Location at the same time that it puts the needed ID number in _fkLocationID.
Yes, that is exactly what I'm going for. Both of your solutions seem great, but they've given me an idea for something else:
What if instead of matching Artifact::constOne = Location::Current, if I had Artifact::constNull = Location::MoveDate, so that if the Artifact has not yet been moved from a box (MoveDate field is null), then it is still the current box. This way I can track when the object was moved and can list location history in a portal sorted by date. Could this work? Can null=null, would that return true?
Unfortunately empty match fields will fail to match to records with empty match fields. But you could use a calculation field on both sides such as
constOne on one side and IsEmpty ( DateField ) on the other.
You can also sort a portal so that the record with an empty date field is the first record in the portal row and with a bit of calculation field creativity, it can be sorted so that the record with the empty field is listed first followed by past locations in descending order by date so that you see your locations from most recent to least recent in your portal.
Great! I will try to come up with something based on your suggestions. Thanks for your help. Very much appreciated.
I got it to work and I'm very happy with it. To show the history I have a portal with one row (and a scrollbar) showing two fields: a calculation field and DateMoved. The calculation field checks to see if DateMoved is null, and if so, returns "This print has no location history" otherwise it lists the storage location. I like it and it looks great, but I've encountered something I wasn't expecting.
In my previous set-up, the Artifact table held all the location information (StorageRoom, Shelf, BoxLetter, Box#). Whenever I wanted to change the location for the print I could do so on the Artifacts layout. Now, if I change these values, it changes the value of the fields in the Location table, not the Artifact table.
Any way to still allow the location to be changed from the Artifacts layout by directly modifying the fields?
Hope that question makes sense.
For listing the "history" of everywhere an artifact was stored, you probably should modify your tables/relationships to be:
Artifacts::__pkArtifactID = Artifact_Location::_fkArtifactID
Locations::__pkLocationID = Artifact_Location::_fkLocationID
Each time you move an artifact to a new location, you create a new record in Artifact_Location. A record in Location marks an actual physical location that could store any number of different artifacts at different times.
Okay, this makes sense, because it's a many to many relationship, which I see now. I have a similar set-up for the Exhibitions History, so I should be fine with changing it up.
In my Exhibitions History join table, I only have the two fields, but with this set-up I would need the DateModified field to go in my "Artifact_Location" join table, correct?
Sounds right to me.