We are attempting to be a little more proactive with our customers. We are wanting to write a saved search that will send an email to our customer service department when a ship date has been missed.
In order to do that, we need to set the ship date on new sales orders to be a certain amount of business days in the future from the create date via a workflow. We have been able to add days by using {trandate}+9 (for example), but that will add total days, not business days.
How would we go about writing a formula on the default sales order workflow we have so it sets the ship date on the sales order to a future business date? The other wrench is, this is based upon shipping method. Different shipping methods have different amounts of days the order is expected to ship. Here are a couple of examples.
We have a total of 8 different shipping methods with 8 different business days they are expected to ship. Just adding days, our set field formula on the workflow looks like this:
CASE WHEN {shipmethod}='Shipping Method 1' THEN ({trandate}+9) WHEN {shipmethod}='Shipping Method 2' THEN ({trandate}+9) WHEN {shipmethod}='Shipping Method 3' THEN ({trandate}+7) WHEN {shipmethod}='Shipping Method 4' THEN ({trandate}+1) WHEN {shipmethod}='Shipping Method 5' THEN ({trandate}+7) WHEN {shipmethod}='Shipping Method 6' THEN ({trandate}+1) WHEN {shipmethod}='Shipping Method 7' THEN ({trandate}+3) WHEN {shipmethod}='Shipping Method 8' THEN ({trandate}+2) ELSE {trandate} END
As previously stated, this works great to set the ship date on a sales order at creation time via a workflow to a date in the future. It just does not take business days into account. We need all of the dates to be business days in the future, not days in the future. We can probably use to_char({trandate},'D' in there somewhere, but we are not quite sure how to incorporate that into the command. Any help would be greatly appreciated it.