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.
2 posts were split to a new topic: How can I search a portal and only find records in the first or last row?