8 Replies Latest reply on Jun 12, 2014 11:07 AM by sjpockmire

    Need To Pull Info From Most Current Record in A Portal

    sjpockmire

      Title

      Need To Pull Info From Most Current Record in A Portal

      Post

           I'm really trying to learn about portals but after four hours, I'm really stuck. I appreciate  PhilModJunk's help and hope he sees this post. I have a list of inventory items in a house-museum. The location and value get updated every so often. I'd like to be able to print an accession sheet that details the item and then pull the most recent information from a Location & Value Table. The accession sheet is based on the Item Table using information from the LocationValue Table as well as InventoryID Table.  PhilModJunk gave me a tip on pulling the most recent value in a list, but I can't pull any text. I'm sure this is a simple thing but I just can't figure it out. Any help would really, really be appreciated.

      Screen_Shot_2014-06-11_at_4.41.35_PM.png

        • 1. Re: Need To Pull Info From Most Current Record in A Portal
          sjpockmire

               Here are the tables.

          • 2. Re: Need To Pull Info From Most Current Record in A Portal
            philmodjunk

                 Do you want to see data from the most recent location record on a layout based on the Item table?

                 Is there a date field in the Location table that records the date on which that item was either moved to that location or re-assessed to a new value?

            • 3. Re: Need To Pull Info From Most Current Record in A Portal
              sjpockmire

                   Hi, yes, I have image shots above - not sure if you can see them. In my first post, the accession sheet is to the right of the data entry layout. It would list everything in the Item Table pertaining to that particular item. At the bottom of the accession sheet, it would have all the information from the most recent inventory taken.

                   To the left is a screen shot of the data entry screen. I entered a bunch of different dates and weird amounts but it just isn't being pulled to the accession sheet.

              • 4. Re: Need To Pull Info From Most Current Record in A Portal
                philmodjunk

                     I can see the uploaded images, but that doesn't answer the crucial question: Is there a date field in the Location table that records the date on which that item was either moved to that location or re-assessed to a new value?

                • 5. Re: Need To Pull Info From Most Current Record in A Portal
                  sjpockmire

                       Thanks. In the Location Table, I have a _FK field that is a number (Inventory ID#)  but is actually the date - i.e. 121212 is 12/12/2012. The date that the inventory was taken is in another table, InventoryDate. I created this new table because I needed to list who cataloged the items and their contact information. 

                  • 6. Re: Need To Pull Info From Most Current Record in A Portal
                    sjpockmire

                         I took the accession sheet and used the Location Table but it isn't pulling from the most recent record. Any help on how to pull the information from the most recent records automatically? Say if I wanted to batch print a whole group?

                    • 7. Re: Need To Pull Info From Most Current Record in A Portal
                      philmodjunk

                           Sorry, but not only is that FK field a less than optimum design choice, it won't serve this purpose. You need a field in the location table that, for a given inventory item, has a different date in each record--the date that records when that item was moved to a different location or reassessed and a new value recorded.

                           Unless I am missing some detail here, not only will that FK field not be that date, but two inventory items might easily have been added to inventory on the same date and then they would have the same FK value and this would result in two records in inventory that would match to the same record or records in Location. That would be a recipe for disaster.

                           So I am looking for something like this:

                           ItemID     Description          Value           Location        Date
                           1             Urn                      $300          Collection X    5/31/2014
                           1             Urn                      $250          Collection X    6/1/2013
                           1             Urn                      $250          Collection A    3/3/2011

                           With data like this, I can tell that the first record (the first row) is the most recent record for ItemID #1 and I can set up relationships that will make it easy to refer to this specific record for Item ID = 1. I could also make this work if the records in Location have a field with an auto-entered serial number as I can just as easily refer to the related record ( a record where ItemID =1), with the largest serial number as I could the record with the most recent date.

                           But without some such field, we have a serious problem with your data. In a perfect world, the most recently recorded location record will be the last record in an unsorted relationship or an unsorted found set of records, but this is not a perfect world. You reported in your other thread that you accidentally deleted some of these records and had to import them from a back up file. Such imports, which make the imported records the "last" records, can alter the results you get when you look for the "last" record.

                           If you truly don't have a field that will serve for this in location and you are confident that imports haven't messed up your unsorted order, I suggest that you add an auto-entered serial number field to this table, pull up an unsorted found set of all location records on a location layout, and use Replace Field contents with the serial number option (and click the check box to update auto-enter options) to assign a serial number to all existing records. You can then sort a relationship to Location by this serial number in descending order to make the most recent location record the first related record and you can then access any field in this most recent record very easily.

                      • 8. Re: Need To Pull Info From Most Current Record in A Portal
                        sjpockmire

                             Hi, again, thank you. All my tables have a field with auto entered record numbers. I'll double check that when I re-imported the files that the record numbers are correct, otherwise I can renumber them. I do have everything that you asked but probably didn't explain it very well.

                        "You need a field in the location table that, for a given inventory item, has a different date in each record--the date that records when that item was moved to a different location or reassessed and a new value recorded." Yes, I have that. 

                        "I could also make this work if the records in Location have a field with an auto-entered serial number as I can just as easily refer to the related record ( a record where ItemID =1), with the largest serial number as I could the record with the most recent date." Yes, I have that.

                        I can print using a Go To Related Record button but was hoping I could batch print if necessary. 

                             Again, thank you so very much.