Generating notification for user to confirm that a field has the correct value

Question

I have a custom field called Pipeline which is a required Yes/No picklist that defaults to No. When the user makes some specific changes to the record, and Pipeline=No, I would like them to confirm that Pipeline should remain No. I have tried to implement this in several ways:

1) Validation rule making Pipeline required. Before update trigger that nulls out Pipeline when those specific changes have been made to the record.

Result: this gives the user a notification, but then when they click save, the trigger runs again. and again. and again. Because the changes gets rolled back when validation fails, it always looks to the trigger like fields have been modified on the record.

2) Like 1 but with a static variable in the trigger to tell the trigger it as already run once.

Result: same thing. Apparently the static variable gets lost as soon as the validation fails.

3) Workflow rule that nulls Pipeline. AddError in before update trigger when Pipeline is null.

Result: About the same as the others. Workflow still runs every time because the record was never successfully updated, the fields appear to have been modified every time it runs.

Been going through the order of operations and trying to figure out some way to get this to work. I am looking for a solution without a VF component, but I am not sure there is one.

Answer 1

One way that I've handled this before is to add a checkbox to the record of 'Confirm'. Then when a user saves the record, a validation rule checks the Yes/No value and looks at the confirm checkbox. If the checkbox is false, the validation rule throws an error and the error message asks them to tick the checkbox and resubmit.

If the confirmation is a single shot deal and the value needs to be reconfirmed every time, you can add a workflow rule that clears the checkbox when the record is edited or updated.

Answer 2

If you have a validation rule that requires this picklist field to be populated under certain circumstances, then the rule will require the user to enter something if that field is null. So it sounds like you need that field to be null before the user enters information, so that it will force them to enter a value when they save (if necessary).

Your picklist has values of "Yes" and "No". However, it sounds like you're saying that there is a point where Pipeline is essentially not determined or not committed yet (according to your criteria), and this would be equivalent to that picklist being null. You really have three values, then: "Yes", "No" and null.

My idea:

• have this field default to null.
• use a before update/before insert Apex trigger to set this field to Null unless it has already been set to "Yes", or the commitment criteria have been met and it has been set to "No".
• use a validation rule requiring that this field be populated if the commitment criteria have been met.
• In reporting, group records with Pipeline = null and Pipeline = "No" together as non-pipeline records. This could be done through a formula field.

Edit: that one won't work, because there are multiple "commitment" points, based on Stage. Every time the Stage changes, after a certain point in the sales process, the user needs to decide to select "No" or "Yes".

New idea:

• Replace "No" with multiple "No" choices: "Stage 3, No Pipeline", "Stage 4, No Pipeline" and so on.
• Create a picklist dependency between Stage and Pipeline, so that at Stage 3 you only have the choices of "Yes" and "Stage 3, No Pipeline"; at Stage 4, you only have "Yes" and "Stage 4, No Pipeline". When the user changes the Stage from 3 to 4, the Pipeline picklist will automatically clear itself on the page if it was "Stage 3, No Pipeline" before. If it was "Yes" before, it will stay "Yes".
• Your validation rule requires setting the Pipeline field if Stage is one of 3,4,5,6
• Reporting can lump all the No Pipeline Opportunities together, again through a formula field

Answer 3

This really is a tough one. I've been asked about building confirmations into standard page layouts before, but have never been able to come up with a working solution. The root problem lies in the fact that Salesforce.com does not allow you to hook into the Save functionality. If this were a Visualforce page, it would be easy enough to add an onClick JS function to the Save button that pops up a Confirm dialog before the save method is executed.

I believe the reason that none of the attempts using a Validation Rule, Workflow Rule, and/or Trigger are not working is because Salesforce.com effectively wipes the slate clean when an error is displayed on the page (aka rollback). Thus, it's not possible to build a solution to display an error message only the first time Save is clicked, but not the second time. As far as the save processing logic is concerned it's always the first time. Any changes made by a WFR or Trigger are rolled back before the error is displayed to the user.

Other than adding a "Confirm" checkbox as suggested by Bob Buzzard or moving to an Approval Workflow process, the only other solution I can come up with is to re-write the New/Edit page as a Visualforce page.

Answer 4

You could setup a workflow rule which sends an email alert to the user to have them validate the record.

Your Workflow Rule would evaluate on essentially your validation rule criteria, and an Email would be sent to the User. Inside the email you would have the list of fields, and a hyperlink to the record in SFDC to allow them to update it.

The notification is then occuring in the users email inbox, not on the direct screen. But this would resolve your problem using declaritive features of Force.com, and give them ability to review and update the value if they forgot to update it.