Jump to content
  • 0
Sign in to follow this  
viterbo

Counting Search Hits Across Fields On A Single Record

Question

Hi Guys, 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.
Thanks
V.

Share this post


Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

You need to create an authentication so user can login and you can get access to user color choice, add a calculated field to your page and use this code


 

(CASE WHEN '[@authfield:Color1]' = '[@field:Color1]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color1]' = '[@field:Color2]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color1]' = '[@field:Color3]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color1]' = '[@field:Color4]' THEN 1 ELSE 0 END)
+

(CASE WHEN '[@authfield:Color2]' = '[@field:Color1]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color2]' = '[@field:Color2]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color2]' = '[@field:Color3]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color2]' = '[@field:Color4]' THEN 1 ELSE 0 END)
+
(CASE WHEN '[@authfield:Color3]' = '[@field:Color1]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color3]' = '[@field:Color2]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color3]' = '[@field:Color3]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color3]' = '[@field:Color4]' THEN 1 ELSE 0 END)
+
(CASE WHEN '[@authfield:Color4]' = '[@field:Color1]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color4]' = '[@field:Color2]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color4]' = '[@field:Color3]' THEN 1 ELSE 0 END) + (CASE WHEN '[@authfield:Color4]' = '[@field:Color4]' THEN 1 ELSE 0 END)

You would then see:

 

 

If you do not want to see the user it self in filtering you can add name not equal and receive from authentication

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...