Current User Reference for Reports

[gkeyes]

Using Sugar Version 5.0.0d (Build 3235)
Using Selects in ZuckerReports 1.8

My Code:
SELECT leads.first_name, leads.last_name, leads.phone_work, leads.phone_mobile, leads.date_entered, leads.date_modified, leads.description, leads.account_name
FROM leads
WHERE leads.deleted = 0
AND leads.assigned_user_id=1
ORDER BY leads.date_entered;

What it does:
The above will pull records from leads when the WHERE is TRUE, the problem is I do not want to create a report for EACH user.

What I want it to do:
I want to replace the 1 in the WHERE statement with a variable that contains the ID value of the logged in user.
Any suggestions?

[gkeyes]

Any suggrestions on a this?

Basic idea is I need to setup reports to pull ONLY records for the currently logged in users.

[Angel]

Where are you doing this? That might help us give you some better guidance.

i.e. Built in reports modules? Zucker? ???

EDIT: Nevermind, just saw the bit about Zucker.

Not sure you can do that with Zucker, as you'd need to be able to reference a web variable within the SQL query.

[gkeyes]

Reports and Query Templates

I so see a reference to the right of the query box which says.
The following placeholders are supported as well:
$SUGAR_USER_ID - contains the ID of the currently logged on user
$SUGAR_USER_NAME - contains the name of the currently logged on user
$SUGAR_SESSION_ID - contains the ID of the current session

It seemed simple enough, but I am not able to reference $Sugar_USER_ID in the "WHERE" statement.
My programming knowledge is not great, but this seemed simple.

[Angel]

I am familiar enough with Zucker, but it sounds like those references are what you need.

Maybe it is just a syntax problem.

Do you get an error message when you try to use $SUGAR_USER_ID?

Can you post the query with the $SUGAR_USER_ID reference in it?

[gkeyes]

My CODE:
SELECT leads.first_name, leads.last_name, leads.phone_work, leads.phone_mobile, leads.date_entered, leads.date_modified, leads.description, leads.account_name
FROM leads, users
WHERE leads.deleted = 0
AND leads.assigned_user_id=$sugar_user_id
ORDER BY leads.date_entered;



OUTPUT ERROR
Output


Error while running Report Query: SELECT leads.first_name, leads.last_name, leads.phone_work, leads.phone_mobile, leads.date_entered, leads.date_modified, leads.description, leads.account_name FROM leads, users WHERE leads.deleted = 0 AND leads.assigned_user_id=$sugar_user_id ORDER BY leads.date_entered;
MySQL error :

*************************
Not sure I am using it right, I do appreaciate your help on this, I have an appointment to attend, but I will be following this.

[Angel]

Try changing $sugar_user_id to '$sugar_user_id' (with the single quote)

[gkeyes]

Well it ran, but no output., just column headers in the HTML output

I am logged on with Admin which has an id of 1, I confirmed I do indeed have a lead created by ADMIN, but no output, so I assumming it is looking at '$sugar_user_id' as TEXT and not finding a match.

My meeting is starting, I will check back later,..... (these meetings take a long time)

[Angel]

Try '{$sugar_user_id}'

Other than that, take a look at the Zucker documentation to see how it is one is supposed to reference those fields. Per my earlier message, I am not familiar enough with Zucker, so I don't know what the appropriate syntax is.

[gkeyes]

Ok Tried that, ran, but no output, I wonder if I am accessing the proper DB in my FROM statement, although I would think this is created and held as a system var.

I looked at the documentation and there is no reference to this. I saw it in the program itself.

I will keep looking around and trying differnet things.

Thanks for your help.