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.
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.
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.
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.
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...
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.