FM beginner (sort of). 20 yrs ago without any computer experience, I created a FM database that has served me well. 20 years later I need to add a feature and don't know how to approach the problem. I'm excited to have found this site and look forward to learning from you all.
The database has 1000 records for 1000 objects of which a portion move to different locations monthly - this could be 1 or 500 objects moving to a new location - it varies each month. Location is broken down by "Building", "Room" "Location status" which is either "On View" or "Storage" and lastly, the "location date change". I import the info monthly from an excel spreadsheet - except for the date change which I do as a single mass update with all changes recorded as occuring on the last day of the month. No problems so far.
NOW I need to keep an ongoing record of the location changes for each object and determine the duration that the object is in each location. My brain tells me to create a table for Locations which would get a new record for each time an object changed locations. Then I would have a portal in the basic record that showed the history of location changes. However, making a new record for each object each time the object moved seems cumbersome. I haven't even considered how to set up for the duration at each location. Any ideas? Thank you.
Making a new record is the simplest and easiest way to keep track of your objects.
- Make a new record for each time the object is moved. That records it's location.
- Record the date and time of the move in a timestamp field.
- When the object is next moved, calculate the duration by subtracting the date and time stored in the timestamp field from the current date and time. Put that duration into the old record.
- For objects that are still in-place you can have an unstored calculation field which calculates the duration an object has been in place.
This is a requirement of several of my data management solutions. One example is a scientific laboratory setting, where specimens are checked-out, assigned to analysts, transferred to different departments, etc.; another is an inventory management system which includes item sales as well as consignments (multiple in and out movements to clients all over the world); a third example is a Company Asset management system where nearly every conceivable tool, computer, stapler, desk is assigned to staff and locations, but which may change at any time.
In all of the above cases there is a LocationHistory table used and typically items are scanned (barcode, but could be manually entered or just a button on the record layout to call the transfer script, etc.) into a global field (one or many items). Company Assets are typically transferred from the individual asset record (e.g. Assigned to:...), where as inventory or lab specimens are typically done in scanned batches (e.g. 15 different specimens transferred to LA-ICP-MS, assigned to one scientist).
Finally, a script collects the necessary variables of item ID, destination, by whom, why, IP Address and Persistent ID (for security), etc. Then we simply open a utility window (off screen, no fields, form view only) based on the LocationHistory table and loop through the list of IDs scanned into the global field mentioned above. We reset the ITEM ID $variable for each iteration of the loop and simply have the script step of New Record/Request.
The LocationHistory table is setup so that all (nearly all) fields are auto-entered to each of the variables created above: $itemUUID, $destination, $reason, etc. There are date and timestamp fields usually, and we normally have an "isVoid" field so we can void a history record if needed.
The LocationHIstory table is related to the Inventory table or the Assets table or LabSubmissionLIneItems (not real name) table via the item's UUID set as a foreign key in the history table.
Back on the Inventory or Assets or LabSubmission record is a means to view the history of each item via a portal to the LocationHistory table (usually on a Slide Control Panel or similar). Any voided history records are omitted from view on the History panel.
I blathered on here, so if any of this is interesting and if you need more/better guidance or a sample file, please let me know. I'll try to help.
I didn't address this, but @Malcolm offered a method. If you need daily (or other periodic) duration status updates, one can script any of many options such as a report showing all items due to be moved; a simple find based on due date and show in list view; a script trigger OnRecordLoad to inform you that the specific item is due to move; a maintenance script to run at any interval that check the entire inventory to due dates, etc.
Thank you Malcolm, thank you Daleallyn. Even a quick reading of your suggestions is helpful, bringing to mind further questions. But not today -- I'm out with pneumonia but wanted to acknowledge your generous responses to my query. I look forward to revisiting when I'm better.
Wish you get well soon.
I hope you feel better soon, @Lovezinnias .
Thanks again, your ideas were very helpful. As suggested, created a Location History Table. Location changes are imported from an excel doc (outside source) into an objects record including a time stamp. Then a new record is created in the LHT whenever an object is moved; a LH portal lists the history of an objects movement: date of move, and time at each location using Get (current date) - time stamp.
There is also a "Location Status" which has 3 options: On view, On loan, Storage. I would like a field that is the sum of days an object is On View and On loan. Calculations are new to me and don't know if this is possible or where to begin. Thoughts? Also, if there are tutorials on how to build a calculation or script, that you'd recommend, it would be appreciated.
Check the Claris resources page. They produce training materials.
Thank you for all your help with my project to create a location history of objects: location, duration, etc. I set up a Location History Table and a new record is created each time an object moves. The portal located in the bottom the new location record shows the entire history of location moves for that object. The portal is sorted by "location begin date" (desending) so that the most recent location is in the first line of the portal.
In the Objects Table/layout, I created a portal from the Location History Table which just shows the first line of the portal. Thus, only the object's most recent/current location appears on the objects record layout. It has been working great. UNTIL, I needed to find all objects currently in a particular location. I used the LH portal located on the Objects layout to search the "Building Location" field. The result (and problem) is that EVERY object that was EVER in that location is included in the found set, which makes the "find" useless to me. I have been racking my brain for a solution and am afraid my very limited experience with FMP limits my ability to even imagine a solution. Your thoughts or ideas would be welcome. Thank you.
I see two ways to solve this issue: add information to the location history table; add the location information to the object table.
The portal points to the current and past locations, regardless whether one or multiple portal rows are visible. As you saw, searching it does not limit the search to the first row. You could add a "most recent location" field to the location history table and ensure that only the most recent location record has information in it (1/true or empty are good values). That way, your search in the portal is now "find all object in location X whose most recent location is true". Of course, you would need to clear the most recent location field of the previous location history record every time you move an object.
You could also add the current location information in the object table. This is a great example of selective duplication of data. This means that, when you move an object, you create a record in the location history table AND you modify the object's current location information with the new location information. You would then search for that location in the object table's fields, not the location history fields, when you want to find all objects in a particular location.
Hope this helps.