Also, these fields are in a portal that refers (correctly, I think) to the appropriate related table...
And what is the relationship used for that portal?
Did you select the "Create" option for the Notes table in this relationship?
"Create" is not the only way to do this, but it's the method that allows you to add new Notes records directly in the portal by entering data into the blank "add row" that this option causes to appear in your portal.
thanks for answering... I'm not entirely certain of your terminology. I created the Notes table in the Manage -> Database tool. (It's been more than a week, so I can't recall exactly how I did it! )
Where is the "Create" option? Perhaps I can try to create a "Notes 2" table to see if I've more success.
I'm not sure what you mean by "what is the relationship in the Portal". I have the Notes table listed in the Manage->Database-.Relationships display, with the "Company Name" connected to the main "Current Table".
When you created the portal, you were prompted for the related table (occurrence) the portal should point at.
In the Relationship Graph, that table occurrence forms a relationship with the table occurrence of your layout. If you click on the operator (very probably a =) in the connecting line for the relationship, you open the Relationship Setup dialog.
In this dialog you'll find the "Allow creation of records ..." option that Phil mentioned to be able to use the portal for the creation of related records, i.e. new notes.
This sounds much more complicated than it is.
I asked what is the relationship FOR the portal not what is the relationship IN the portal.
In layout setup and also in the status tool bar, there's a name of a table occurrence ( a box found in Manage | Database | Relationship ) that is the basis or "context" for your layout. Likewise, you've specified a table occurrence in Portal Setup--it's listed in "show related reocords from" and also in the bottom corner of the portal when you view it while in layout mode. The relationship linking the two table occurrences forms the relationship set up for your portal. The match fields and relationship options that you selected for this relationship will determine how your portal functions.
Go to Manage database | Relationships. Find these two table occurrences. You probably connected them with a relationship line that links one directly to the other. If so, double click that relationship line to open a dialog where you'll find a check box where you select the option that allows users to create related records by directly adding data to the 'add row' in the portal. You will also see what fields are specified as match fields in your relationship. If simply selecting the "create" option for the Notes table does not fix this issue, tell us what fields you selected as match fields.
If you did not directly link the two table occurrences, please describe or use the image tool in your tool bar to add a screen shot of your relationship graph to show us what you set up here.
And what is selected in the dialog box that opens when you double click the line linking CustomerInfoTable to Notes?
when that issue is resolved, you should tell Ronan about auto-entered, unique, meaningless primary keys in every table …
Yes, matching by company name is not a good idea, but as you've said, first we need to get the portal issue resolved.
And now the next step--what my friend erolst pointed out:
you are linking notes to CompanyInfoTable by company name. What will you do if:
a) A company changes its name or
b) You enter a company name incorrectly, log some notes in the notes table and only then discover the error in the company name field?
Either way, you should change the company name in the CompanyInfoTable, but if you do, your notes will disappear because they have been set up to link to the previous value in the company name field. This is one reason why it is usually best not to link tables by such name fields. The values don't stay the same and you have to both update the parent record (CompanyInfoTable) and every related record in the child table (Notes) with exactly the same value in order to keep the records correctly linked.
Instead, add an ID field to both tables. The simplest approach is to add a number field to CompanyInfoTable and set it to auto-enter a serial number. My preference is to name such a field like this:
The two underscores, cause the field to sort to the top of any alphabetic list of field names and you encounter such lists in a number of places in FileMaker when working with the design of your solution. The pk indicates that this is a primary key. I then put in the name of the table followed by "ID" to further identify the field.
Then add a number field to Notes, but without any auto-enter options. I'd name it:
the single underscore sorts it just below any primary key field with two underscores. The "fk" stands for Foreign Key and this identifies any field that matches to a primary key in relationships.
You can also make both of these fields text fields and set the primary key field to auto-enter this calculation:
Get ( UUID )
And it then also specify a "unique values" validation option on that field.
Both methods work. Serial numbers are simpler to work with in some respects--particularly value lists where the ID field might be the first field but UUID values are universally unique and thus make for easier updates if you have to import this info into a new copy of the file or have to "synch" the info from multiple copies of the file on multiple devices such as a bunch of iOS devices that all synch back to a main file.
Many thanks, Philmodjunk and Erolst! Your help has been invaluable! (I've been offline for a few days, so I'm just getting back to it now).
I have the "Notes" function working - thanks to yoru excellent help - but I will now take your advice and implement the ID filed solution.
Philmodjunk, I have a few questions about the __pkCustomerInfoTableID and _fkCustomerInfoTableID keys.
1. Since I've already got an extensive list of "customers", is there a was I can populate this field automatically? (I see that the "auto_enter" feature operates on "creation" which will presumably work for new customers, but my issue is with the existing database.
2. Thanks again for your discussion of the Primary/Foreign Keys. Am I correct in saying that the __pkCustomerInfoTableID in the CustomerInfoTable should be connected to the _fkCustomerInfoTableID in the Notes table? (And should I also delete the relationship between the "Company Names" in the two tables?
3. And since I have several different tables with the "company name" field, should I also add a _fkCustomerInfoTableID field in each table as you described in your discussion?
1. You can use replace field contents to add either serial numbers or UUID text to existing records. The serial number option in this tool can even update your serial number settings accordingly.