Jump to content

Join table problem


This topic is 2598 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

hello.  I am new to relational dbases to apologies to start.  

I have a dbase (LOAN) that  contains loan information.  It has a primary key field _kp_LoanID .  
I have a dbase with people (COMPANY) in it.  These people can be lenders, borrowers, escrow folk, etc…..  It has a primary key field _kp_CompanyID.  Each of the Primary Keys are auto-enter serial fields with required, unique values.
I have a join table LoanCompany with it’s own primary key field _kp_LoanCompanyID and two foreign key fields _kf_LoanID and _kf_CompanyID.  As the same person often wears multiple hats i thought it best to put the role_in Company field in the join table also.  The person and their role is linked here and COMPANY can JUST be the contact information for the person.

There is a many to many relationship between these two main dbase, as the same person can have many loans associated with them and one loan will have many people associated with it.  I have created a relationship between the LOAN dbase and and its associated foreign key in the join table, and the COMPANY dbase and its associated foreign key in the join table.

I have created layouts to enter data in the non-join databases. I can create a new loan record and then go over and enter a new company record.  On the company layout I then choose what loan that COMPANY record is associated with and what hat the company person is wearing.  I put related fields on the layout to ensure I was getting the correct loan and join information.

 A new record is created in the Join dbase when a new company record is created-that is the foreign keys are created once both parts of the relationship between LOAN and COMPANY have been established.  (FMP automatically creates the matching foreign keys because I put those fields on the Company input layout, I believe.  I tried with just the join fields and with them thru a portal.   It did not seem to have any impact one or the other so I took the portal out.)

What I want to do is find a company record that has been used previously on a loan ( has at least one join record in the join table) and create a new instance in the join table so I can add a different role to that person with the same loan or possibly just add them to a new loan, with the same role or maybe add them to a new loan with a new role.

The problem is I cannot  get a new join record created that I can then fill with appropriate Loan and Company and Role information.  Without that when I have a company record up and modify the _kf_LoanID to get a new loan associated, it just CHANGES the join record.  The same occurs with and modification of the role_in_Company field.  The Join record is MODIFIED, but not a new one created.  Being able to modify it is helpful.  But some how I need to be able to independently add a new record to join to make the dbases useful.  

Or have someone tell me I really mucked up the design of the two databases in the first place and a better way to lay that out.  

Thank you in advance for your assistance,


Link to comment
Share on other sites

I tried with just the join fields and with them thru a portal.   It did not seem to have any impact one or the other so I took the portal out.)


If I am following your description correctly (which is not at all certain), that is the main issue here: you must have a portal on the layout of Companies in order to view (and create) multiple join records.


See if the basic demo posted here can help:


Link to comment
Share on other sites

  • Newbies

 I have been reading and viewing so many FM posts that I am not sure from where I heard what at this point.  I recall someone writing you can either create a portal to see related info or can just add the field as from the related table directly to the layout and if the relationship was set up correctly and you check off that records can be created from the parent to the child, then that will happen.  The data across the relationships is coming thru on the layouts and with the correct sequence of adding new records in the main dbases, new join table records are also being created.  it becomes a problem when i want to reuse a record from one or the other and change the associated fields in the related dbases. 


All that said, I will go back and redo with portals and see if that resolves it all.  thank you for your extremely quick response.




Link to comment
Share on other sites

The relationship between Companies and the LoanCompany join table is one-to-many. If you place a field from LoanCompany on a layout of Companies outside of a portal, it will display data from the first related record in the join table. You need the portal to view more than one related record.


There are ways to create new related records without using the last (i.e. first empty) portal row. I suggest you put them aside until you understand the basic method.

Link to comment
Share on other sites

  • Newbies

thank you for that.  At this point, I am not really trying to view more than one record at a time.  As of yet, it is not important to view all the loans related to a given Company record.  Nor do I really need to show all the hats a particular Company person is wearing.  I am mearly trying to solve how the user will get data into each of the databases successfully.  getting it out I will deal with later.  I THINK i now understand how a portal will helpme to get some of it out/viewed on the screen.  I also see how the portal allows the creation of a join record when I add any loan number to the mix and then avails itself to add another record if I want.  In reality what I really need for input is JUST the LAST row of the portal, that one that allows input, not changes.  That would allow me to add people and hats to my hearts content and associated each and any with any given loan.  Is it possible to do that?  I have attached the file I have been working with.  I would be very grateful if you would take a gander and let me know what you think.


thank you again,



Link to comment
Share on other sites

