5 Replies Latest reply on Apr 1, 2013 1:38 PM by philmodjunk

    Item Tracking Over Time



      Item Tracking Over Time



           Hello World! ^.^

           I'm hoping someone can guide me a little here in the correct direction. I have a project I'm doing with FIlemaker Pro for work (both to help me learn more about Filemaker as I am still very much a noob at it, and to help make life a little easier at work.). I am trying to develop a Database that can Track Items like Tools, and Equipment over a period of Time by User. When a User Signs-Out the Items, any User should be able to Sign it back In. On top of that, as each User Signs-Out an Item, that Item needs to be Unavailable until it is Signed back In.


           Currently I have six (6) Tables that I believe are set up completely incorrect (I will Upload a screen shot of the Relationships Graph for a better view):


           and then as a secondary path



           I managed to be able to perform Sign-Outs and associate Users with Items and create a Timestamps, but I ran into a road block that I don't know how to solve because I don't know how to append a "Sign-In" Timestamp to the Items that were Signed-Out or track who Signed the Items back In.


           I know it's a lot, but I figured I'd go with a crazy project like this to really learn Filemaker. Any and all help/guidance would be very much appreciated. ^.^


        • 1. Re: Item Tracking Over Time

               Are all the "boxes" in your screen shot separate tables--each has a separate row on the tables tab or do some of these Tutorial: What are Table Occurrences? share the same data source table? If some have the same source table, which ones?

               Why do you need separate table occurrences for signing items in and out? Can't one table occurence with the same layout be used for both?

          • 2. Re: Item Tracking Over Time

                 Hello Phil,

                 I actually was evaluating that myself and I'm going to change it around a bit. The bottem "box" intitled "Transactions_Line_Items_In" is just an occurrences that shares data from from one table, and the bottem "box" intitled "Transactions_Sign_In" utilized a completely seperate table. I was thing of Merging my "Transactions_Sign_In" and "Transactions_Sign_Out" tables into just one table (this way I can have each User specify the 'type' of transaction they are doing in one table), and only have one occurance of "Transaction_Line_Items".


                 What are your thoughts?

            • 3. Re: Item Tracking Over Time

                   Well that's why I asked the question. I'd use a single table for signing stuff in and out. You can have one date, time or timestamp field for when it is signed out and a second such field (or set of fields for signing the item back in.

                   I don't think you'll need a separate table nor an additional table occurrence to make that happen.

                   PS. check your starter solutions. FileMaker 11 has a lending library starter solution you may want to look at for ideas. And FileMaker 12 has an Assets starter solution, that I think has similar features.

              • 4. Re: Item Tracking Over Time


                     Hello Phil,


                     Thank you again for the advice! I utilized what you told me as-well-as some other items I learned about and I was finally successful in what I was trying to accomplish. Now I have another piece of the puzzle that perhaps you might be able to help me out with.


                     As each UserSigns-Out a piece of  Equipment, I would like a conditional logic statement to check to see if the piece of Equipment has a status of "Signed-In", or "Signed-Out". If it IS "Signed-In", then they can proceed to sign it out (aka make a Sign-Out record) and change the piece of Equipment's status to "Signed-Out" (basically prevent Users from being about to Sign-OutEquipment if it is already Signed-Out). And the I would like to apply the same logic but in reverse for Signing-In Equipment.


                     Below I have provided a screenshot of my database to veiw it's structure:

                • 5. Re: Item Tracking Over Time

                       You still have stuff here that I don't think that you need.

                       First, I suggest a change in the match fields used:

                       Transactions::__pkTransactionsID = Transactions_line_Items::_fkTransactionsID

                       It may seem unlikely, but unless you only a single user database, it's possible that two users might just start signing out/in equipment at the exact same second in time and then you have a problem easily avoided if you use a serial number field in Transactions instead. You can remove the Date Time Out field from the line items table. Any time you need that data from the context of the line items table, you can refer to the time out field in transactions.

                       A portal to line items placed on the transactions layout can also be set to filter out all checked in items so that only remaining items still "out" appear in the portal and this does not require adding another relationship to do so.

                       From what I see here, each item has a unique asset number, correct? If so, I can see two ways to determine if an item is "in" or "out":

                       Script triggers on fields in the line items portal can be used to change a status field in Equipment to mark it as "in" or "out".If you use this method, be careful to not permit deleting portal row records from the line items portal except through a script that first updates the status field in Equipment before deleting the record.

                       Or you can set up a relationship between equipment and Transactions_Line_Items that only matches if the Date_Time_In field is empty. Define a calculation field, cOutFlag in Transactions_Line_Items as IsEmpty ( Date_TIme_In ).  Be sure to clear the "Do not evaluate if all referenced fields are empty" check box. Define constOne in Equipment as a calculation with just the number 1 and a result type as number.

                       Then you can define this relationship:

                       equipment::Equipment_Asset_Numberpk = Transactions_Line_Items|Out::Equipment_Asset_Numberfk AND
                       equipemtn::constOne = Transactions_Line_Items|Out::cOutFlag

                       Then from the context of Transactions_Line_Items, Transactions_Line_Items|Out::Equipment_Asset_Numberfk will be empty if the item is currently checked in.