1 2 Previous Next 15 Replies Latest reply on May 17, 2012 5:09 PM by Willz

    Relationship Link

    Willz

      Title

      Relationship Link

      Post

      Having a strugle with proper relationship link for these two tables - see screenshot from simplified relationship chart -

      Table A (Matrix_Master 2) has data of 6 different machines with product, and material combinations.

      Table B (M_OmniBeam) builds records of operation time for each product/machine production.

      I have primary key field in Table A for each instance with ID number populated.

      Not sure what to do with the Foreign key field I put in Table B and how it populates with the correct ID to match Table A.

      Not sure the correct relationship link.

      Thank You for your help!

      Screen_shot_2012-05-16_at_11.17.17_PM.png

        • 1. Re: Relationship Link
          GuyStevens

          I'm assuming that your "Matrix_Master 2" is a sort of table that holds machine information, and that your "M_Omnibeam" is the table you will be filling with data linked to a certain machine.

          You just make a relationship between

          Matrix_Master 2::_pk_MatrixMaster_ID-------------M_OmniBeam::_fk_MatrixMaster_ID

          Then in a layout based on your "M_OmniBeam" table. You set the "_fk_MatrixMaster_ID" as a dropdown value list that displays the Machines from the "Matrix_Master" table.

          Just make sure that in the settings of the value list your first field is the "Matrix_Master 2::_pk_MatrixMaster_ID"

          You can set the second field as the ProductName if you want, and you can even choose to show only the second field.

          If you select a machine from your dropdown the Primary Key of the Matrix Master table will be set in the Foreign Key field in the Omnibeam table.

          • 2. Re: Relationship Link
            Willz

            Yes, You're correct - "Matrix_Master 2" is machine information of 6 different machines.   "_M_Omnibeam" is table for data about one of the machines.

            I have the pk and fk fields exactly as you say.

            Your suggestion:
             "Then in a layout based on your "M_OmniBeam" table. You set the "_fk_MatrixMaster_ID" as a dropdown value list that displays the Machines from the "Matrix_Master" table."

            in my layout based on "M_OmniBeam" table
            I set the _fk_MatrixMaster_ID field as you suggest, it has dropdown value list
            1st field is _pk_MatrixMaster_ID
            2nd field is machine name showing only 2nd field.

            Still not getting other fields in the OmniBeam layout to populate with the data in MatrixMaster.

            See Screen shot

            How does "_fk_MatrixMaster_ID" populate itself with the "_pk_MatrixMaster_ID" automatically?

            • 3. Re: Relationship Link
              GuyStevens

              Ok, I think I'm seing the problem.

              You have a lot of double fields.

              You have the same fields in the OmniBeam table as in the MatrixMaster table. That' not neccesary.

              The FK Field in the Omni beam table will populate itself with the PK of the Matrix Master table.

              But the trick is to put fields from the Matrix Master table on the layout based on the OmniBeam table.

              That way you only have your product information stored in one table (MatrixMaster) and you just display it on a layout based on a different table because in that table you set the PK field of the MatrixMaster table in your FK field of the Omnibeam table.

              The trick with relational database design is to store as little date as possible. You store all your product information once in one table and you don't duplicate it.

              What you do is set it's PK in another table and make a relationship to relate those tables.

              So all the fields you already have in the "MatrixMaster" table need to be removed from the OmniBeam table.

              • 4. Re: Relationship Link
                Willz

                Re: the double fields. All the data is in matrix - no data is in Omni, Omni is just a timner and work FORM for the operator to use. When the operator selects a product in the dropdown of omni it has to draw data form matrix to show production details about that product being produced on the omni machine. 

                I added as you suggested to Omni - "_fk_MatrixMaster_ID" as a dropdown value list that displays the Machines from the "Matrix_Master" table.  The second field is now Products - so this works good showing the Matrix ID and the product...but other fields still not populated.

                I added a join table between matrix and omni seeing that the link fork was showing many to many. that seems ok the tables are related.

                Last remaing problem  It finds the first instance (row) of the product/machine in matrix instead of the row with the correct product/machine data.

                matrix has all 117 products 6 times for the 6 different machines.

                • 5. Re: Relationship Link
                  GuyStevens

                  Isn't there a problem keeping both machines and products in your matrix table?

                  Wouldn't it be smarter to make=

                  - A machines table with 6 records
                  - A products table with 117 products
                  - and your Omnibeam table.

                  Because

                  matrix has all 117 products 6 times for the 6 different machines.

                  Doesn't sound like a good idea.

                  I also don't see why you would put a join table between these two tables.

                  Now I don't really understand the large picture of your database, but maybe we can come up with a better structure.

                  I'll make you a quick little example for getting your machine info in your omnibeam table.

                  • 6. Re: Relationship Link
                    GuyStevens

                    Also your timers could be set in a different table linked to your Omnibeam table so you can have as many or as few timers as you want.

                    • 7. Re: Relationship Link
                      GuyStevens

                      Another option would be to add a relationship between the "Products" of the Omniblue and MatrixMaster tables.

                      So there are two relationships.

                      That way the Omniblue only sees the records in the MatrixMaster table if

                      - The ID of the machine is the same
                      AND
                      - The product is the same.

                       

                      EDIT/

                      But I do still think it's a bad idea to have so many repetitions in your table.

                      • 9. Re: Relationship Link
                        Willz

                        I had already seperated matrix table into 6 tables - one for each machine

                        Like you said it was too much to have them all in one.

                        Now each machine has its own matrix source

                        The example machine we've been talking about, OMNI represents what I need to do for all 6 machines.

                        See ERD

                        • 10. Re: Relationship Link
                          Willz

                          the large picture
                          Operators are measuring machine run times.  Example: Using the OMNI we've been discussing, It is a layout with time start and stop buttons.  This Omni timer layout also tells the operator some detail about what they are producing, like product and particular info to run the machine for this product.  all the particular (unchanging) info is stored in the table we've been discussing called Matrix. 

                          This is Intended to help operator with info needed to operate and also to build a db of times it takes to run a product.  Omni and the 5 other machine layouts exactly like Omni will accumulate a lot of records over time to show us an average run time for product and machine.

                          So when operator goes to Omni clicks "run Product" a new record is created.
                          Omni looks up data from Matrix to fill in the details like: file Version, Material Cut, etc.

                          Its not populating the fields with the data from matrix.

                          • 11. Re: Relationship Link
                            GuyStevens

                            Wow that looks really funky.

                            I still don't know enough about your Database or what you would like to do, but it looks to me you need a little structure change.

                            You shouldn't have to do the same thing 6 times over for every machine. What if a new machines is bought?

                            It should just be a matter of adding a machine in the Machines table.

                            Could you tell me a little of the types on date you are working with and then I can suggest a structure that works a little better.

                            For instance:

                            You have Products. That would require a Product Table:
                            ProductId
                            ProductName
                            Price??
                            ...

                            You have 6 Machines, so there is a Machines Table:
                            MachineId
                            MachineName
                            ...

                            Then you have jobs (I would guess.)
                            A certain machines makes a certain product.

                            So a table Jobs:
                            JobId
                            ProductIdFk
                            MachineIdFk
                            Date

                            Or it could be that every machine does multiple jobs on a day.
                            Then you would use two tables:

                            TableJobs
                            JobId
                            Date

                            With a details table: TableJobDetails:
                            JobDetailsId
                            JodIdFk
                            MachineIdFk
                            ProductIdFk
                            AmountMade
                            ...

                            Let me know a little more and I might be able to make a little example.

                            • 12. Re: Relationship Link
                              GuyStevens

                              Whoops, I missed your previous post. I'll read it now.

                              • 13. Re: Relationship Link
                                GuyStevens

                                Its not populating the fields with the data from matrix.

                                Well put all the products in a products table and then you'll be able to show product details on your onmibeam layout.

                                I'll make you a little example.

                                • 14. Re: Relationship Link
                                  GuyStevens

                                  Try and have a look at my example file to see if something clicks.

                                  http://dl.dropbox.com/u/18099008/Demo_Files_FMP12/OmniBeam.fmp12

                                  I think this kind of structure would be better.

                                  Now My example is really basic, and remember I'm lacking a lot of information. But I think this would be the idea.

                                  In my example it's super easy to add a machine, or a product, to list operators and their info. To add and remove operators.

                                  If you have any questions, let me know.

                                  1 2 Previous Next