Confused Newbie - Many to one relationship

[St3v1e]

I’m new to FMPA 18, and have been “playing” with it over the past few months after being asked by my supervisor to create a small database to keep track of our customers. Nothing too fancy, but just enough that we can search for a name/company, create a few notes and print a pdf/invoice when required etc.

I’ve been creating a few test databases and have checked out hours and hours of online tutorials, but this particular issue has me confused & frustrated.

 

I’m trying to figure out how to have many contacts (employees) associated with one company (many to one?). I created a portal in the “Contacts” layout which references only the “CompanyName” field in the “Companies” table, and then created another portal in the “Companies” layout to show all employee’s in that company.

My setup:-

Tables:
I currently have a “Contacts” table, and a “Companies” table, and will eventually add the “Notes” table before looking into more complex items (pdf’s etc).

Fields:
Both tables have Primary Keys (formatted as Text) setup to use the “Get( UUID)” reference (“Calculated Value” box in the Options/Auto-Enter window with also the “Do not replace existing value of field (if any)” box). Also in the same Options/Validation window, I’ve checked “only during data entry”, “Not empty” & “Unique Value” to complete the Primary key setup.

I’ve followed this same process for the “Companies” table PK field.

Next I created a foreign key (“company_id” - my PK’s are upper case, and FK’s lower case), which is formatted as “Text” in both tables and has no other formatting or field “Options” applied.

Finally, in the “Relationships” tab, I initially connected the PK (Contacts) & FK(Companies) together and checked “Allow Creation of records in this table via this relationship” in the “Company” table. (See attached screenshot)

The problem:
When I create a new record in the contacts layout, and then add the company name, a new company is added in the companies records. But when I create subsequent contacts and add the same company, a new RECORD in the companies Records is created. Only one employee shows up in each “Companies” (portal) record.

I also tried removing the relationship between the PK and FK, and using a text field in each named “Company” - this produces a list of employees in the “Contacts” portal window (Companies Layout), but again adds a new record which duplicates the first record. If I then add a third record I have 3 employees and 3 records in the Companies Table.

Obviously I don’t require a new record every time I enter the same company name but want to reference each employee to a single company record, so I’m missing something and have not yet been able to find a tutorial which helps.

Hope the above is not overly confusing.

Any feedback or help very much appreciated.

• Insert existing attachment

 

[comment]

Your relationship should be based on matching the primary key field in Companies to the foreign key field in Contacts - and nothing else. This will allow you to change a company's name without breaking the relationship to its existing contacts.

 

9 minutes ago, St3v1e said:

When I create a new record in the contacts layout, and then add the company name ...

When you create a new contact in a layout of Contacts, you need to select a value for the foreign key field in Contacts. There is no need to "add the company name" - although you can show the company's name when selecting the value from a value list of companies primary keys.

 

13 minutes ago, St3v1e said:

... a new company is added in the companies records

That's because you have "Allow creation ..." turned on on the Companies side of the relationship. That makes very little sense. OTOH, if you want to allow adding new contacts by entering them into a portal on a layout of Companies, then you need to turn on Allow creation ..." on the Contacts side of the relationship.

 

16 minutes ago, St3v1e said:

I created a portal in the “Contacts” layout which references only the “CompanyName” field in the “Companies” table

That's not necessary. As your title says, this is a one-to-many relationship. A portal is used to display records from the "many" side of the relationship. To show the contact's parent company, simply place the Companies::CompanyName field on the layout of Contacts. Make sure it's not enterable, so it won't get modified accidentally.

 

[St3v1e]

Hi comment - thanks for your feedback:

Quote

When you create a new contact in a layout of Contacts, you need to select a value for the foreign key field in Contacts. There is no need to "add the company name" - although you can show the company's name when selecting the value from a value list of companies primary keys

OK, so actually instead of adding the company by name, I’d add something like “1002” ? But if that’s the case, how would I remember all the company “values”?

Is this "foreign key" value the UUID from the Primary Key in the "Companies" table, or would I create an editable drop-down list which would also serve as the foreign key??

How does this work in real-life terms?

Quote

That's because you have "Allow creation ..." turned on on the Companies side of the relationship. That makes very little sense. OTOH, if you want to allow adding new contacts by entering them into a portal on a layout of Companies, then you need to turn on Allow creation ..." on the Contacts side of the relationship.

I was hoping to be able to create a new “Company” record from the “Contacts” layout, but are you advising that’s either 1) An incorrect methodology for a DB, or 2) Not possible in FMP?

Possibly this is tied in with my first question?

Quote

