Unique foreign key duplicated in a report?!

Oh, the perils of reporting.

Did you know that adding a field to a report can cause duplicate results? This can happen if one of the selected fields has more than one value per record. The great example of this is the problem of label/value pairs. When custom fields are added to a record, a label and a value is created for each custom field. When you display the list of items with standard fields, there are no duplicate items. However, if you want to see a list of all items with custom fields, each custom field forces a new row in the report.

• Item 1 | Custom Label 1 | Value
• Item 1 | Custom Label 2 | Value
• Item 2 | Custom Label 1 | Value

What I didn’t realize is that this can happen not only with label/value pairs, but it can also happen with any field that could have multiple values per record. For example, if a sales transaction has two salespeople on it for some reason.

• Unique Transaction ID 001: Salesperson 1
• Unique Transaction ID 001: Salesperson 2

One solution is to remove the offending fields from the original report. A second solution would be to hack a unique key by appending [Transaction ID]&[Salesperson].