10 Replies Latest reply on Jun 30, 2015 7:23 AM by philmodjunk

    Chemical database



      Chemical database


      I am using filemaker Pro 13 to manage a molecular laboratory - I am not a developer. 

      I have a table for all the unique chemicals that are ordered.  I am looking for a system for tracking items as they are ordered and received.  I was thinking of using a 3 table system.

      So the first table "chem list" contains a record for each unique item that is ordered - the second table  "ordered" is a holding file for items that have been ordered - I was hoping to duplicate the record or some subset of fields from the first table - and add a order date and quantity ordered  - but I'm not sure which tools to use.

      then when the item arrived I would like to transfer the record out of the "ordered" table into a third table "chem inventory" where specific info like received date, lot number and expiration date fields could be added.

      The first and the third table already exist - but I would like to streamline the creation of the  table 3 - inventory records to avoid errors and save time.  what is the best way to set this up?

        • 1. Re: Chemical database

          From what you have described, I would see two tables.  Table 1 - WHAT could be ordered.  Table 2 is the Order.  It is not necessary to duplicate the items in Table 1 into Table 2.  A connection of a Key Field to link them is enough.  What you describe as Table 3 may really be Table 2, but with a STATUS field that is changed.  

          • 2. Re: Chemical database

            I need an intermediate table because the items do not necessarily arrive in the same order as they were placed and the third table assigns an inventory number that physically tracks the item in the lab, back ordered items can linger for weeks.

            • 3. Re: Chemical database

              You still might not need that third table. What you describe could simply be more fields in table 2 that are updated when the ordered item is received. You might add a third table with exactly two fields, An ID that links back to a specific record in table 2 and a inventory ID field that auto-enters unique ID's each time a new record is created in that third table. You might include a Qty field if it is possible to receive partial shipments of the same ordered chemical--such as ordering 1,000 grams of Silver Nitrate and getting two partial orders  of 500 grams in two different shipments.

              The important thing is to avoid copying data from one table to another except for the needed ID's that link your records in their defined relationships. WHen you get copies of the same data in multiple places, updating them--say to correct for a data entry error becomes difficult to do without introducing inconsistencies into your system. For example, you wouldn't want a record in table 2 to name the chemical Sulfurous Acid and the corresponding record in table 3 to name it as Sulfuric Acid. So you keep the name in exactly one place, Table 1 and link any records in your other tables back to 1 in order to refer to its name and any other details specific to the chemical.

              • 4. Re: Chemical database

                Sometimes I have such a hard time visualizing this stuff...like almost there, but not quite.

                Table 1 is unique to each chemical form, vendor and amount - because it also links to vendor specific safety information - its unique ID is a CHEM#.

                Table 3 is currently flat - and has a unique inventory ID  (INV#)  This field is auto assigned on creation.   this table tracks quality control (lot IDs, QC run info and expiration dates).  

                I pull my ordering information from Table 1 and enter on a web based university wide application, or a department specific order form.  There is no option to automate this, but that's ok.  

                I was imagining a button (script) for each record that would create a record in a sort of holding file - table 2 - when an order was initiated, you are correct it would need a quantity associated with it, and partial shipments are possible -

                I was thinking to use another button on table 2 for receiving items - this table would have inventory specific fields such as received and expiration date and lot information.  the button would "transfer" (delete in table 2 and create in table 3) when an items arrived - you are also correct - I was worried about typos at that point that would be difficult to quick fix.  I was thinking the receive script on table 2 would have a loop depending on the quantity that generated the proper number of records in the inventory table for items that are ordered say a dozen at a time.

                I think I see what you mean - in that if I created an "order ID" in the inventory table - the record would be created when the order was initiated, but assigned a "inventory ID" only when it arrived.  That could still be connected to the receive button - right?  

                would 2 tables make it more complicated when items are purchased in quantities greater than 1? I'm thinking there would be one record that would have to be "updated" to whatever number of records when the items were received.

                I really appreciate the input.  sorry the questions are so user based and not developed based. 


                • 5. Re: Chemical database

                  It would seem that you could link all records to a unique ID defined in Table 1.

                  But you may be oversimplifying things as you have indicated:

                  Table 1 is unique to each chemical form, vendor and amount - because it also links to vendor specific safety information - its unique ID is a CHEM#.

                  I assume that the "amount" is the unit price charged for that chemical.

                  But I would have one more table, a Chemicals table where each chemical is listed exactly once as I would assume that a given chemical can be ordered from a different vendor. A unique ID in this fourth table would uniquely identify each chemical in your inventory. Thus, if you order two identical concentraion solutions of Sulfuric Acid from two different vendors, you would use two different records in your table 1 to set up the order information, but both would link to the same record in this Chemicals table.

                  Your fear of typos directly addresses the recommendations we are sharing with you. The idea is to enter information exactly once and don't make copies of that information in other tables as that introduces the chance for a number of different errors. Instead, all references to a chemical link back to a single record in the Chemicals table--to identify the chemical and link back to the table 1 record to identify the vendor and supply any vendor supplied info such as the government mandated safe handling information required of each such vendor.

                  • 6. Re: Chemical database

                    Table 1 is that unique place for each chemical (vendor, cat#, size) - amount relates to size - I haven't added that cost component in yet - that currently lives in an excel spreadsheet, but table 1 is also the "shopping cart" for ordering (this way no one should be able to order a chemical that we don't already have all the safety information on file in our security office plus other info e.g. formula weights for reagent prep...).  

                    I will try the two table method and see how far I get.  

                    • 7. Re: Chemical database

                      You process flow is similar to a basic inventory ordering system.  I am just throwing this out there. Maybe some elements will help.  

                      Table 1 is the Chemicals that you are tracking.  

                      Table 2 is Summary Record, i.e. the purchase order.  Date ordered, supplier, total. Status (ordered, back ordered, received)
                         A portal to order items pulling data as Phil mentioned above and then entering how much is ordered.
                         A portal to see received.  In a different portal enter the received quantities. (Different fields same records)

                      Table 3 is the detail records of what is ordered.  It can be viewed as a portal record to Table 2 or a portal record to Table 1.

                      You could have container fields to hold PDFs of the MSDS forms.  

                      • 8. Re: Chemical database

                        I repeat that it doesn't look like table 1 can be used as you describe. At least when I taught high school chemistry, I had several available suppliers that can supply the same chemicals. You really need one table specific to your vendor-chemical pairs where duplicate chemicals-of any number of sizes can be listed and another table where each chemical, regardless of size has exactly one record.

                        • 9. Re: Chemical database

                          Phil - Table one will have a unique entry for every vendor and size - based on safety requirements - the SDS sheets are specific to the vendor - so even if I ever do order a chemical from two different vendors - they will have a unique entry in the chemical table.  (the table is specifically set up for this system - a unique chemical - for our purposes -  is not based on its formula - ).  But that is not a major issue one way or the other (I don't think).

                          Can I confirm what you are telling me then?  3 tables - an order originates in table 1, creates new records in table 3, and is tracked and received using a portal in table 2?  that would be using an orderID field in table 3 - and creating the inventoryID when the sample arrives, not when the record in table 3 is created (as happens now).

                          Is there a way to auto enter consecutive numbers in a field only when some criteria (e.g. entry in a received date field) is met? I don't like the idea on manually entering the inventoryID - 

                          • 10. Re: Chemical database

                            Yes, you need your vendor specific table. I am recommending that you also add an additional table with one record per chemical that would link to your original table. This is to avoid having to enter the same information over and over into different records of your original table in order to purchase a particular chemical from more than one vendor.

                            That makes 4 tables, not 3.


                            A field in Inventory can be defined to auto-enter a serial number each time you create a new record in that table. That will create the sequential order you specify, but all you really need is a unique value.