Why are answers listed multiple times in our custom report of published answers?
If an answer is listed multiple times in a report, it is because the answer is assigned to multiple products or categories, and the product or category field is listed in the output. If an answer is assigned to multiple products or categories, it will be listed once for each value and combined values it is set to.
For example, if an answer is assigned to product A and B and category 1 and 2. If the report lists the answer ID and the product and category fields in the output, the answer will be listed four times, once for each of the combinations below:
Product A -- Category 1
Product A -- Category 2
Product B -- Category 1
Product B -- Category 2
You can include an expression in your report to list an individual value or to list the word "Multiple" if the answer is assigned to more than one value. The expression counts the number of products, sub-products,categories or sub-categories (depending on how your site is configured).
The expression is similar to the following:
count(if(answers.map_prod_lvlx_id > 1,'Multiple',answers.map_prod_lvlx_id))
Be sure to replace "x" in the expression with the appropriate level of the item you are counting. For example to count the number of sub-products, you would use "2" to use the answers.map_prod_lvl2 field from the database.
Similarly, if you need to count the number of categories or sub-categories, replace "prod" with "cat" so that the correct database field is referenced. For example, to count the number of categories an answer is assigned to, use the answers.map_cat_lvl1 field in the expression.