Many to many relationship update in ui

[BrianI]

Hi

 

I have a case where Athletes need to be related to Coaches. Both athletes and coaches are stored in the same table which I have named Users. I have created a second table with AthelteID and CoachID called tbl_coach_athlete_relationship so that I can have a many-many relationship and linked both to the Users table. This works and I can add the relationships.

 

I now need to be able to allow the athletes to select more coaches via the UI. I have done this by creating a submission for for the tbl_coach_athlete_relationship which works but it is not ideal because it allows the athlete to select the same coach many times and it is also not very easy to search.

 

Ideally I would like a tabular report which the user can use to search only the coaches not already assigned to them.

 

I have managed to do this partly by creating a view including all the records from the user table with type coach and records from the tbl_coach_athlete_relationship where there is a relationship. Then in the tabular view I added a filter where athleteID is not equal to the logged in user. That gives me the results in the tabular view that I need but I am stuck trying to find a way to allow the user to select a new coach and update the tbl_coach_athlete_relationship  table.

 

Thanks for any ideas.

[CoopperBackpack]

Hello @BrianI,

As far as I understand, you have Tables like these ones:

 

To prevent duplicate records submission you may add the so-called composite key.
In my example, IDs fields have an Integer data type, so I need to convert them to text, concatenate them, and mark the field as unique:

CAST([@field:AthleteID] as nvarchar)+CAST([@field:CoachID] as nvarchar)

This means the user cannot insert a record if the same combination of IDs is already stored in the Table. 
The same is relevant for the record update.

 

However, customization of the list of coaches requires JavaScript code that will populate the Dropdown or Listbox with values that depend on the logged-in user. 

[BrianI]

Thanks for the response. I think I had got that far. My question as a better summary is; is it possible to add a custom action link to a tabular report that will run some script to inject data into the connections table? And if so where do I write it and how to execute?

Cheers

[Tubby]

On 6/10/2023 at 6:57 AM, BrianI said:

Thanks for the response. I think I had got that far. My question as a better summary is; is it possible to add a custom action link to a tabular report that will run some script to inject data into the connections table? And if so where do I write it and how to execute?

Cheers

Not quite sure about injecting data to the table through script as if that is possible, it will be a huge security issue. But for any custom links or that you want to add on the table, you can add an HTML block and disable HTML editor and put the code for the link. For the script, just add a Header and Footer and put it in the Footer (with HTML editor disabled again)