I have a form that allows you to select multiple names using the choice component. When the form is submitted, the names are added to a table column, separated by commas, like this: “John,Amy,Hugo,Ivan,Sarah”.
I would like to know how to separate each name into its own row in another table upon form submission so that each name appears on a separate row. Additionally, I would like to link the two tables using a form submission ID.
Could you please advise me on how to accomplish this?
You must create a custom form, split the emails, then create the list. To add multiple rows is only possible if your Players list is fixed. Then you can create a custom action and repeat add a row for each player… if is not fixed… You need to create a script (not possible in the Glide table, only Google Sheets)… that will run add row loop depending on how many players are in the split value.
I’m curious about this. What is your use case when you want them in separate rows? Do you want to enter different info for each player later on?
There is a total cost associated with the players. e.g £100. For all the players selected (in this example we chose 5), the cost will need to be divided by 5. £100 / 5 = £20 per player.
However, the players and the number of players can change every form submission. i.e Ivan may not participate in the following week, so will not be selected in the form. Or in another week we may have 10 players.
The aim is to create a ledger of costs (another table) associated with a player.
Okay, so I don’t think you need to create all those extra rows. You can calculate what you need as follows:
The Players choice component will write a comma separated list of values.
Add a split text column that targets that column to create an array.
Do a rollup through the array column to count the number of selected players.
Now use a math column to calculate the cost per player for that row.
Now back in your Players table, create a multiple relation column that matches the Player with the array column in the other table.
Now to get a total cost per player, do a rollup->sum through that relation.
Thanks, Darren! With your guidance I feel like I am getting much closer to the solution!
Here is what I’ve done from your instructions, so far:
Session table
Players table
4. I created a relation column where I used multiple-match to find all the matching arrays (I feel like I have done this wrong)
5. I created a roll-up column which sums the cost per player (I feel like I have done this part wrong)
Issue: the value in the roll-up is that it’s showing -40 for all players. This is clearly incorrect…
Where have I gone wrong? could you help me fix the steps I’ve taken?
Check your rollup column in the player’s table. It should be using the relation. I’m guessing that you are pointing it directly to the table instead of the relation.
Everything else looks correct to me.
I understand the concept you and Darren are trying to explain, but I can’t execute it correctly!
Here is my player’s table setup for both columns. Where have I gone wrong?
The relation is good, but your rollup is pointing to the entire sessions table. That’s why you get the same total for all players. Re-select the column in your rollup again, but choose the New Column relation instead of the sessions table as the source for your rollup.
Once you get that working (which you will do, if you follow Jeff’s advice), then I have a suggestion for you.
Add a RowID column to your Players table, and use that as a PlayerID.
Then configure your choice component to write a list of PlayerIDs in your Sessions table, instead of names. And then use PlayerID instead of Player name to create the relation.
This is a more robust approach that is much less likely to cause any problems. RowIDs will never change, but names can and do change. So using names to create relations is rarely a good idea, as they will break at some stage.
