Using an Advanced Find as a Data source for a List Gadget

2 minute read time.

Imagine you are a user with no administration rights. Just like 'Peter Johnson' in the demo database. In the demo system this user has the right to see cases, so he can search for them. BUT he can't change the system in any way. Now imagine he needs to find communications that are linked to a cases.

He is a sales person and he might reasonably want to know what support has been saying to his customers and prospects.

Any cases, communications, company and person information that he can read is limited by his security rights. So the problem for him is not being allowed access but rather not knowing what has been said.

You might think that he can just search for communcations linked to cases, but as you can see from the default search screen shown below, there is no way of saying "show me communications linked to cases for companies where I am the account manager."

Of course he could ask his System Administrator to change the search screen but with Sage CRM he doesn't have to. He can configure his system in his way and have the information immediately available to him.

The user can use the Advanced Find feature to get to the information they need. The Advanced Find is available to all users by default and is under the regular find menu. You can see below the user has created their search based on the communications and has retrieved all the communications that are linked to a case and where the company is assigned to Peter Johnson.

Note: Using the clause "Communication : Communication Case ID <>" with the Value field left blank allows the search to find all not null records. The underlying SQL that the Advanced Find uses is


SELECT * FROM vSearchListCommunication
WHERE COALESCE(comm_caseid, 0) <> 0 AND 
COALESCE(comp_primaryuserid, 0) = 3

This Advanced Find definition has been saved and has been called "My Accounts' Support Issue Comms".

Advanced Find definitions are one of the possible data sources used by List Gadgets.

The 4 types of data source a List Gadget can use are

  • Dynamic Groups
  • Reports Defintions
  • Saved Searches
  • Advanced Find Defintions

The user can now create a new gadget on their Interactive Dashboard.

The Gadget needs to be based on Communications.

In my example I have only Reports and the Advanced Find defined earlier available as Data Sources. The user can select the Advanced Find "My Accounts' Support Issue Comms".

The user can select the fields they want displayed.

Then decide the Drill Down action and Default Action associated with the list.

The Gadget just needs a name before saving.

And the data is displayed.

This Gadget like any other list gadget can be linked to others, it can drill down to the underlying data source, and its fields can be rearranged in any way the user wants; all without having to get a System Administrator to do it for them. By using Advanced Find and the Interactive Dashboard, data that would otherwise be hard to reach for users can be driven to their screens to allow them to see the information they require in the form they want and to easily carry out the business actions they need to do.

  • Hi,
    I am not sure if commenting here is still relevant but just in case somebody comes looking for help on similar issue:
    You can check if it is because of the AdFi_SQLText column? You can check that by running the query text and see if the query is broken and not returning a sensible outcome. Reason could be any. All columns for the Advanced Finds table record could be checked to narrow down the problem.
    Hope it helps!
    Kind Regards,
    Seema

  • Hi Jeff,

    We have problem with create new list gadgets using saved advanced find as data source. We can use saved search as list datagets data source without any problem. But if an user (any user) in the system created a saved advanced find based on an entity (any one of available entities in the find dropdown list), then any other users of the system will get the "Error loading data" error when they try to create a new list gadgets based on that entity. the error occurs in the step 2 of 5 (Select the data on which you wish to base your gadget). As soon as that saved advanced find is deleted, the error will disappear. So, we are not be able to use the saved advanced find as data source of list gadgets which is very import for our users. We did everything we could from looking at the log files to googling internet trying to find what's going on, but no luck and have no clue for what to do next.

    Can you help us figure out what is the cause of the problem or give us some advises for how or where to dig out the problem?

    Thanks.