I created an advance find with those 5 fields

Here is the SQL:

WHERE ((( comp_comm1 = N'Tomatoes')) OR

(( comp_comm2 = N'Tomatoes')) OR

(( comp_comm3 = N'Tomatoes')) OR

(( comp_comm4 = N'Tomatoes')) OR

(( comp_comm5 = N'Tomatoes'))

)

I checked and it works fine.

However, I was hoping for a method where you add a designated field to the company search screen to return these values.

Right now, I need to replace the 5 comp_comm fields with the same value... There's got to be an easier way.

Let me know if I should be adding custom content to the CompanySearchBox (?)

The Keyword Search within the company screen uses the view vSearchListCompany and this includes all the columns of the company table so I would imagine that just using 'Tomatoes' in the Keyword search box with other criteria in the main fields will find the data records you need.

Hi Jeff.

Thanks for your quick response. Much appreciated.

I just tried what you indicated and it is returning the wrong amount of records.

When I search on Tomatoes through the advance (SQL) find I created above it returned 32 records with Tomatoes.

With the Keyword Search function, it returned only 2 records.

---

Not sure why it didn't return 32... I also checked the vSearchListCompany and it has the required fields.

---

I think my best bet is to create a new entity.

And then create a Search Select Advanced field.

What do you think?

Steph

Steph

Before you go down the route of changing the datamodel I think you need to spend a little more time looking at what is going on as you query this table.

You want to be able search for records that have the value 'Tomato' in any one of 5 fields in a record. You can trace the actually SQL issues with the SQL logs and the database trace facility - what is it actually sending?

Hi Jeff.

Ok - I went into the logs and the system logging level appropriately.

However, I have no errors appearing.

So, I'm going to setup a trace and let you know what it is doing exactly.

Get back to you shortly.

Steph

Ok.

Using the keyword search isn't searching on those fields.

It appears to be only pulling on the company name field and some other fields within the company table.

So, why is it not working?

Steph

Steph

I've asked some colleagues for some clarification. As soon as a I get an answer I'll share it.

Regards

Hi Jeff.

I'm wondering if it has to do with the fact that these fields are of type selection fields?

Perhaps the keyword search function is unable to pull this data.

However, I'll await your answer.

---

Also, I tried tracing and nothing is appearing in the SQL log.

I set it at 5 (in terms of log setting) - waited a few minutes - ran the query - and nothing is appearing in this log in terms of my keyword search.

What is the trick to see the keyword search query?

Steph

You could turn the 5 fields into 1 field and do a % search on it.

I.e. comp_comm1 + comp_comm2 + comm_comm3 + comp_comm4 + comp_comm5 as comp_commall

The show comp_commall on the search screen and %tomato in the field.

Hi Daniel.

ok - this is an interesting an easy way of doing it.

---

Oddly, when I created my new search box definition, the Associated List (vCompCommList) isn't being pulled - its pulling the Company table fields instead of my list.

I'm running CRM 2018 R2 - is this new where you cannot pull on an associated list to select fields from a view??

ok - this is really odd.

I refresh my meta - and it didn't work.

However, I re-created the block and my list appeared.

--

Here is the final outcome:

I created a new custom search page (called commodities).

I'm using the technique that Daniel suggested above. Its too bad that there wasn't away to just use the keyword function.

Thanks for all your help.

Steph

Is both the screen and the list using the same view?