Report is returning duplicate records

[Sam P]

Hi all, I have a report that keeps returning duplicate records - one line will be partially complete and the other is complete.  Can anyone see where I've gone wrong? 

Join1:

Join2:

Filter:

Result:

Thanks in advance

[SamS]

Hi @Sam P,

Once you start linking to a table with more than a single record linked to it, then you get that behaviour. With a "JOIN" you are literally asking for every instance where the IDs match, so if a request has 15 questions against it, then you would see 15 lines with the same/repeated request data and only differing in the parts with the questions and answers.

The Questions table is the likely culprit here. I suspect that two of your questions contain a question with the blanked out phrase in the question. Might I suggest that you filter on the form ID and Question ID instead? They would match the form and fields names set in the Intelligent Capture.

IF you want to have BOTH those answered questions ON THE SAME LINE as the call data, then that is also possible. For that you will need to use a LEFT JOIN to the questions table FOR EACH question/answer you wish to show.

That being said, there is currently a minor bug(*) in the way fields can be selected to show in the end-result with the LEFT JOIN method. IT should be fixed within 2 weeks.

(*) it is already in line for deployment, so expect it working within a fortnight.