Hoping someone can offer some help . . . we have a date/time field that tracks the "Last Status Change" for leads.  When a Lead Status is updated, a trigger updates the date/time to "now" and then we have a formula field that calculates the number of days between now and that date/time of the last change.  However, we only calculate that value for leads that have not been converted and where they don't have a Closed status.  The current formula is below and it works great.

IF(AND(NOT( ISNULL( Date_Time_Last_Status_Change__c )),NOT(IsConverted),NOT(ISPICKVAL(Status,"7. Closed"))), (NOW()- Date_Time_Last_Status_Change__c ),NULL)

Now . . . here's the question.  There is some talk about changing it to try and only show workdays since the change--to not calculate weekends.  I have the following formula to show the workdays.  BUT . . . how would I combine the conditions in the formula above and the formula below so I only get workdays since the date/time of change if the lead still meets the conditions of not being converted or closed???

CASE(MOD(  DATEVALUE(Date_Time_Last_Status_Change__c) - DATE(1985,6,24),7),

  0 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
  1 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
  2 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
  3 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
  4 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
  5 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c),7),1,0,2,1,3,2,4,3,5,4,6,5,0),
  6 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
  999)
  +
  (FLOOR(( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) )/7)*5)

Looking for a strong SFDC administrator who is also able to craft solutions using tools that go beyond SFDC configurations (i.e. Apex, VisualForce Pages, HTML) while keeping focus on enabling the sales organization to be effective.

Please reference the information and/or apply at the link below:

http://www.polycom.com/company/careers/career_search.html

Requisition # 285BR

Hello . . . hoping someone can provide some suggesttions to work around an issue I'm having.  Basically, what I need to do is to have a field on an opportunity that can display a value from a field on the Opportunity Owner's SFDC user record.   I will need that field for a few different things like reporting and also to be used for routing in a approval process.  The issue is that I would expect that user record field to be readily available through a formula.  But it's not. Each SFDC user has a "User Theater" field on their user record.  This is the field I need to display/use on the opportunity.

If I try to create a formula field on the opportunity, I get to the "insert field" section and when I drill down I can't get to the opportunity owner's user record.  The only Owner field I can get to is the Owner ID.  I need to use a field on the opportunity owner's user record. 

I can get to the "Created By" user record for the field I need.  I can also get to the "Last Modified By" user record for the field I need . . . so why can't I get to the actual opportunity owner's user record for the field???  Created by and Last Modified by in many cases may not provide the valid info--I really need it to be based on the opportunity owner's info.

Is there any way at all to make this available on the opportunity?  I'm not a developer . . . I can't write triggers.  I'm hoiping there is a way to get to this data that is doable for me to implement.  Any assistance would be great!

I'm struggling with another validation rule.  We have a picklist where the sales reps indicate if it's a standard discount or not.  Then there is a multi-select picklist where they indicate the particular type of discount they are asking for.  I want to have a validation rule that says "If the Standard Discount field = Yes, then the Discount Type field can't have certain options selected.  I have figured out how to do a rule to error out checking for one value in the multi-select list.  But I can't seem to figure out how to check multiple options with one rule (I'd rather not have 10 separate rules if it can be done with one).

Here is my example which successfully creates an error if the Stadard Discount = Yes and then the Discount type includes "Rebate":

OR(
AND(ISPICKVAL( Standard_Discount__c , "Yes"),
INCLUDES(Discount_Type__c, "Rebate")))

So how do I take this rule and give an error if the Discount Type also includes "Trade In" or "Certification" or "Credit Return" etc.?

Could use some help . . . I created some validation rules to impose certain restrictions on a discount request functionality we have.  Works great, when someone plugs in an amount of $10k or more, the validation rule triggers and stops the save. 

The problem is that we have multicurrency enabled--I was expecting the validation rule to catch the conversion rate and to prevent someone from entering what would be over the $10k USD amount . . . that doesn't happen!!!  If I switch the currency on the record to GBP and put in $9000, the record saves.  It only errors when I hit the 10000 value regardless of the currency (and 9999 GBP is way more than $9999 USD).  The formula I am using is below.

AND(ISPICKVAL( Is_this_a_10k_10percent_NST__c , "Yes"),Hardware_Incremental_DiscountUSD__c >=10000)

This is a HUGE problem . . . I need to have this rolled out by Monday and if the system is letting larger GBP/EUR discounts run through without stopping them it will be a disaster.  How do you get a validation rule to take multicurrency into account???  Or can it?

