Adding a new row from a referenced table within a details screen

[Mishta_P]

OK. I have been trying to avoid this post for a week.

I have a simple contacts database.
I have a simple tournaments database.
I have a linking table for when I want to register Contact to a Tournament (they will also register as Yes/No/Maybe, but that is not overly relevant).

I have a virtual column within my contacts table that looks up any upcoming tournaments that the contact is NOT registered for (here is where my headaches started).

**I have also tried a virtual column in the tournaments table to, which achieved a similar result but in reverse, with no luck**.

On the customer details screen I have the list of Unregistered Tournaments.

This acts as though it is in the Tournaments table so...

If I create a "Register" action on here I can add a row to the linking PlayerRegistrations table with the TournamentID (Tournament.Row ID) easily, however I have been unable to find a way to bring across the Contact ID (Contact.Row ID) as part of the same action, or series of linked actions.

I have tried every combination of linktoform, context, thisrow, parent, getrow etc etc. I have tried various ways to temporarily store the Contact ID data in the tournaments table.

I have searched everything I can, and even spent some time with ChatGPT and Gemini, who both get into a loop around _Parent or _Context etc which I had already failed on.

I have created similar functions in other apps with no issues, both coded and no code, but can't seem to work my way through this one.

Happy to provide any screenshots, although I am not sure they will be overly helpful as I am not getting errors, just not able to achieve the last part in something I think should be fairly straightforward.

Any guidance would be greatly appreciated.

[Suvrutt_Gurjar]

May we request a description if possible with screenshots of the relevant columns in the relevant tables? This could help the community in understand table relationships and your requirements better.

A simplified  example of how the description and screenshots can be is given below:

There are two tables, "Customer" and "Orders"

The "Customers" table is referenced in the "Orders" table.

I am trying to pull "Customer" s city in the "Orders" table.

[Mishta_P]

Contacts table. I am using UnregisteredTournament to find all Tournaments (table below) where the contacts Row ID does not appear in the Related PlayerRegistrations field.
Contacts Table

I have tried versions in reverse where I have a virtual column in the tournaments table, this ended up with the same issue in reverse

Tournaments Table

I use the PlayerRegistration table to join the two together with an availability marker. This is working as intended, if I use a normal form.

Joining Table

Below are a couple of Contact views, there is more data above, but its not exciting and all other links etc are working as expected.

This example has someone registered to a tournament as Yes and one "upcoming tournament" ie Unregistered

Contact 2

This one has 2 upcoming that they are not yet registered for

Contact 1

This is the current action for the Register button (the star symbol). I know this version doesn't work, because it references the "CurrentContact" column in the tournaments table where I was hoping to store a temporary copy of the Contacts Row ID (with no luck).

Register action

I hope that provides some context.

I would like to get the Register button taking through the Tournament Row ID (no problem), and also the Contacts Row ID (which is the screen in which the table sits and is filtered on, but is where the problem lies). This would go to a screen with the Yes/No/Maybe dropdown. However, if the Register button works, then I could just create a button each for Yes, No and Maybe with a confirmation screen to confirm the action.

I assume I am missing something obvious (most likely), or may have just hit a technical limitation (I hope not).

I can work around the issue with a registration form, but that is just less user friendly when I know it can be done more effectively (hopefully).

[TeeSee1 · Accepted solution]

I can suggest one way though it is not exactly what you are looking for.

I would create an ENUMLIST of reference to Tournaments in the Contacts table and in the suggested values, list unregistered tournaments for the contact.

After the user selects tournaments that they want to register for, then use this technique (variations of it) to add to the registration table.

If your action runs on the tournaments table,  there is no way that I am aware of to indicate where you are on the Contacts table unless you somehow 'tag' the row. (This would be another approach)

Anyway, either way, it would be a two step solution for the user and I cannot think of any one step ways to achieve this.... (I would also love to be surprised otherwise)

[Suvrutt_Gurjar]

Thank you @TeeSee1 for chiming in. I also think enumlist in the Contacts table is the best approach.

I was about to suggest the same approach after knowing the app configuration.

@Mishta_P ,

@TeeSee1 has rightly touched upon the reason your current set up is not working.  I am elaborating it a bit in more detail.

Even though , you have not shared the explicit details of how you have created the "Upcoming" tournaments column in the Contacts table as related records, I guess it is created something like 

Tournaments[Row ID] - [Related Tournaments] 

However in doing so, there is no yet any relationship established between the unregistered tournament records and the parent contact record. It is merely a list without any referencing. I believe that is the reason you are not able to link the contact in your action. I believe,  essentially you are trying to bypass the join table , which is not possible.

By using the enumlist approach  of unregistered tournaments in the Contacts table itself as @TeeSee1  has suggested, one gets over the issue of establishing the reference.q

Here is a similar approach of using enumlist in the parent table, even though for a different requirement of moving multiple records from one parent to another.

Conveniently and simultaneously move several child... - Google Cloud Community

Hope this helps.

[Mishta_P]

@Suvrutt_Gurjar 

Thank you for your response. It's not that anything isn't working. The joins are working perfectly. The issue was referencing the contact, whilst on the contact details screen, but within the tournament based table.

It appears from both your answers that, unfortunately, it does not seem possible to make a reference to the detail view. It is a shame, as I have had similar working within Glide and Bubble, however if this is a technical limitation I have to work with, then that is what it is.

In a way I am pleased that I hadn't missed something obvious.

Many thanks.

[Steve]

You still haven't explained what you want the app to do in plain language. I suspect what you want is doable, but wading through your mess of a description is a non-starter for me.

[Mishta_P]

I have managed to implement a solution that gets 90% of the way around the problem. And for this purpose, given that I am doing someone a favour, 90% is plenty good enough. 

You could have just asked for clarification if you felt you could help, as others have done. But, given the unnecessary bluntness of your response to a new member of the community, giving you that clarification is a non-starter for me. But I do appreciate you taking your time to say that you won't help.