This topic is 2598 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By Lea Alexander
      I have 3 tables: Person, Session, and Contract where Contract is a join table.  
      I am in the Session table layout, in a Contract portal.  I have a field – Trainer Name in the Contract table – where I want to select the name of a person from a list of people in the Person table.  The Trainer Name field is a dropdown list using values from the Trainer value list “Person::__k_p_Person and Person::Name_Full”. I have tried numerous configurations with table instances and I either cannot alter the Trainer Name field at all, or I get a “?”.  
      After the Contract record is created, I want it to show up in a Contract portal on the person’s record in the Person table.  
      Each person will teach multiple sessions.
      Each session has multiple trainers.
      I have foreign keys in the Contract table for Person and Session. 

    • By -dp-
      I’m attaching a cluster of screenshots that document a join table. All field are indexed. It looks correct to me, but I’m not able to enter groups in the portal. I can’t even access the field. Any help would be appreciated. Thanks.

    • By Auraboros
      Here is the scenario...
      Contact table joined to Contact|Event table joined to Event table
      Portal in Contact table of Event table to see all Events linked to a particular Contact
      Needing to link 100 (or any number) of Contacts to 1 Event in Event table
      Do not want to do this manually. 
      I have all the tables and relationships set up and everything works. All I need is a way to create multiple records (joint records in Contact|Event table) in multiple Contact records so I don't have to enter them in one by one...which of course I can do.
      I figure this would be a script...but I don't even know where to call the script...in the Contact record? In the portal row? in the Event record? Where does the script button go? And what is the script? Once I create a found set in Contacts, how do I assign one Event to many contacts (in the found set) without having to enter the link manually.
    • By Guy_Smith
      Is there a difference in how Filemaker reads/acts on the two following Relationships:
      1.  Parent Table A --< Join Table AB >-- Parent Table B  and
      2.  Parent Table B --< Join Table BA >-- Parent Table A  ?
      After my last disastrous foray into designing a Relationship Graph, I went back to the Filemaker Training Series to re-re-re-read about building correct ERDs and Relationship Graphs, but only managed to confuse myself even more.  The FTS examples all use the Anchor and Buoy concept, but seem to have a huge amount of unnecessary repetition, unless I'm completely off base.  Using their "04_Bonsai" Relationship Graph as an example, they have one Anchor-Buoy set of
      ORDER --< order_LINEITEM >-- order_lineitem_PRODUCT        and another Anchor-Buoy set of
      PRODUCT --< product_LINEITEM >-- product_lineitem_ORDER.  
      These seem functionally IDENTICAL to me - you can even swap the icons around so the ORDER TO is on the left, the LINEITEM TO is in the middle, and the PRODUCT TO is on the right for both sets without changing the Relationships between them  (and yet a third set with LINEITEM on the left and both lineitem_ORDER and lineitem_PRODUCT to it's right, which can be manipulated to match the above two sets as well).
      If i'm sitting on a record in the ORDER TO, looking through the LINEITEM TO into the PRODUCT TO, does it matter if I have to look left or have to look right?  Or are they just adding a bunch of Anchor-Buoy sets on the Graph to, ummmm, "clarify" process flows?  Are there any performance issues in eliminating the redundant TOs? Is it considered a Best Practice to place a TO on the left of the Graph for every Table and delineate Relationships to the right of each beginning TO?04_Bonsai Relationship Graph.pdf
       I have included a screenshot of the Relationship Graph (squished to fit) to illustrate my problem and, as always, your insights are greatly appreciated.
    • By Lyinggod
      I am building a digital asset manager in FM 13 for Windows. I am having problems displaying filtered data in a portal. I have series of scripts that bulk imports the file names & folder structure and creates a collapsible hierarchical keyword structure that is comparable to the folder structure. It creates all the joins between keywords and assets. 
      results in
      When "Fiat" is clicked, all assets associated with this path are shown in a second "Asset" portal using the filter:
      not IsEmpty ( FilterValues (  Data for Keywords::ID ; global::gCurrent Keyword ) )
      This portion works fine. User added keywords are intended to appear in the same keyword portal using the structure of:
      c: (imported example above)
      ------User Added keyword (ie Fiat)
      A script inserts the user created keyword alphabetically into the keyword hierarchy and creates the join table record. This is where the problem occurs. Clicking on the user keyword in the keyword portal returns an empty asset portal. I have double checked all PK values in the asset and keyword tables against the matching join table records and they are correct.  I have also manually deleted and inserted working and no-working join link records to see if there is an issue with how the joins are created (manually vs script) without affect.
      Any suggestions on why this may be occurring would be greatly appreciated.
      Thank you

  • Who Viewed the Topic

    1 member has viewed this topic:
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.