5 Replies Latest reply on Jan 6, 2014 12:38 PM by pi-comm

    Multiple input options, and multiple links between tables

    pi-comm

      Hi

       

      I use FMP for engineers to go into factories and check pieces of equipment.

       

      My tables are as follows: Products, Customers, Assets, Test results (relationships attached below)

       

      The Asset table is made up from a combination of Products and Customers. The Test results are entered by the engineers by using an asset number, that populates all of the product and customer info into the Test Results table, so they can simply put the results of the tests.

       

      This works fine. However, when an engineer gets a new Asset, I want them to be able to put in a new Asset number on the test results, which will create a new Asset, but I want them to choose a make and model and have them fields automatically populated from the products table.

       

      Whenever an engineer puts in a new asset number, they choose the make and model from a drop down list, but this then creates a new product, duplicating the one that is already there. When I ask for the Product::Model field to be a unique value, it will then not let the engineers pick an existing value, as it says it is not unique.

       

      My question is: How do I stop the product table creating duplicate products each time a new asset is created? (to clarify, I only want the engineers to access the test results table, I do not want them manually creating new products).

       

      Thank you for any help!

        • 1. Re: Multiple input options, and multiple links between tables
          jlamprecht

          Hi,

           

          It is odd that the drop down is creating new Product records. I would check to see what fields you are using for the drop downs in addition to if you have set up for new Product records to be created based on the relationships you have there.

           

          If the engineers want to create a new Asset, I would take them to a layout with the context for the Asset table. (You can also leave them in the Test Results context and use the relationship and just allow creation. My preference is to take them to the Asset context.) Then, I would create a value list based on the Product table. It would consist of the PI Product ID (unique value) that would be used in the relationship between Asset and Product. I would also include a second value to show in the value list. This could be Make or Model or also a new calculation field that holds the Make and Model of the Product. This value list would be used on the field PI Product ID in the Asset table.

           

          There are many ways of accomplishing your request. This is just one that comes to mind. If you prefer another method, let me know and I will glady help.

           

          -JohnAustin

          • 2. Re: Multiple input options, and multiple links between tables
            pi-comm

            Thank you for your answer.  This too has me baffled!  and extremely frustrated.

             

            It is odd that the drop down is creating new Product records. I would check to see what fields you are using for the drop downs in addition to if you have set up for new Product records to be created based on the relationships you have there.

             

            The drop down comes from 'Product Info::Model'.  In some instances I will need new products to be created (i.e. when it is a new product), but have no idea why it will not use existing data.

             

            If the engineers want to create a new Asset, I would take them to a layout with the context for the Asset table. (You can also leave them in the Test Results context and use the relationship and just allow creation. My preference is to take them to the Asset context.) Then, I would create a value list based on the Product table. It would consist of the PI Product ID (unique value) that would be used in the relationship between Asset and Product. I would also include a second value to show in the value list. This could be Make or Model or also a new calculation field that holds the Make and Model of the Product. This value list would be used on the field PI Product ID in the Asset table.

             

            PI Product ID previously consisted of the same info as the model field, however I was advised to change this to a unique serial number.  Do you think it would be better changing it back?

             

            I would really prefer them to stay within the Test Results table, otherwise they will get confused and simply wont bother entering new Assets.  Ideally they would enter 'Test Results::Model' which is a drop down list that consists of 'Product info::model' data, and that would populate the other 12 fields that relate to the model.  Do you see any issues with the way I have the relationships set out and doing this?

             

            I really think it is something to do with the 'Product Info::Model' field.  It is set up as Field name:Model  Type:text   Options: Indexed, By Value List, Unique

             

            Any ideas?

             

            Cheers

            • 3. Re: Multiple input options, and multiple links between tables
              jlamprecht

              Yes, that is correct. Keep PI Product ID set up as a unique SN. That is the best way to go. You should use that field in the relationships you make to ensure you a referencing a single, unique Product.

               

              For the 'Product Info::Model' field, you have By Value List, Unique. What value list are you using there and why are you using it? Just so I can understand what you are trying to accomplish. This value list could be the issue.

               

              Concerning the relationship setup you have, why do you have two Table Occurrences for PI Product Info?

               

              Also, can you explain what an Asset is? From my understanding, I assume this is a Product held by a particular Company.

              • 4. Re: Multiple input options, and multiple links between tables
                pi-comm

                For the Product Info::Model field, the picklist is just results from that field.  It is for easy entry when evtering a new product from the Product Info table (although the engineers wont do it from there, a couple of us will still use this table to set up new products.

                 

                The second PI Product was an attempt to link Product::model to Asset:: AssetModel (this is a seperate field but using the same model dropdown list as the others above).  The link was to try and get the correct product ID.  This could be totally wrong so happy to take any ideas if it needs changing?

                 

                In this context an asset is a specific machine, and combines generic product info, and the customer details, along with its exact location and a few other bits of info.

                • 5. Re: Multiple input options, and multiple links between tables
                  pi-comm

                  So I have looked at this and since previously changing the fields to the description above, it no longer creates a new product by entering the product name, but the issue still stands that I cannot get the info to pull through from the product table onto the test results  - unless it is an existing Asset.