That's not necessary. As your title says, this is a one-to-many relationship. A portal is used to display records from the "many" side of the relationship. To show the contact's parent company, simply place the Companies::CompanyName field on the layout of Contacts. Make sure it's not enterable, so it won't get modified accidentally

OK, I believe that’s what I have in the bottom l/h side of the screenshot.

The “CompanyName” field has been inserted from the “Companies” table. But I was hoping to show a list of the “Many” companies in the portal in the “Contacts” layout. Most likely wouldn’t use it in the final layout, but what it showed was the many records created of the same company as a quick reference/check (that much I achieved).

I’m still confused about the setting up of foreign keys, can you explain if the PK populates this, or if it’s independent and needs to be setup as a UUID, or a value list reference etc? Am I correct in assuming all PK’s should all be set up as UUID’s on creation of the record ?

Any chance you may have some time to put together a quick demo file so I can figure this out?

 

Many thanks

Edited May 6, 2020 by St3v1e
update

[comment]

Here's a bare-bones demo of a parent/child relationship. You can create child records by entering data into the first empty portal row (this will automatically make the new child related to the current parent), or by creating a new record in the Child layout and selecting the parent for it.

If you want to create a new parent for a child, I suggest you script it, instead of trying to do it through a relationship.

 

2 hours ago, St3v1e said:

I was hoping to show a list of the “Many” companies in the portal in the “Contacts” layout.

Not sure what you mean by that. As I said, a contact has only one company, not many. You can, if you want, show a portal of ALL companies - but you would have to define another relationship for this. 

 

ParentChild.fmp12

[St3v1e]

Thanks,

Quote

If you want to create a new parent for a child, I suggest you script it, instead of trying to do it through a relationship.

Yes I'm beginning to think what I'm trying to achieve might need scripting.

Just checking the demo . . . . thanks.

[St3v1e]

comment: Thanks for the demo, and after playing around with it, I've come to the conclusion I'll most likely need scripts to achieve what we need.

In summary (revision after checking your demo file), this is what I’d “like” the database/layouts to do before moving on further:-

    1.    Create new contact record (easy to do)
    2.    Choose a company this contact is associated with in a “Company Name” field or drop-down box scenario.
    3.    If no company exists -click button to create new company/record in Companies table/layout
    4.    In either case “Company Name” field in Contacts layout will be auto-populated or chosen from drop-down list (other idea’s welcome) once selected.
    5.    If another contact record is created, and this new contact is related to an existing company, it should not create another “Companies” record (ie: duplicating the same company)
    6.    Deleting/changing the Company Name inside of the Contact record should update the company employee record in the “Companies” record (“Employees” - Portal to “Contacts” references inside of “Companies” layout?)
    7.    Deleting/changing the Company Name inside of the Contact record should not delete the company record unless “nil” employees are associated with the company record.
    8.    I prefer not to have to go to the “Companies” layout to create a new Company, but would prefer a Pop-over button type entry within the “Contacts” layout, whereby I can enter all information and have it carried over to the newly created record.

I’ve been testing the popover button situation, and this would work very well for creating a new company (record) if I’m able to implement it correctly.

Does the above seem feasible?

If so . . where to start?

Thanks

[comment]

All this is feasible (although not all of it advisable) and can be implemented in a number of ways, some more "slick" than others in terms of user interface. Since you describe yourself as novice, I would recommend you concentrate on the simpler ones.

So for example, to create a new company from a layout of Contacts, I would suggest using a custom dialog to collect the necessary user input into variables, then have your script hop over to the Companies layout, create a new record, populate its fields with the user input, grab the CompanyID primary key value into another variable, come back to Contacts and set the CompanyID foreign key field.

I don't think it's a good idea to allow changing the company name (or any other company attribute) directly from the Contacts layout, because such edit affects all contacts related to that company, not just the current one. But if you want, you could place an "Edit" button and have it display a custom dialog with the fields from the related company's record for the user to modify.

Another option is to use the Go to Related Record script step to display the related company's record in a card window (and you can also use a card window to create a new company record, instead of a custom dialog).

A popover may be suitable for editing an existing company's details (again, with the caveat that such edit affects all its contacts), but to use it for creating a new one you would need to add a dedicated relationship; this is an advanced technique that I don't want to go into now.

A user should not be able to delete a company's name. If they want to delete a company's record, you need to make sure that the company has no child contacts (this can be set up in users privilege set).

 

[St3v1e]

comment - excellent. Thats enough infomration for me to be able to get started with this, without worrying about the possibility of needing to rebuild parts of the database later.

I'll divulge all this information and come back in a few days if I have any further questions . . .  if I may?

Thanks again, really appreciate the help & direction.