Many to Many Relationships

[ann]

Hi all,

i've got a many to many relationship between my property and project. I've created the link table and the portal is showing the records that I want.

I need to be able to assign more than one project to a property. What is the best way of doing this. I created a global table and a global field so I can set that and then use a script to update the link table. However, I@m not sure my relationship is good as I can't seem to get my global field to populate.

Is there a better way of allowing this? Potentially I need to be able to assign up to 10 projects to a property.

[bcooney]

There's many ways to create records in the join table "ProjectPropertyLink". It depends really on the workflow that you want.

I don't really see the need for the GlobalTable.

If the relationship between Projects and the join table has allow create checked on, then you can simply add records directly to the join table using a portal based on the join table. Typically, the foreign key is your target and it's formatted as a popup menu which is assigned a value list of Properties (Id and Name).

You can duplicate this functionality on a layout based on Properties, just do the reverse. The portal is still to the join table, but the key you'd show and select in the popup menu is to Projects (Id and Name).

You might outgrow this simple method with either list gets long. The go to technique I'd use would be a card window picker. User clicks and Add button that runs a picker script. It shows a card window that's a list of Projects and each row has a Select button. Clicking that Select button closes the card window and sets the key into the join table. The nice thing about this is that the Card window list can include a QuickFind filter. Google card window pickers, there's got to be demos around or youtube videos.