Peter

You should be able to create saved searched for a custom entity created using the Advanced Customization Wizard. The saved search if created by a system administrator or by a 'info manager' can be for the 'CurrentUser' option.

Saved searches can then be used as a data source for list gadgets within the Interactive Dashboard

Jeff

If I do an advanced find I can choose multiple fields and "OR" the criteria together (no currentuser option) but in the saved search the fields (roles) are all "AND" joins so the only way the Jobs show up in the results is if the user has all the roles on that job.

Peter

List Gadgets can use saved 'Advanced Find' definitions as data sources.

community.sagecrm.com/.../using-an-advanced-find-as-a-data-source-for-a-list-gadget.aspx

Hi,

I cannot replicate what you are saying is happening, here is what I am doing:

1. Create a report, checking the 'Current User' and 'Advanced Find':

2. Build the advanced find with an 'OR' operand:

3. Run the report, and it works as expected, giving the SQL of:

SELECT pers_firstname,pers_lastname,pers_primaryuserid
FROM vreportperson WITH (NOLOCK)
WHERE ((pers_firstname LIKE N'Toby%' ESCAPE '|') OR (pers_lastname LIKE N'Prince%' ESCAPE '|') )
AND Pers_PrimaryUserId = 1
ORDER BY pers_firstname ASC,pers_lastname ASC,pers_primaryuserid ASC

So if I have interpreted what you are after correctly, this type of report will cover what you need, and can then be added as a gadget to a dashboard:

Got you, sorry I missed that.

One way that may work, is use a view with a UNION, aliasing the columns as the current user field, so for example against the person it is:

pers_primaryuserId

I could create a view along the lines of:

SELECT DISTINCT pers_firstname,pers_lastname,pers_primaryuserId
FROM (
SELECT pers_firstname,pers_lastname,pers_primaryuserId
FROM Person

UNION ALL

SELECT pers_firstname,pers_lastname,pers_dcl_primarypersonId [pers_primaryuserId]
FROM Person) as a

So to mock this up, the Account Manager for one of my records is 1, and the other is not, the pers_dcl_primarypersonId is 1, but for the other it is not, and then for one record both are set to 1, the output of the report is:

The data in the person table looks like:

So you can see, the pers_primaryuserId is not 1 (as would be the current user), yet all the Tobys are returned, and that means the party can really get started.

The full view would look like:

So you can see it creates a DISTINCT row based on all fields, so the report would work with the current user if they were user ID 1, or user ID 11.

Hope that helps.

Also some super stupid SQL in there, no need to use a UNION ALL then a distinct, sorry, you can simply use:

SELECT pers_firstname,pers_lastname,pers_primaryuserId

FROM Person

UNION

SELECT pers_firstname,pers_lastname,pers_dcl_primarypersonId [pers_primaryuserId]

FROM Person

My bad, one day I will learn not to be an idiot, but for the time being it is stuck with me.

Yes List gadgets can used saved 'Advanced Find' definitions but 'Advanced Find' doesn't allow me to select 'Current User'. Is there a way to edit the saved search definition in the database to change it from logical "AND"s to "OR"s?

Hi Toby,

The Job entity has several role fields (Owning Partner, Principal, Manager) that are user fields. So Toby could be a partner on some jobs, a principal on other jobs and a manager on still some other jobs. Peter could also be a partner, manager, or principal on the jobs. What I want is a single query that I put into a list gadget that would show Toby all the jobs that he is connected to and for that same gadget show Peter all the jobs that he is connected to regardless of what his connection is (partner, principal or manager). In reality there are actually five roles and a variable list of role descriptions but I simplified it for explanation purposes.

Depending on the user it would generate something like:

For the system administrator:

SELECT Job_description,Job_DueDate

FROM vreportjobs WITH (NOLOCK)

WHERE Job_PrimaryUserId = 1 or Job_SecondaryUserId = 1 or Job_TertiaryUserId = 1

If the user_userId of current user were 7 the query would need to be something like this:

SELECT Job_description,Job_DueDate

FROM vreportjobs WITH (NOLOCK)

WHERE Job_PrimaryUserId = 7 or Job_SecondaryUserId = 7 or Job_TertiaryUserId = 7