Why have you related the two tables by material? What is that supposed to accomplish for you?
With this relationship: TableA::Material = TableB::Material
and a portal of TableB located on a TableA layout, if you enter "plaster" in TableA::material, then only records in TableB that have "Plaster" in the TableB::Material field will be visible. If you enabled the "Allow creation of records via this relationship" option for TableB in the above relationship, using the portal to enter new data will automatically enter "plaster" in TableB::Material for this record in order to maintain the relationship between the portal records and the current record in TableA. If it didn't do that, your new "brick" record would disappear and you'd need to create a record in TableA with "brick" in its material field.
I must sound pretty dense here, most of FM seems pretty basic but I'm really struggling with relationships (i'll probably struggle with scripts but that comes later.
I think one of the issues I have is I'm following the VTC tutorial and it uses a product/customer/invoice example which all makes sense when it comes to relationships, but I'm trying to relate what I'm learning to my own scenario which is simply a data gathering. I complete the tutorial exercise and then try to relate it to my own requirements and from there it just seems to fall apart.
As you answered my question in another thread, I assume you know what I'm trying to achieve, if I shouldn't relate materials what should I relate.
I don't know what you should relate here as I don't know the purpose of the portal.
Using the invoice example from VTC (I haven't seen it but am pretty sure it follows this model), You would have a a field in the invoice table that auto-enters a serial number. The line items in the invoice would be link an InvoiceID number field to this serial number field so that the portal on any given invoice record lists all purchased items for that invoice. The key concept here is that the current invoice record and the listed line item records in the portal all have a common value the invoice ID number that was auto-entered as a serial number. If you create a new invoice record, it will have a different serial number value and thus it won't list the line item records from the previous invoice. Instead, any new records created in the portal will have the same invoiceID number as the new invoice record.
So in your case, you have one field in the current record of table A should have a common value with the related records shown in the portal to Table B. What value should be in common between these records in order to show the values you want in the portal?
What does one portal record (Sometimes called child records) represent?
What does one record in Table A (the parent record) represent?
I have it working properly now thanks Phil, I'm not sure it's the ultimate answer but for now a name field is serving as the common field.
"but for now a name field is serving as the common field."
You should change this before you get too far into your design because the name field is probably one of the most important relationships in your entire structure. And once you start creating relationships using it, it is difficult to change later. Change now when you are starting your design.
Your customers or staff or whatever represents 'name' should have their own table with a unique auto-enter, FM-generated serial (number field) which increments by 1. And tables should be related using this ID. This is very important concept and every table should have its own unique ID established. :^)
I'm not yet at the stage of designing a database, I think I likely have a complex project for a first time project especially for someone who's skills are in commercial property and not at the computer. My goal is to to create a database to collect data about buildings and their energy requirements, on a typical survey (job) I will collect 3,000 to 5,000 individual bits of data and I want to do this on my iPad and lose the pen and paper.
I'm currently undertaking the VTC tutorials and trying to work out how what I learn will fit in with my own requirement and have divided my own project up into parts for the time being, which might not be the best approach but allows me to apply what I've learned to what I'm trying to acheive.
Once I have worked through the tutorial and practiced in sections, I plan to start the project from the ground up, but use any parts that might be useful that I can (say custom graphics, value lists etc) but I don't expect there to be very much.
I was struggling to grasp the concept of relationships, I was missing a vital part of the puzzle that was stopping me progressing, Phil pointed me in the right direction.
In this particular instance the name field I was missing was the name of a wall, within each job the name of a wall will be unique. As with everything it now seems somewhat more obvious from the other side.
Just keep in mind what LaRetta is telling you. Using a name field for a relationship instead of an ID number is a common "newbie" mistake and she is trying to help you avoid it.
Where relying on a name field will get you in trouble is that if you discover you have entered or selected the name in error and then correct your error, you break the connection to any related records in the portal and they seem to disappear. You'd have to find the related records first, update them with the new name then return to the parent record and update it with exactly the same name. Using an auto-entered serial number avoids this issue.
There are ways to use both a name field and the serial number field in a drop down list so that you can base the relationship on the serial number but your user selects the unique name from the value list to enter it.
Many thanks Phil. I hope I wasn't coming across the wrong way, I wasn't dismissing the advice given, indeed I've now incorporated using an ID for the relationship into the projects I am experimenting with. The problem was I was having difficulty understanding relationships and couldn't get them to work at all. By using a name field temporarily it allowed me to see relationships working and then move on to make them work better.