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.
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?
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.
Thank you PhilModJunk, this is a great help.
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 :-)
Open Manage | Database | Relationships.
Double Click a relationship line to open the Relationship Details dialog.
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.
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::__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.
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?
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.
I have those, apart from the product is not a child of the customer.
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.
How the customer links to other tables is not at issue here.
Let's drill down and see what we find:
For this relationship:
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.