Could use a little help here from the masters on this board . . . we have a discount approval process that uses an Object related to Opportunities.  Our order management folks really want the Opportunity Owner's name to be reflected on the Object itself (Approval Request Form).  Looking at the Opportunity itself is apparently too hard or requires extra steps.  Whatever!

Anyway . . . seems like it should be an easy thing to do but all I have been able to do is create a formula field that can pull in the Opportunity "Owner ID"--which means nothing to the people who need to see it.  I really need a field that can automatically show the actual Opportunity Owner Name in text on the Approval Form so order management can see "Bob Smith" instead of "00530000000diZm".

I'm sure there is probably something simple I am missing.  But how in the world do I get the name to show up in a field on that Approval Form?  Is there a formula function that "translates" the ID and can do that?  I'm pretty stumped.

We have a multiselect field where we can note "Special Designations" for Accounts . . . can be things like Fortune 500 or other special groups we want to associate the Account to without having tons of check boxes.  We are also doing some cleanup/lockdown on some specially designated Accounts and I want to restrict users (except admins) from changing the Account Name if a particular value is selected in that Special Designations multiselect list--lets say that special designation is Fortune 500.  How would that validation rule be written?  I believe that multiselect lists can be included in validation rules now but I'm not sure how to actually do it.

I tried the following but I get an error trying to reference the multiselect picklist.  But I am crummy with formulas so not sure what the solution is.  Any suggestions would be gratefully welcomed!

AND( IF($User.ProfileId <>"00e70000000wI69", true, false),
 IF(Special_Designations__c,"Fortune 500", false), IF( ISCHANGED( Name ), true, false) )

Is there any way to have a formula field that would do a simple list of the product names included on an opportunity?  Example: an opportunity has Widget A, Widget B and Widget C listed in opportunity products.  Would there be a way to populate a field through a formula or workflow rule just to show those product names?

The reason is that I am getting tons of questions for reports based on opportunties that do or do not have individual products or combinations of products.  SFDC as far as I can tell does not really do this (like trying to get a report that will exlude ALL opportunities that contain Widget C . . . if you exclude Widget C in a filter you still get opportunities that may have Widget A, Widget B and Widget C--but it just won't show the Widjet C rows).

If there is a way to "summarize" product names in a text field then I could see a way to do this.  Is there any hope?  Or another way to try and do this?

Back for some formula field assistance.  I am working with our IT team to set up a hierarchy structure on SFDC User records so they can trigger some dashboards off that info.  So I went to create the fields that they need . . . I have lookup fields on a user record that can be used to look up other SFDC users and put them into a spot on the hierarchy.  Those lookups bring back other data from that "manager user" record and shows them on the sales rep's user record (So on Sales Rep #1's user record, there is a lookup for Manager #1 (2, 3, 4, etc) and by selecting that person it returns data for that manager and puts it on the sales rep record for reference).  Hope that makes sense.  Anyway, it all works fine EXCEPT for one thing.  I want to have a field that pulls in another Manager field--but it's a picklist on the Manager User record.  And I get syntax errors when I try to do a formula field on the Sales Rep record to bring back the Manager User field value from that picklist.

So my question is . . . how do I build a formula field that can simply return the text based off a picklist driven field???  So if the Manager User record value selected from a picklist is "Country Manager", then how can I pull that Country Manager value to the Sales Rep user record?  I really want to use a picklist on the Managr record, otherwise I will run into data inconsistencies.

Any suggestions?  Can this be done?

We have a process that runs under a "dummy admin user" and it will periodically makes updates to a record.  We want to be able to more easily track when the latest change from this user was made (and potentially generate workflow rules, etc from this field).  What I am thinking of doing is just to have a field that whenever the "Last Modified" field is updated as being related to this particular user . . . . the field "Last Modified by X" field would show that last modified date.  Is that possible?  If so, how would that formula be written?

Here is maybe a better example of the thing I am hoping to do.  "User X" makes a modification to a Lead and so the Last Modified By field shows "User X 4/8/2009".  I am hoping to have a field called Last Modified by User x that would show 4/8/2009.  That way we can have a view/report/workflow that can work off that field.  The reason we want this is that other users could make updates to the records after User X and they become hard to find.  With the formula field, we can easily see Leads this week/month that User X modifed.

Is there a way to write a validation rule that can trigger an email notification whenever ownership of a lead changes?  I know I'll feel dumb after seeing it, but how would the rule be written to do this?

Leads change hands here quite a bit and people don;t always remember to check the "Send Notification" box.  I'd like to see if I can have a workflow rule that will automatically trigger an alert to the new owner without that box getting checked.

Hoping someone can offer some help . . . we have a date/time field that tracks the "Last Status Change" for leads.  When a Lead Status is updated, a trigger updates the date/time to "now" and then we have a formula field that calculates the number of days between now and that date/time of the last change.  However, we only calculate that value for leads that have not been converted and where they don't have a Closed status.  The current formula is below and it works great.

IF(AND(NOT( ISNULL( Date_Time_Last_Status_Change__c )),NOT(IsConverted),NOT(ISPICKVAL(Status,"7. Closed"))), (NOW()- Date_Time_Last_Status_Change__c ),NULL)

Now . . . here's the question.  There is some talk about changing it to try and only show workdays since the change--to not calculate weekends.  I have the following formula to show the workdays.  BUT . . . how would I combine the conditions in the formula above and the formula below so I only get workdays since the date/time of change if the lead still meets the conditions of not being converted or closed???

CASE(MOD(  DATEVALUE(Date_Time_Last_Status_Change__c) - DATE(1985,6,24),7),

  0 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
  1 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
  2 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
  3 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
  4 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
  5 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c),7),1,0,2,1,3,2,4,3,5,4,6,5,0),
  6 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
  999)
  +
  (FLOOR(( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) )/7)*5)

