Subform with many to many relationship

[treed]

Hello All, Got the classic organization table with OrgID and Contacts table with ContactID. The contacts can be contacts for more than 1 organization. Made a linking table ContctToOrg with OrgID and ContactID in a record. The problem is the subforms only allow a single field for linking and a single table for the form to be based on. So how would I accomplish this task? Make an editable view?

[Janusz]

Hi,
Just example of one implementation I have with many-to-many connections.
I created additional table called connection which in fact keeps only just IDs both for organization and contacts.
This Connection table is a subform. Additional fields on the subform with contacts data are display objects with hash cookies making the link to the subform specific record ex.:

Code: Select all

SELECT rap_location from v_connect_sub WHERE connection_id='#RECORD_ID#'

additionally can be added field updated-on to keep some history.
When I want to add new contact it's not with subform - its with contacts form.
To add already existing contact to the subform I use LookUp. To open the Contact form with specific record you can use onclick in the subform ex.

Code: Select all

if (nuFORM.edited) {return;}  var f=$('#con_rap').val(); var f=nuSubformValue(this, 'con_rap'); if (f!=='') {nuForm('5d5fbf488ceedd4',f,'','');}

Przechwytywanie.JPG

Przechwytywa1nie.JPG

and practically it works very well.

[treed]

Thank you Janusz, looks like a workable solution. Question where to you put the SQL code for the display fields? And when the user comes back from entering a new contact, is that contact now added to the connections table or does the user need to then select the new contact with the lookup?

[Janusz]

The code is added as following:

p1.JPG

In my application the contact after added is not in the connection table - but it's just my choice - because different people are defining let say "contacts" and different are linking them with "Organizations".
But in your application can be done that if the new contact is initiated from "Organisation" form then it can be assigned automatically to the current record - I mean after creation it will generate connection record.
Additional remarks:
In the display I refer not directly to the "contacts" table but to the view which is composed with connection and contacts:
example connnection table:

p2.JPG

example view used in display:

p3.JPG

[treed]

Thanks Janusz I have it working!

[treed]

Well almost, I'm trying to open the edit screen with no success. The subform has a lookup with and ID of ContactID and I can't seem to get that value in a hash cookie. Trying to do this from a RUN object instead of the onclick event of the form.

[kev1n]

I don't quite understand what you are trying to do. Can you describe in more details? (Add screenshots if necessary)

[Janusz]

@treed - please find enclosed short ad hoc movie - with some visual explanation how it works.
https://drive.google.com/file/d/1Ao6y6z ... sp=sharing

[treed]

First THANK YOU for all the help!!! Below are some screen shots of what I'm trying to do. Very similar to Janusz in the attached video. If I put the ContactID instead of the hash cookie the correct record is opened in the contact form. Using the hash cookie the form is blank.

[Janusz]

Yes - for the lookup - no hash to be used.
Hash cookies to be used only in the display/SQL objects to link it with proper record/line in the subform.

Not sure but looks like for IDs you are probably using Autonumber (looking on the Lookup) - in some cases it may link to difficulties - me personally did not experience it - but after reading following post I spent some time to remove Autonumbers from my databases.
https://forums.nubuilder.cloud/viewtopic. ... autonumber