+ Start a Discussion

Accessing user object from another object


First, the scenario:

We want to be able to group accounts, opportunities, leads and contacts by region. For simplicity let's only focus on the account. Here is my first attempt which kept us going for some time (but will very soon not be enough):

  • Create custom region field on account of type formula
  • Create huge formula that asks if ownerid = "ukRepOwnerId" then region = "UK" or if ownerid = "usRepOwnerId" then region = "US" else region = "Scandinavia"
Now, this worked fine for some time. However, it has 2 major flaws:

  1. It needs updating every time we add a new user
  2. With enough users the formula grows too big for SFDC to handle (it basically exceeds the limit of allowed characters)
So, I thought I could just create a custom field on the user object (not remembering I had already tried this some time back :smileyindifferent). Creating the field is not a problem but accessing the field from e.g. a formula field on account seems to be a big problem. I only seem to be able to access the usual account fields and various information about the user that is currently logged in.

Is there some way for me (e.g. using triggers) to achieve this?

/Søren Nødskov Hansen

The issue I come across is that I can't make a formula field = to a picklist field. So if I had picklist A,B,C,D and wanted another field = (whatever the user chose in the picklist) I can't seem to do this. If you could do this, I think it would solve your problems. (But if you can't, here is what you can try):

If you create a custom field of type Text on user, you can add the country.

Then you can add a lookup field to accounts = User

You could create a workflow that sets the lookup field to account owner when the record is saved. so the rep doesn't have to add this every time.

Then, you can create your custom region text field under account as a Formula and have it = Userfield Region.

When you add the lookup value to Account this should enable you to see your user custom fields.

Let me know if this works...



I didn't know that adding a lookup from account to user would open up for accessing the user fields on the account object. A very useful piece of information!

When creating the workflow with a field update it seems I cannot choose account owner but only a specific user so it would seem I can't use a workflow to set the user field on the account. My next thought was to create a validation rule forcing the rep to select the appropriate user but then I might as well force the rep to select the appropriate region.

If you have any ideas to set the user lookup to the account owner automatically it would be greatly appreciated.

Once again, thanks for all your help so far!

/Søren Nødskov Hansen

Message Edited by noedskov on 10-24-2008 01:06 AM

I think I have a solution.

I tested it out and it seemed to work.

I got rid of the user lookup field on account.

1) Went to User and added a field called region (text field, not picklist) Typed the region

2) Went to account and added a custom formula field called region (formula=$User.region__c)

I could see my custom user fields (they were not seperated by Standard and Custom like most objects are. They were all merged into one, so I missed it at first)

3) Tested it out, added a new account and the field immediately filled out the region.

I don't know if it is because I created the User lookup first and then deleted it, that it somehow let me see the user custom fields, or if I just overlooked the fields because they are not separated out.
This would actually help me a lot (right now not very many sales reps) so it is easy to assign, but in the future it will grow.
Let me know if this still doesn't work. I have it working on my end...
I convinced my boss that we could simply define region on a lead based on the country (we have a validation rule preventing you from saving a lead with no country).

When converting the lead the country is passed over to the account and here we have the same formula defining the account's region based on the country.

Finally, on opportunity I created one last formula simply pulling the region field from account.

/Søren Nødskov Hansen