6 Replies Latest reply on Jul 16, 2010 8:35 AM by philmodjunk

    New User: Structure Question...

    ShawnAmann

      Title

      New User: Structure Question...

      Post

      Hello. Fist post. Just learning how to work with filemaker by taking some tutorials on VTC this past month. Very neat stuff and Im excited to learn it. I am a Biochemist so this is new for me...

      That being said I am making a database to track inventory in my lab. As reagent comes it needs to be tracked, as well as when it is used in testing. Reagent can be uniquely identified by its barcode. There can however be many different lot numbers of each type of reagent. 

      For state regulations I need to have a record of when reagents arrive, their lot and expiration dates. I also need to be document when reagents are used and by whom. So for example 100 kits of Reagent A come in. Lot # 124 on 7/1/10. On 7/2/10 User XX uses one kit of Reagent A, lot #124. 99 kits of Reagent A, lot 124 remain. 

      I made an INVENTORY_LINES table, that is fed from an INVENTORY_ENTRY table. (There is also an INVENTORY_DEPLETION table for when stuff is used, havent gotten as far as setting this up yet) I did this so that the ENTRY table can create the records in the LINES table and the entries couldn't be removed or altered, and they could be reported on so the inspector could see dates of arrivals, usage and to keep running totals of the reagent. 

      So my basic question is, does this make sense? Or am I using 5 tables for something that one could accomplish? 


      *Extra Info*
      (The REAGENT table is there to provide the corresponding Reagent Name and Manufacturer for when a user would scan the barcode. The DATE LOOKUP table occurrence is there to auto enter an expiration date when a user types in a Lot# for a reagent that has been previously entered into the database.) 

      screen-capture.png

        • 1. Re: New User: Structure Question...
          ShawnAmann

          So my thinking now is that the Entry and Deplete tables arent needed. Just have and Entry and Delete Layout. Then based on what layout the user is using, populate a status field in Inventory Lines and use that for calculations and summaries....

          • 2. Re: New User: Structure Question...
            philmodjunk

            You might consider a "ledger" approach for logging reagents into inventory and then also logging their depletion. Think of receiving reagent shipments as "deposits" and logging consumption as "withdrawels".

            You might have these fields to track each inventory change:

            kf_ItemID
            Date
            Description
            Qty Received
            Qty Depleted

            cBalance: Qty Received - Qty Depleted

            sBalance: Summary, Running Total of cBal; restart totals, group by ItemID

            With this approach you make a new entry each time you receive or use a reagent and the summary field, sBalance will compute the remaining quantity for you. Put this in a list view layout sorted by ItemID and you can show how much reagent you have on hand and also see how it is being used.

            kf_ItemID should link to a separate table where you record each lot of reagent and a primary key serial number links it to kf_ItemID in this field.

            • 3. Re: New User: Structure Question...
              ShawnAmann

              Thank you for your reply. I am chewing on your answer. 

              The ledger idea makes sense. I am using one table now called INVENTORY_Lines to mark items entered or received, although altered in method of entry as the field Received or depleted is calculated depending on what layout they are using as to avoid them entering reagent, when they should be removing it, etc. 

              What I am now working on is a way to get totals. I will get summaries based on Reagent Name, as well as sub totals of each lot. This should be pretty straight forward. 

              Also some kind of warning of when they remove a lot #, and there is something still in stock with a closely approaching expiration date compared to what they are trying to use....

              • 4. Re: New User: Structure Question...
                philmodjunk

                To get summaries based on reagent name, you can have running total summaries and use the group by option to specify the reagent name. You can then sort your entries first by reagent name and then by Lot# to group your layout changes.

                • 5. Re: New User: Structure Question...
                  ShawnAmann

                  THank you for your help. I was able to make the entry work how I needed it to. Works great when I sort the records on my list view to have all the lot#s grouped together. 

                  Followup Question: How do I get the list view layout to automatically sort each time. Is this something I would have to script along with a onlayout enter trigger? 

                  • 6. Re: New User: Structure Question...
                    philmodjunk

                    That's probably the simplest to way to set it up.