Search Issue on multiple fields within the COmpany entity

This question has suggested answer(s)

Hi.

I'm hoping someone can help me with this search issue.

I have created 6 new fields within the Company entity.
All fields are created the same except for the column name being different.
I also have all fields pointing to the same Lookup Family:

Entry Type: Selection
Column Name: comp_allcomm, comp_comm1, comp_comm2, comp_comm3, comp_comm4 and comp_comm5 
Caption: Commodities
Lookup Family: comp_allcomm

Each field is populated with a product selected from the comp_allcomm field.

So, I thought I could just add the comp_allcomm field to the company search screen (which I did) and when I search on it (ie. comp_allcomm), it would return the companies associated to the product found within the fields above.

- Unfortunately, I'm wrong with this methodology as nothing is returned.-

However, if I search on comp_comm1, it does return a value but only those who have been selected within this field.

So, is it possible to search on 5 fields at once from 1 designated field? I thought I was doing this by selecting the data from the lookup family.
Let me know.

Thanks,

Steph

All Replies
  • 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.

    Sage CRM specialist and all round fan! Please feel free to follow me on twitter @dannycrm

  • 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?

    Sage CRM specialist and all round fan! Please feel free to follow me on twitter @dannycrm