Pick from a list of records and generate a many-to-many relationship to another set of records

[JoeyBrannon]

Here is the use case....

We have a list of teams that we work with (tableTeams)

We have a list of tools that we have developed (tableTools)

We would like to have an interface that lists all of the tools we have created with a blank checkbox next to each one

Checking the box makes the tool available to the current team (the current team is passed to the datapage using a parameter). Unchecking the box removes the tool from the current teams "toolbox"

---

I originally planned to tackle this using a many-to-many join table (tableToolAssignments) where fields for tableTeams.teamID and tableTools.toolID would relate teams to their available tools and I can still do that but I'm looking for a more intuitive and efficient interface. In other words I would just like to go down a list of checkboxes to check and uncheck tools as desired rather than create a new record by selecting a tool from a dropdown list to create each association.

[NiceDuck]

So we have two tables here.

Are each record from the tableTools already marked to determine which team it belong? 

The idea I have right now is that, instead of checkbox, you will have a text255 field (lets name it current_owner) that will hold the ID of the team that to which the tool is assigned.

On the datapage level, you can set this field as dropdown that directly uses the tableTeams as lookup table. The dropdown will show each of the the teams and the tools will be assigned on the team selected on that dropdown.

Then, you can now have a view which joins those two tables through the fields 'tableTeams.ID 'and 'tableTool.current_owner' That you can then use to create a report DataPage.

No need to pass parameters. 

[JoeyBrannon]

2 hours ago, NiceDuck said:

...
Are each record from the tableTools already marked to determine which team it belong? 
...

 a tool can belong to more than one team so the relationship between tableTool and tableTeams has to be many-to-many.

it might help to think of tools as training modules where certain modules might need pre-requisite modules to be completed first  and some modules might not apply to a particular team at all. As a new team is onboarded there is a standard set of modules that will almost always need to be made available to the team. So we are looking for a way for the advisor to go in and quickly see which modules have been assigned to the team and which have not and then easily assign any of those that have not.

The only way I know how to do a many-to-many relationship is through a third table that has fields for the tableTeams.teamID and the tableTool.toolID.

[DaveS]

Hey, Joey,

If I'm reading your post correctly it's close to something I dealt with a couple of weeks ago - a "Full Outer Join".  Take a look at this post and see if it is helpful to you.

[NiceDuck]

 
In this case, I would make an approach where it will now involve an extra table other than the Tools Table and Teams table.

Lets Call it "Assigned_Tools" table.

Now, what I would do there is that, It will have 4 fields. An ID field, a field to hold the tool's ID, A field to hold Teams ID and a checkbox.

Then I would use an application Task to create records for this new table. The idea is that to create a record on this table for ever combination  of records in between Teams and Tools table.

So let say we have team A and Team B and we have Hammer, Pliers and Wrench.

The 'Assigned_Tools' table will have 6 records in it.

Team A Wrench
Team A Hammer
Team A Pliers
Team B Wrench
Team B Hammer
Team B Pliers

Now, on the checkbox field, you just check/uncheck the records if that specific tool is assigned to them.

With this you can simply use a tabular report DataPage with inline edit to monitor the tool assignment. 

 

[NiceDuck]

You can then use a view, set to join those 3 tables. 

Tools table is inner joined to Assigned_Tools via Tools_ID field

Teams table is inner joined to Assigned_Tools via Teams_field

Then if you want to restrict the displayed record, put a criteria on the view so it would only display records where the checkbox in the 'Assigned_Tools' table is checked.