Create Report of Accounts with No Quotes - Sugar Pro v5.0

[els565]

I have a large database of accounts and I'd like to find the ones that have no quote attached to them.

I've been able to export info and find them in Excel but I need that list in Sugar. I need to change the ownership of every account without a quote and I don't want to go one-by-one.

I'm using the Sugar Pro 5.0 custom reporting feature and I can't figure this one out.

Any ideas?

[sugarcane]

Hello els565,

I have a large database of accounts and I'd like to find the ones that have no quote attached to them.

you could technically create an Account based report which has an optional relationship to the Quotes module. This report would then list all accounts, regardless of whether or not they have a Quote attached. You could then sort by the Quote Subject field to view all the accounts with no Quote. However, this will not resolve your issue of mass updating the Assign to field for the Accounts with no Quotes.

If you upgrade to 5.1, there is an import and update feature, which would allow you to update the fields for accounts on your csv (based on the ID field). However, this is based on the ID field, which is not available in the reporting module.

The best way to do this would be to run a script on your database, which will update the assigned user field for all accounts that have no quote. You could do something like this:

Update accounts
Set accounts.assigned_user_id = "[the ID of the user you want to assign the accounts to]"
where
accounts.id not in
(select quotes_accounts.account_id from quotes_accounts where quotes_accounts.deleted=0)

[els565]

Thanks so much for your reply!

This worked!

[christianknoll]

The report on Accounts without Quotes is Standard Feature in our KINAMU reporter (http://www.sugarforge.org/projects/kinamureporter/). the Mass Update feature is on the todo list since one of our customers already asked for it.

christian.

http://www.kinamu.com

I have a large database of accounts and I'd like to find the ones that have no quote attached to them.

I've been able to export info and find them in Excel but I need that list in Sugar. I need to change the ownership of every account without a quote and I don't want to go one-by-one.

I'm using the Sugar Pro 5.0 custom reporting feature and I can't figure this one out.

Any ideas?