6 Replies Latest reply on Apr 29, 2015 4:20 AM by sjpockmire

    Inventory Listing by Current Location



      Inventory Listing by Current Location


      Hi, I am stuck again and hopefully someone can help. I have an inventory file. It has three tables: Item, Location, and Picture. There is only one item but the locations change over the years. I want to print a list containing the picture and details about the item with the current location (see screen shot). I have also created fields with:

      Last (Location) sorted descending order in the relationship with matching Item ID
      Last (Date) sorted descending order in the relationship with matching Item ID

      My layout is Item

      Find: Shaw Parlor in LastLocationCalc (which is the Last (Location)), and
      Find: 3/23/15 in LastDateCalc (which is the Last (Date))

      However this doesn't work. It brings in items that used to be in the parlor in 5/16/2000 and now in a new location. In this screen shot, this chair is in a new location.

      Any help would really be appreciated.


        • 1. Re: Inventory Listing by Current Location

          When an item is moved from one location to another, what changes do you make to your data? Do you create a new record in Item or a new record in location?

          I think you need a third table set up like this.


          Items::__pkItemID = Item_Location::_fkItemID
          Locations::__pkLocationID = Item_Location::_fkLocationID

          You can place a portal to Item_Location on the Items layout to list and select  Locations records for each given Items record. Fields from Locations can be included in the Portal to show additional info about each selected Locations record and the _fkLocationID field can be set up with a value list for selecting Locations records by their ID field.

          Moving an item to a new location is then a matter of creating a new record in Item_location in order to pair that item up with a different location record. This assumes that you have multiple items in the same location.

          • 2. Re: Inventory Listing by Current Location

            Hi Phil, here is what I have. I think I have everything you mentioned. But as you can see, it is pulling the chair from the first and second inventory. Not the most current one on 3/23/15.

            • 3. Re: Inventory Listing by Current Location

              The Location Last and Location Date Last use the calculation of Last (Location) in text and Last (Date) in date are sorted in descending order.

              • 4. Re: Inventory Listing by Current Location

                I solved the problem by using a combination mixture of "inventory last" and "current inventory." It worked on my data entry layout so I just made adjustments in a list layout.

                • 5. Re: Inventory Listing by Current Location

                  I do not see in your data model the set up that I recommended. I'm not sure why you have a table named "items" and one named "inventory" as in many systems, that would be two names for the same table...

                  But as long as it's working for you...

                  • 6. Re: Inventory Listing by Current Location

                    Hi Phil, Thanks but I didn't comprehend your last post but I think I have what you suggested. 

                    I have 3 tables -


                    There is only one item. The location can change each time an inventory process is done and my problem has been to pull the most recent location and date with the item. In the location table, I have Last (Location) and Last (Date). In the relationship I have the FK_Item in the Location table match with the PK_Item in the Item table with a descending sort on Last (Location).

                    When I went to my list and I did a SEARCH for a house and room using these fields in the Location relationship - LastLocation and LastDate. It didn't print the most current information.

                    What did work was to do THE SEARCH in the regular Location and regular Date field but had the information come from the LastLocation and LastDate field. This floored me that it worked but it does.

                    Now I just need to comprehend what you said about exporting those container fields in an earlier post. I am completely lost on that one but did do it manually.

                    Thanks again for all your help.