Hello Gliders,
I am currently making an app that would help recruit teams into a football tournament team. I want to ask how I can use Glide to check for some conditions as follows:
- The teams will have 7-12 players and each will be identified with a Name/ID.
- The data of the team will be pulled from a Google sheet, and the data of each player will be pulled from columns ;
Email /Team Name / Name 1 / ID 1 / Name 2 / ID2 / Name 3 / ID 3 /…
1
2
- I am using the array formula to check for duplicates with COUNTIF and if the results return a value > 0, Glide will disable some of the functions of the app for the user.
I like to ask how I can display the duplicate Name/ ID values to the users that will display which Name/ ID is duplicate. Currently, I can only put a hint that says “Found ID Duplicates” and the user will have to find who is the duplicate by themselves.
Much help would be appreciated.
When you say duplicates, I assume that you mean duplicate names within a single team, yes?
Let me first say that this would be much easier to deal with if you restructured your data a bit.
What I would do is just use two columns for each team. One for the team name, and then a second column being a joined list of player IDs. With this approach you could use a multi-select choice component to assign players to teams, and it would be impossible to have duplicates in the first place.
That said, given your existing structure, probably the simplest approach I can think of would be as follows:
- Create a template column that concatenates all the player IDs, and returns a comma separated list.
- Pass that list into a JavaScript column that processes the list and returns the IDs of any that are duplicated.
Thank you, Darren.
- The duplicates can also happen in multiple teams, for example, Team Z, can also have a player from Team A or B.
- The players’ data is collected through Jotform and then passed to Google Sheets. So I cannot make a choice component to assign players to the teams.
As for right now, there are about 70 teams with a total players around 700 players. I am not sure that it will make Glide unresponsive but I will try to implement what you have suggested.
Okay, if you need to check across all teams, then you will need a joined list column to gather the lists for all rows, and then process that.
Question: Do you have a separate table that lists all of the PlayerIDs?
If you do, or can create one, then here is a simpler approach that doesn’t require JavaScript:
- Create a Make Array column in your Teams table that generates an array of PlayerID’s from your 12 PlayerID columns.
- In your Users table, create a multiple relation column that matches the UserID’s with the array of UserIDs in your Teams table.
- Now add a Rollup column that takes a count via the multiple relation.
- Any Users that return a count greater than 1 are duplicated, and if you want to know which teams they are duplicated in, you can use a Joined List column via the same multi-relation to get a list of team names.
1 Like
Following your instructions, I’m not sure if I missed something or might have done something wrong. Here’s a screenshot.
Here is the table structure in the app.
Form Responses : Email / Team Array
Users : Email / lookup Team Array
Relations | Users: Team Array with Form Responses: Team Array (Match Multiples)
It seems there’s nothing to let the rollup function count.
Which table is your first screenshot from? I can’t tell.
Do you mean you have a lookup column in your Users table? That isn’t what I suggested. Please take another look at my instructions and follow them carefully. I tested the solution with some dummy data before posting, so I know it will work if setup correctly.
