Counting Search Hits Across Fields On A Single Record

[munkitkat]

I hope somebody has an idea for this. I'm stuck creating the report, step 3 below.

 

1) user Bob insert its color preferences on a search web form:

 

Name    color1    color2     color3    color4

Bob       red         blue      yellow    black

 

 

2) it is compared against a table I created with information from other users that also like colors

 

Name    color1    color2        color3      color4

John      red         white        orange     brown

mary      purple    magenta    green      grey

Elen       white     green        red          blue

 

 

3) I need to give Bob the best match with the other users of the database. On the report I search by "red" OR "blue" OR "yellow" OR "black" at the same time

 

Table of best matches for Bob (this would be the report)

 

Name    "Hits on search"

Elen       2

John      1

 

I hope to be able to do this with a calculated field on my report ("hits on search", on the example) and sort by it. Somebody suggested to use COUNT, but that counts vertically across users, if I understood correctly: (times "red" showed up under the color1 column, for example)

 

Any idea would be appreciated.

[aam82]

You can't use Caspio to filter your results based on a calculation. So your Hits on search must always be a table or view of ALL USERS, which you can then pre-sort by your calculated field.

 

As for the calculation, yes you can use COUNT, and you can SUM your COUNT of each color match. I think it would be:

 

select sum(

select count(1) from surveyResponses where color1=target.[@field:colorToMatch], select count(1) from surveyResponses where color2=target.[@field:colorToMatch], etc.

)

 

You could also weight your results, maybe something like if color1 is a match, then add 100 to your count, color2 add 75 to your count, color3 add 50 to your count..., and sum the whole thing.

 

select sum(

select sum(

(100, select count(1) from surveyResponses where color1=target.[@field:colorToMatch]),

select sum(

(75, select count(1) from surveyResponses where color2=target.[@field:colorToMatch]),

etc...

)

)

 

didn't test these formulas