Looking for a strong SFDC administrator who is also able to craft solutions using tools that go beyond SFDC configurations (i.e. Apex, VisualForce Pages, HTML) while keeping focus on enabling the sales organization to be effective.

Please reference the information and/or apply at the link below:

http://www.polycom.com/company/careers/career_search.html

Requisition # 285BR

Hi,

   I am working on one workflow which sends out some notfication email to user depending on some data from opportunity.

   One of the triggering rule is ...the Opportunity Amount > 15000 USD.

   Since the currency in the opportunity varies, it could be USD or maybe not.

   How can i conver the Opportunity Amount in other currency into USD by using formula?...

I'm struggling with another validation rule.  We have a picklist where the sales reps indicate if it's a standard discount or not.  Then there is a multi-select picklist where they indicate the particular type of discount they are asking for.  I want to have a validation rule that says "If the Standard Discount field = Yes, then the Discount Type field can't have certain options selected.  I have figured out how to do a rule to error out checking for one value in the multi-select list.  But I can't seem to figure out how to check multiple options with one rule (I'd rather not have 10 separate rules if it can be done with one).

Here is my example which successfully creates an error if the Stadard Discount = Yes and then the Discount type includes "Rebate":

OR(
AND(ISPICKVAL( Standard_Discount__c , "Yes"),
INCLUDES(Discount_Type__c, "Rebate")))

So how do I take this rule and give an error if the Discount Type also includes "Trade In" or "Certification" or "Credit Return" etc.?

Could use some help . . . I created some validation rules to impose certain restrictions on a discount request functionality we have.  Works great, when someone plugs in an amount of $10k or more, the validation rule triggers and stops the save. 

The problem is that we have multicurrency enabled--I was expecting the validation rule to catch the conversion rate and to prevent someone from entering what would be over the $10k USD amount . . . that doesn't happen!!!  If I switch the currency on the record to GBP and put in $9000, the record saves.  It only errors when I hit the 10000 value regardless of the currency (and 9999 GBP is way more than $9999 USD).  The formula I am using is below.

AND(ISPICKVAL( Is_this_a_10k_10percent_NST__c , "Yes"),Hardware_Incremental_DiscountUSD__c >=10000)

This is a HUGE problem . . . I need to have this rolled out by Monday and if the system is letting larger GBP/EUR discounts run through without stopping them it will be a disaster.  How do you get a validation rule to take multicurrency into account???  Or can it?

Could use a little help here from the masters on this board . . . we have a discount approval process that uses an Object related to Opportunities.  Our order management folks really want the Opportunity Owner's name to be reflected on the Object itself (Approval Request Form).  Looking at the Opportunity itself is apparently too hard or requires extra steps.  Whatever!

Anyway . . . seems like it should be an easy thing to do but all I have been able to do is create a formula field that can pull in the Opportunity "Owner ID"--which means nothing to the people who need to see it.  I really need a field that can automatically show the actual Opportunity Owner Name in text on the Approval Form so order management can see "Bob Smith" instead of "00530000000diZm".

I'm sure there is probably something simple I am missing.  But how in the world do I get the name to show up in a field on that Approval Form?  Is there a formula function that "translates" the ID and can do that?  I'm pretty stumped.

We have a multiselect field where we can note "Special Designations" for Accounts . . . can be things like Fortune 500 or other special groups we want to associate the Account to without having tons of check boxes.  We are also doing some cleanup/lockdown on some specially designated Accounts and I want to restrict users (except admins) from changing the Account Name if a particular value is selected in that Special Designations multiselect list--lets say that special designation is Fortune 500.  How would that validation rule be written?  I believe that multiselect lists can be included in validation rules now but I'm not sure how to actually do it.

