multiple choice field based on field in another table causes duplicate values from a unique record table.

  • 1
  • 1
  • Question
  • Updated 3 years ago
  • Answered

 A one-to-many relationship exists between two tables in our database.  A payer record is created in our Payer table that references the specific office in the office table of this application.  When a user creates a payer record, there is a drop down based on the office table to select the office (from this other table with unique offices).  In doing this for a quite a long time however, our payer records have multiple records with the same office name, but somewhere behind the scenes  it represents a different "office".  I have tried everything in my power to figure out where the duplication occurs. But if you report out by office, there will be as many as 3 different listings for the same office name (the name is exactly the same).  None of the Payers are duplicated, just multiple office names for the payers that are all the same.  Any help is appreciated.

Photo of Janet

Janet

  • 0 Points

Posted 3 years ago

  • 1
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 370 Points 250 badge 2x thumb
Is this Office dropdown - a Text - Multiple Choice field, or is this the Related Office (or Office Name) field from the relationship?

If it's the Related Office field - then there shouldn't be any duplicates (since there are only unique records in the Office table).


If it's a Text - Multiple Choice field - it's possible that somehow some very similar Office names went in there - e.g. Office names with an extra space after, etc.  (so that Office Names aren't exactly the same).


So if it's clear that in fact these are all the same Office - you can grid edit (or otherwise mass edit) those listings, to use the correct single Office value again.
Photo of Janet

Janet

  • 0 Points
Yes - the field that is "duplicating" is the "Related Office" field (a text field) in the relationship to this offices table.  I have grid edited this when I catch it, but I cannot understand why it happens.  The Office table does have unique records based on the office name field,  When I look at the difference "versions" (I built a report to detect the differences), the "new" versions of the same office name look to have a carriage return or something displacing/creating empty space at the end of the original name.  very odd.  Thanks for your help.
Photo of Xavier Fan

Xavier Fan, Champion

  • 370 Points 250 badge 2x thumb
Great - glad you found the duplicates.  

I think the next place to look - is how the records are being added.  

If the Payer records are being added one by one, and you're selecting the Office from a dropdown - then I wouldn't expect any of these Office "duplicates" (since you're selecting the one correct value from a dropdown).

But if the Payer records are being imported, or grid edited - then it's possible that in the cut and paste (e.g. from Excel), or in the original import, there are Office names with the space / carriage return at the end.