10 Replies Latest reply on Oct 31, 2013 3:49 PM by philmodjunk

    filter portal prior to version 11

    RyanBuckley

      Title

      filter portal prior to version 11

      Post

           I am trying to implement a Location history feature to an existing database of artworks. I'm working with the following set-up:

           Artifact------<Location

           Artifact::ArtifactID = Location::ArtifactID

           In the Locations table, I am thinking of adding a field "current" (binary value) or use datestamps to determine the current location.

           BUT I currently have a Location layout that gives general information about the location and has a portal listing the artworks that are in that location. My question is, if my changes to Locations means I will now be tracking previous as well as current Artworks, how can I retain the feature of viewing only the current works in a particular location?

           (My mind is still stuck in SQL thinking. I would love to select * where current = '1'...but I know this isn't the case with FMP and I'm trying to adapt to this change)

           Thanks in advance.

        • 1. Re: filter portal prior to version 11
          philmodjunk

               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.

          • 2. Re: filter portal prior to version 11
            RyanBuckley

                 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.

            • 3. Re: filter portal prior to version 11
              philmodjunk

                   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.

              • 4. Re: filter portal prior to version 11
                RyanBuckley

                     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?

                • 5. Re: filter portal prior to version 11
                  philmodjunk

                       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.

                  • 6. Re: filter portal prior to version 11
                    RyanBuckley

                         Great! I will try to come up with something based on your suggestions. Thanks for your help. Very much appreciated.

                    • 7. Re: filter portal prior to version 11
                      RyanBuckley

                           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.

                      • 8. Re: filter portal prior to version 11
                        philmodjunk

                             For listing the "history" of everywhere an artifact was stored, you probably should modify your tables/relationships to be:

                             Artifacts----<Artifact_Location>------Locations

                             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.

                        • 9. Re: filter portal prior to version 11
                          RyanBuckley

                               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?

                          • 10. Re: filter portal prior to version 11
                            philmodjunk

                                 Sounds right to me.