1 2 3 4 Previous Next 59 Replies Latest reply on Jan 9, 2014 1:27 PM by philmodjunk

    Multiple input options for the same field

    pi-comm

      Title

      Multiple input options for the same field

      Post

           Hi

           I have a database set up with various tables (customers/products etc).

           The database is used by engineers who input the asset ID and the fields are populated from the other tables.

           However, there are some times that the database will not have the info, such as when new machinery is being serviced.  

           Can I set it up so that a if the data is not available, the engineers can manually complete the form, without having to enter the info in each table?  Perhaps a script with a button for manual input?

           If that is possible, is there a way to then update the other tables with the new info (i.e. the customer contact details etc).  therefore linking the tables in the opposite direction as normal?

           I know the obvious answer is to get the engineer to add the customer/asset each time but I think this is too complex for them and want the easiest setup possible.

           Thanks for any help.

        • 1. Re: Multiple input options for the same field
          philmodjunk

               Can I set it up so that a if the data is not available, the engineers can manually complete the form, without having to enter the info in each table?

               I hope that this does NOT mean that you need to enter the same data into multiple tables. If so, you would appear to have serious issues with the design of your database as the entire reason for using a relational database such as FileMaker is to avoid entering the same data into multiple tables.

               That said, there are multiple methods that could be used to implement such an "add a new record (or set of records)" situation. And using a script is a definite possibility.

               But devil is always in the details and those are not present in your first comment posted to this thread.

          • 2. Re: Multiple input options for the same field
            pi-comm

                 OK, a bit more detail.

                 Purpose of database:  engineers will be using FMG on iPads to log records of tests done on factory machinery

                 Tables included:  Products, Customers, Assets, Test results.  (Assets takes info from generic products table, and customers table.  Test Results takes info from all the other tables).

                 Problem:  Sometimes the engineers will come across a piece of kit that has not been included on the Asset table, or even the product table.  Rather than have them amending the various tables (that I really don't want them doing) I wondered if they could just do a manual test record, without pulling data from the other tables.  

                 The second part:  If they have manually entered details of new machinery within the 'test results' table, can I set up a relationship going the other direction to input this into the Products and Assets table?

                 Hope thats a bit clearer?

                  

            • 3. Re: Multiple input options for the same field
              philmodjunk

                   If your fields show looked up data, then a script can load variables with this data, switch to the parent table, create a new record, use set field to set the fields to the values of the variables, set a variable to the new record's primary key, switch back to the original layout and set the record's foreign key to the matching primary key to link the records.

                   But this can actually be made simpler yet. If you enable "allow creation.." for the parent table in this relationship, simply entering data into a field from the parent table while on the child table's layout will create a new parent record and copy the new parent record's ID into the current child record's foreign key field. In a script, a single Set Field step can make this happen.

              • 4. Re: Multiple input options for the same field
                pi-comm

                     Thank you PhilModJunk, this is a great help.

                • 5. Re: Multiple input options for the same field
                  pi-comm

                       So I have digested your answer and had a long look around my FMP database, but cannot work out how to 'allow creation'.

                       Do you know how where I need to go to (i.e. which menu I need to choose?).

                       Thanks in advance :-)

                  • 6. Re: Multiple input options for the same field
                    philmodjunk

                         Open Manage | Database | Relationships.

                         Double Click a relationship line to open the Relationship Details dialog.

                    • 7. Re: Multiple input options for the same field
                      pi-comm

                           Once again, I thank you sir.yes

                      • 8. Re: Multiple input options for the same field
                        pi-comm

                             So I have done all of the above, but because I am not creating the parent record manually (i.e. it is being done via the child record), the unique field is not being entered.

                             I am getting the error message 'This field cannot be modified until “insert unique ID name” is given a valid value.'

                             Is there a way of doing this automatically without a; having the unique IDs on my input page, and b; without the unique IDs being just another sequential number?

                              

                             Further detail:  one of the unique field names that links my tables is the 'Product code'.  In here I am trying to make these as obvious as possible by making them the machine model name.  So a auto generated number would not be ideal.  Can I set it up so that when a parent record is created from a child record, the unique ID will revert to being the model name?  (the model name field is on the input form that the engineers will use, but I want to keep this tidy as it becomes a work certificate for my customers when complete).

                             Another example is my customer number - I suppose this one could be an auto generated number.  

                        • 9. Re: Multiple input options for the same field
                          philmodjunk
                               

                                    and b; without the unique IDs being just another sequential number?

                               They should be "just another sequential number". the only other practical option is to use Get ( UUID ). What issue do you have with using the number? (The value linking parent to child, ideally, should not have any other purpose but to uniquely identify the parent. Name and other description fields can be added to the parent record so that your users can find the correct records without having to know the ID number or string of any parent or child record.

                               Here's what should work:

                               Say you have two tables: Parent and Child in this relationship:

                               Parent-----<Child

                               Parent::__pkParentID = Child::_fkParentID

                               where the __pk field is an auto-entered serial number and the _fk field is a number field. In the relationship, select "allow creation of records via this relationship" for the Parent table. That's not the normal option to choose here, but it's what you need for this example. Place any field from Parent except the __pkParentID field on your Child layout. Create a new Child record and type some data into the field from the Parent table. This will cause a new record to be created in Parent and the new record's serial number will be automically copied into the _fk field of your current Child record. Neither the _fk nor the __pk field need be present on your layout, but if they are, you'll see the new parent record's ID number appear in them.

                          • 10. Re: Multiple input options for the same field
                            pi-comm

                            yes

                                 Brilliant!!!!

                            • 11. Re: Multiple input options for the same field
                              pi-comm

                                   OK

                                   Nearly there now. 

                                   So I currently have 4 tables:  Customer info, product info, asset info, test results

                                   It is set up so that if there is a new asset or a new product then creation of a new parent record can be done from the 'test results' child table.

                                   However, if it is a new asset, but the same as a lot of other pieces of kit, how do I populate the other fields that relate to the parent record (product info).

                                   The info includes information about the specification of the equipment the engineers are testing.  If they use the asset number the filed is auto populated, however if they manually input, even if they pick an existing make and model from a drop down list, the subsequent fields are not populated and a new product is created with the same make and model.

                                   Do I need to to separate relationships to link the same tables?

                              • 12. Re: Multiple input options for the same field
                                philmodjunk

                                     SO you have these relationships?

                                     Customer----<product----<Asset-----<test results    (parent----<Child)

                                     Then, when you create a new Asset, the user will need to use one method or another to select the product that should be the parent of that new Asset. This could be a drop down list of ProductID's and Product names or more sophisticated methods could be used such as a search portal or an auto-complete enabled list of product names. These last two options use a script to get the correct ID value into the correct field in Asset.

                                • 13. Re: Multiple input options for the same field
                                  pi-comm

                                       I have those, apart from the product is not a child of the customer.  

                                       I have:

                                       Customer and Product that both go towards making the Asset, and the test results pulling info from Customer and Product via the Asset.

                                       Customer & Product -----<Asset-----<Test Results 

                                       The Make/Model is a drop down that is being populated from the 'Product' table, but does not then populate the other fields that are being pulled from the product table (it only does this when I use an existing asset ID).

                                       I am sure I am missing something very simple, but I am stumped.  

                                        

                                  • 14. Re: Multiple input options for the same field
                                    philmodjunk

                                         How the customer links to other tables is not at issue here.

                                         Let's drill down and see what we find:

                                         For this relationship:

                                         Product-----<Asset

                                         you should have match fields such as:

                                         Product::__pkProductID = Asset::_fkPRoductID

                                         Where __pkPRoductID should be defined as either an auto-entered serial number or an auto-entered Get ( UUID ). You may have other unique identifier fields in Product, such as a client supplied model number, but use an internally generated ID for the relationship. It's much safer. Your field names are likely different than what I am using, but how they are used and the properties I specify are critical.

                                         With that relationship there are two ways that data from Product can appear on an Asset layout:

                                         1) The data can be copied from Product. Auto-enter calculations and Looked up value options can copy data from the parent record in Product. This copy of the data means that subsequent changes to the looked up data in either asset or Product will not automatically change the corresponding data in the other table.

                                         2) The data can be referenced. You can use the field tool to add any desired field from Product to your Asset Layout. In this case, no data is copied and any change to the data will automatically appear on either Asset or Product based layouts as they are both referring to the one and only copy of this data.

                                         Both options have their uses in different circumstances. Both require a valid relationship between parent and child.

                                         So if data from Product does not appear on your asset layout, the first thing to check is if the relationship is valid, has a value been entered into _fkProductID that exactly matches the __pkProductID field in the Product Table?

                                         The second thing to check is to see if these are fields that copy the data from Product. If they do, then you may need to check their auto-enter field options to see if you can spot why they didn't update. Some auto-enter calculations may need to be set to replace existing values before they'll update when the user selects a product for the new asset.

                                    1 2 3 4 Previous Next