Join table problem

[scottsp]

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,

scott

[comment]

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:

http://fmforums.com/forum/topic/50942-portal-grouping-problem/#entry239210

[scottsp]

 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.

 

sincerely,

sp

[comment]

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.

[scottsp]

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,

sp

LMC2.fmp13.zip