I tried the following but I get an error trying to reference the multiselect picklist.  But I am crummy with formulas so not sure what the solution is.  Any suggestions would be gratefully welcomed!

AND( IF($User.ProfileId <>"00e70000000wI69", true, false),
 IF(Special_Designations__c,"Fortune 500", false), IF( ISCHANGED( Name ), true, false) )

Is there any way to have a formula field that would do a simple list of the product names included on an opportunity?  Example: an opportunity has Widget A, Widget B and Widget C listed in opportunity products.  Would there be a way to populate a field through a formula or workflow rule just to show those product names?

The reason is that I am getting tons of questions for reports based on opportunties that do or do not have individual products or combinations of products.  SFDC as far as I can tell does not really do this (like trying to get a report that will exlude ALL opportunities that contain Widget C . . . if you exclude Widget C in a filter you still get opportunities that may have Widget A, Widget B and Widget C--but it just won't show the Widjet C rows).

If there is a way to "summarize" product names in a text field then I could see a way to do this.  Is there any hope?  Or another way to try and do this?

Back for some formula field assistance.  I am working with our IT team to set up a hierarchy structure on SFDC User records so they can trigger some dashboards off that info.  So I went to create the fields that they need . . . I have lookup fields on a user record that can be used to look up other SFDC users and put them into a spot on the hierarchy.  Those lookups bring back other data from that "manager user" record and shows them on the sales rep's user record (So on Sales Rep #1's user record, there is a lookup for Manager #1 (2, 3, 4, etc) and by selecting that person it returns data for that manager and puts it on the sales rep record for reference).  Hope that makes sense.  Anyway, it all works fine EXCEPT for one thing.  I want to have a field that pulls in another Manager field--but it's a picklist on the Manager User record.  And I get syntax errors when I try to do a formula field on the Sales Rep record to bring back the Manager User field value from that picklist.

So my question is . . . how do I build a formula field that can simply return the text based off a picklist driven field???  So if the Manager User record value selected from a picklist is "Country Manager", then how can I pull that Country Manager value to the Sales Rep user record?  I really want to use a picklist on the Managr record, otherwise I will run into data inconsistencies.

Any suggestions?  Can this be done?

I am a bit of a beginner at formulas, so hopefully one of you will think this is easy, and be able to help. :-)

Customer requirement:

Create a formula field that displays the Week Number (as Week 1, Week 2, etc), based on the Date entered on a particular record (same object).

Ex: 01/01/09 - 01/04/09 would return a value of "Week 1"  

01/05/09 - 01/11/09 = Week 2

01/12/09 - 01/18/09 = Week 3

and so on.....

Is using the CASE function the way to go, and if so, what's the shortest way to create this formula?

Thank you in advance!

S

Hi,

    I want to trigger a workflow rule 1 on object A, with the Evaluation Criteria as "Every time a record is created or edited".

    I have another time-based workflow rule 2 which will updatea filed on Object A.

    So at a certain time, the field on Object A should be updated by workflow rule 2 and then workflow rule 1 will be triggered.

    However, the result turns out to be the field was updated but  workflow rule 1 was not triggered at all. If I  manually update that field, then I can see the expected result.

    Any ideas about this? I know the update of formula filed won't be considered as a edit action but this field is just a text field.

    Thanks in advance!

We have a process that runs under a "dummy admin user" and it will periodically makes updates to a record.  We want to be able to more easily track when the latest change from this user was made (and potentially generate workflow rules, etc from this field).  What I am thinking of doing is just to have a field that whenever the "Last Modified" field is updated as being related to this particular user . . . . the field "Last Modified by X" field would show that last modified date.  Is that possible?  If so, how would that formula be written?

Here is maybe a better example of the thing I am hoping to do.  "User X" makes a modification to a Lead and so the Last Modified By field shows "User X 4/8/2009".  I am hoping to have a field called Last Modified by User x that would show 4/8/2009.  That way we can have a view/report/workflow that can work off that field.  The reason we want this is that other users could make updates to the records after User X and they become hard to find.  With the formula field, we can easily see Leads this week/month that User X modifed.

Is there a way to write a validation rule that can trigger an email notification whenever ownership of a lead changes?  I know I'll feel dumb after seeing it, but how would the rule be written to do this?

Leads change hands here quite a bit and people don;t always remember to check the "Send Notification" box.  I'd like to see if I can have a workflow rule that will automatically trigger an alert to the new owner without that box getting checked.