In my original email to Mark, I wrote, "At the start of the year, I built a fitness app (ArenaStrength) for a client. The app uses user-specific fields (checkboxes) which users check off when they have completed particular workouts. My client just told me that some of the users have duplicate rows in the tab that is used for the profile. She is interested in de-duping the user sheet, but is concerned that users will lose their progress (user-specific checkboxes) and perhaps their profile. The duplicate rows all have the same email address, but the user tab also has a column for RowID, and those RowID’s are different for all the duplicate rows.
Do you have any suggestions on how to de-dupe the user list while ensuring that the user-specific fields are kept? Is this data all bound together by the Row ID, user’s email, or something else? "
Mark replied with the following, “User-specific columns are bound to the user’s email address (and the row IDs in the sheet where they live), so de-duping the user profile rows wouldn’t affect them at all. If you’re not using the row IDs in the user profile sheet in the app for relations or something else, that shouldn’t be a problem, either.”
My follow-up:
Thanks for the reply Mark. Moving the conversation to the community forum as requested. One follow-up question: The sheet used for the user-profile has columns for both email and RowID. Each duplicate row has a different RowID, but the same email address. The client is concerned about the subscribers needing a new PIN AND losing their user-specific data (their progress through the exercise programs, as described above). I believe that, based on your reply, I would have to know which Row ID is associated with the user-specific data to keep that row and preserve the data. Is that correct? If so, how do I know which row ID to keep?
I would think the first row for each duplicated user would be the primary row, but I’m not sure. That largely depends on how you are filtering and sorting data. How many users are affected? Could you just preview as each affected user to see what is contained in each user specific column for that user? Then delete the duplicated row that doesn’t have any values in the user specific columns for the user you are previewing as.
Hi Jeff - There are lots of affected users, so not sure this approach is practical. Since the duplicate users have the same email address, and the only difference in the records is often the Row ID, how would I know which ROW ID I was logging in as?
Hard to picture the exact scenario. Do you have any screenshots? Are you saying there are duplicate user profile rows, which have a RowId, and then that RowID is used in other sheets? I would maybe preview as a few different users, track the row IDs, then see which row id has data. If it’s consistently the first or second row, then you would know which to delete. You could temporarily add a VLOOKUP of the email into the sheet so you could visually see emails instead of rowIDs.
Hi Jeff. This screenshot shows the 2 scenarios that must be de-duped. The first is when multiple rows contain EXACTLY the same data except for the Row ID. The second scenario is when the same email is used for multiple rows, but the first and last names are transposed and there are different Row ID’s.
I suppose I could add the ROW ID field to an admin-view screen and then log-in with different emails to see which ROW ID is associated with the logged-in user. Would you expect it to be consistent (ie, always the first row’s RowID?)
I have a related question. I allow a few of my users to add profiles. But I want to prevent having duplicate profiles as shown in the above screenshot. How can I allow adding a profile but only if it is not an existing user?
Sometimes the staff need to add a new user directly to the app. This works fine except for when they forget to check if the user/email is already in the app. I’m looking for ways to check against the existing profiles to prevent the email address being added again.
You would have to do that outside of the form. Create a user-specific column to hold the email data for checking, then a relation from that column to the already existed email column in the user profiles.
If the relation is not empty, don’t show the form button and vice versa.
Thanks for your help. I couldn’t quite follow that. Instead I’ve added a prompt/warning in the “add user” form along with a choice component that allows them to search for the email. The prompt indicates to cancel the entry if the email already exists. I’ll see what my client thinks about this.
I think I’m still not clear how your data is set up. I assume your screenshot shows data from a user profile sheet? Maybe the first question should be “how did they get duplicates in the first place”?
The way understand how user profiles work in glide is that the signed in user will always attach to the first row that has the matching email address in the user profile sheet. If you are using the RowID for that row in other sheets, then it’s most likely pulling from and using the first matching row to the signed in email.
What I don’t yet understand is how you are placing data in other sheets. Are you writing the RowID from the user profile sheet into your other sheets? Are the duplicate RowIDs also in the other sheets with data? Is this all in one sheet? If so, I wonder why you have user specific columns when you already have a row for each user. User specific fields only make sense to me when multiple users are viewing the same row.
To me user specific column data is attached to an email and the row id is needed for glide to know which row user specific data is attached to (since it’s not stored in the sheet). Otherwise it can only use row position and deleting row would misalign which row user specific data should be part of, so RowIDs keep everything linked on Glide’s back end. So in the end, user specific data is attached to an email, but that user specific data also needs to be attached to a row via RowID.
Depending on how you have everything linked together, maybe it’s also writing data elsewhere in other sheets with the duplicate RowIDs but I don’t really know you app flow and design. That’s why I say to preview as a few of these users, then look at the data editor and see which rows contain the user specific data for each user. If it’s consistently always the first matching row id, then you can delete all of the duplicates after the first row. Again, I don’t have a good visual of how your data is set up and how everything is linked together, so it’s hard to give a good answer.
