5 Replies Latest reply on Jan 27, 2015 8:14 AM by WBSimon

    Questions about database structure for inventories



      Questions about database structure for inventories


      I am currently working on a small project to setup a database to record information for our research. I have reviewed many sources of Filemaker information and tried many different types of relationships and layouts, but I can't find a system that works.

      Currently, we have field plants that need data recorded, and once we collect seed from those plants, we need to catalog and store those seeds. I am using barcodes to separate the accessions, but the barcodes i use in the field are not the same barcodes that are used for seed storage. So, two separate inventories that are linked. My problem is how to I achieve this. Portals? Relationships? etc.

      I have tried two tables. One for Plant Inventory and one for Seed Inventory with overlapping fields linked, but I find that a layout listing Seed Inventory information has blank fields because I haven't collected the seed yet. So, I will have 10 listings of blank boxes because I have 10 records listing Plant data.

      I've tried a joining table between the two inventories, but I find that fields don't like being empty upon data entry. It requires that I put data into every field that is in a relationship. Data that I don't have yet.

      So, could anyone out there give me a clue? Am I missing something simple?

      Thanks for checking this out.



        • 1. Re: Questions about database structure for inventories

          I am not sure if I understand it in the right way (also because English is not my native language).

          But I think you have to join these tables indeed. The only thing is, you need the another unique field to join them. (E.g. PlantNumber). If you use a unique identifier to join them, you can add records if you have the data from the Seed Inventory. You won't have empty fields or records then. (I assume the "Plant Inventory" is the Parent table).

          Hans Lijnbach 

          • 2. Re: Questions about database structure for inventories

            So one barcode identifies the plant? Or a specific plant for a specific "accession"? (Does accession mean identify the season the plant was harvested?

            And then the second barcode identifies the seeds harvested. Would that be the seeds harvested from a specific type of plant? A specific plant? or ???

            The details are crucial to what kind of data model then needs to be set up.


            What version of FileMaker are you using?

            Have you ever worked with SQL queries?

            Is this your first relational database design?

            • 3. Re: Questions about database structure for inventories


              Ok, I guess I should explain this better. First, this is my first database design, I have not worked with SQL queries before, and I am using Filemaker Pro. I think I have figured out a work around, but it's kinda weak considering how powerful Filemaker Pro can be. i have a unique identifier for each plant and each seed packet.

              Yes, One barcode for each plant. The barcode is tied to each pot. On the seed packets, the barcode tags won't fit, so I need to print them out. The seed packet barcode is for all the seeds that originate from one plant.

              I can assign a serial number to each entry into the database and a plant number as an identifier for each plant. The serial number is for the database and the plant number is for my record keeping (i.e. something that tells me about the origin of the plant etc in one glance). I can also assign a serial number to each packet and a seed packet number that is an identifier for me. The problem comes in when I use a layout to list information for the joint table. If I enter all the data from the field for this year, I have many blank fields in the seed listing. If I look into previous years of seed data, I will see 100 or so blank fields before I see any data that has been entered from previous year. For some reason, Filemaker pro lists fields that have no data for that specific record. If you look at the picture, you see blank boxes listed but two records. I've tried adding fields from two different tables on the same layout, but Filemaker Pro seems to not like that.

              My work around is to have a table and layouts specifically plants and a separate table and layouts for seeds. If I would like to look at them in a joint table, I will have many empty boxes for data that hasn't been entered yet.

              In research, I don't have every field to enter right away like someone who would put in an invoice. I have the sowing date of the seed now, but the transplant date will be a few weeks away. My goal is to have a record for one plant with all fields linked. One plant, one sowing date, one transplant date, multiple harvest dates, plant barcode, and seed packet barcode. I would like to look up one record and be able to see all fields which I can then follow back in time to see it's parents and so forth.

              I hope this explanation helps. Also, thanks for the help! Any suggestions help.


              • 4. Re: Questions about database structure for inventories

                Unless the seeds in a packet can come from more than one plant, there is no need for a join table.

                I am assuming that multiple packets of seed can be harvested from a single plant:


                Plants::__pkPlantID = SeedPackets::_fkPlantID

                Your Plant barcode can be __pkPlantID

                A seed packet barcode would be __pkSeedPacketID, a separate field in the SeedPackets table that auto-enters a serial number and which is not used as a match field in this relationship but which can be printed out for/on your seed packets and can be used to look up the Plant that produced the seeds in the packet.

                For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                • 5. Re: Questions about database structure for inventories

                  Once you harvest the seeds from the plant, is the plant destroyed? If you have one plant with one set of seed, you only need one table. I see no reason you cannot combine both tables into one unless you harvest separate sets of seed from one plant.