Question

Calculating values in custom fields using workflow automation question

  • 29 December 2022
  • 11 replies
  • 146 views

Userlevel 1
Badge
  • Inspirational User
  • 25 replies

I have a bid packet that is sent to potential clients. This bid includes a variety of pricing. There are 2 values entered by our estimator, and then 5 other values that are based on those two values.

I have created a cf for each of the values and tried to create a workflow automation based on one of the values entered by the estimator (I would like to do the same for each of the different prices - but just wanted to test it first). Here’s my automation:

 

 

But nothing seems to be happening to the 12 Week Full/WSV field. What am I doing wrong?

 

Thanks!

Ruth


11 replies

Userlevel 7
Badge +7

Hi @ARCO! I’ll take a look at what’s happening. Just wanna confirm, when the estimator fills out the field “8 Week Full/WSV,” you want the workflow automation to multiply that by 1.5 and use that to fill in “12 Week Full/WSV” ?

So if I fill in “8 Week Full/WSV” with $1000, we’re expecting the automation to fill in “12 Week Full/WSV” with $1500?

Thanks Ruth!

Userlevel 1
Badge

Yes Michelle, that’s exactly right. The other calculations will be deducting $250 from a value, deducting 10% from a value, and adding $1000 to a value. Any help you can offer with these formulas is really appreciated!!

 

Ruth

Userlevel 7
Badge +7

So, I set up the same fields and workflow automation in my demo account and it works without any problem.

I looked at your workflow automation history and I see that the rule “Calc 12 Full/WSV Test (RWT-Test)” is saved and turned on. But I don’t see any individual runs on that rule.

Do you have any opportunities that currently have a blank 8 Week Full/WSV? If so, can you try filling in that field and seeing if the automation works?

Here’s my guess - let me know if this sounds right: your team has probably been filling out the “8 Week Full/WSV” field for the past while. This means that most of your opportunities already have “8 Week Full/WSV” greater than $0. The way the workflow automation works, it will only run the first time an opportunity meets the criteria. So since most of your opportunities already meet the criteria of “8 Week Full/WSV” greater than $0, the workflow automation is not being triggered.

In effect, this workflow automation will only work on new values for “8 Week Full/WSV.” If you want it to do the calculation for existing opps/values, then you’ll need to set 8 Week Full/WSV to $0 temporarily, and then fill it back in.

Userlevel 1
Badge

Hi Michelle,

 

Well you’re partially right, some of the values have already been entered, but the test opportunity I was using did not have a value. So when I set the 8 week full/wsv to $0, then changed it to $1000, I DID get the result of $1500. However, when we create a new opportunity, that field is blank...not $0. How do I fix this? should I change the formula in some way that ANY value in the 8 week full/wsv runs the automation?

 

Thanks!!

Ruth

Userlevel 1
Badge

Hi Michelle,

Have you had a chance to review my last question? Basically, when I create the opportunity, the  “8 Week Full/WSV” is blank. It doesn’t show $0...so when we enter a value into the field, the automation is not being triggered. What can I do to fix this?

I will also be creating some other calculated fields.

  1. Also pulling off of the  “8 Week Full/WSV”, I need to deduct $250 from that value, and enter the result in a field called  “12 Week Full/WSV”. What would I use as the formula for this one? (same issue, this field will be blank at first)
  2. Pulling off of a field called, “8 Week NSV”, I need to deduct 10% from that value and enter the result into a field called “12 Week NSV”.

Your help is soooo appreciated!!

Ruth

Userlevel 7
Badge +7

Hi @ARCO! Looks like we have some different things to work through, so I’ll try to keep this response nice and organized:

 

 

Basically, when I create the opportunity, the  “8 Week Full/WSV” is blank. It doesn’t show $0...so when we enter a value into the field, the automation is not being triggered. What can I do to fix this?

So, it should trigger successfully whether the field is original blank or originally $0. I’ve just tested it on my demo account and it responds whether it starts from a blank or a 0. So if that’s not what you’re seeing I’d have to look into it (and possibly send to our support team). Can you try one last time and let me know? Perhaps give it a couple of minutes to run, just in case.

If it’s still a no, I can try to take a look from within your account (with your permission; I would send you a separate email with instructions on that).

 

 

Also pulling off of the  “8 Week Full/WSV”, I need to deduct $250 from that value, and enter the result in a field called  “12 Week Full/WSV”. What would I use as the formula for this one? (same issue, this field will be blank at first)

It would be a very similar formula, but instead of cf_8_wk_full_wsv*1.5 it would be cf_8_wk_full_wsv-250

This is what the trigger would look like:

But I am curious how you will determine when you should multiply by 1.5 and when you should subtract $250?

 

 

Pulling off of a field called, “8 Week NSV”, I need to deduct 10% from that value and enter the result into a field called “12 Week NSV”.

The formula would be cf_8_wk_nsv*0.9 (this is assuming you set the field key for “8 Week NSV” to 8_wk_nsv)

This is what the trigger would look like:

 

Userlevel 1
Badge

Hi Michelle,

Thanks so much for getting back to me. Sorry for the confusion, our calculations table looks like this:

 

Service 12 Week 8 Week 4 week
Service 1 -$250 Estimator x1.5
Service 2 x90% Estimator x1.5
Service 3   +$1000 of Service 1  

 

I had put in all of the calculations and they were working….sometimes. It seemed like they worked if I created a New Opportunity 100% of the time, but when I tried to test it by setting the Estimator values back to $0 and deleting the previous values in the calculated fields, sometimes some of them worked, and some did not. I thought it might have been a “cueing” issue, but I even waited until this morning to refresh and the fields remained blank. So I’m not sure what that issue is. For now, I’ll leave it as it is, since there are not very many times when we might need to change the estimator’s original entry.

 

But eventually, I would like it if all of these values return to $0, if the opportunity is returned to the “pre-pricing” stage. I already created these workflows, but for now I have them inactive.

 

The 2nd part to my issue is that I would like these values to show up in a custom report. Since they many of them are new fields, I went into the settings in the Insights Builder and added them as Metric Fields, this was on Friday, but they are still not showing up as Opportunity fields to add to the builder:

As you can see the 8 week values are there (which I added) but I would also like the calculated fields to be on the report as well.

Also, this morning I noticed the letter “f” in the report under the cf “# of units”, which is populated with a number - do you know why they wouldn’t be showing up on the report?

Screen shot of report section showing the # of Units:

And now I can see that in the column “CF Opportunity Email (primary contact)” field, the # of Units is showing:

Do you have any insight into why this might be happening?

 

Thanks again for all of your help. BTW, the goal of this report is to use both opportunity info and company info to import into a google sheet for mail merge.

Thanks Michelle!!

Ruth

Userlevel 1
Badge

Hi Michelle,

Thanks again for all of your help. My fields seem to be populating correctly now with the calculated numbers.

 

However - I am unable to add these fields to my insights Can you please take a look at my last response showing some screenshots? How do I get these Opportunity, cf’s to show up so I can add them?

 

Thanks!!

Ruth

Userlevel 7
Badge +7

Hi @ARCO! Just want to clarify the issues you’re running into. Can you confirm if I have the right understanding?

  1. You added Opportunity Metric fields under the “Configure Custom Fields” area on Friday, but they are still not available to use in the Insight Builder’s data picker
  2. In the Insight Builder, some fields are showing data that is not consistent with their field type. Specifically, Opp CF # Of Units should be a number but is showing values of “f” (as in true/false) and Opp CF Opportunity Email (primary contact) should be a text field but is showing values in numbers.

I’d want the technical team to take a look at both these issues. Let me know if I got it right and then I can connect you with them.

Userlevel 1
Badge

Hi Michelle,

 

Thanks again for your help. Yes, you have it right. However, since I hadn’t heard from you (not a complaint - I really appreciate all you do), but I searched for the live reporting weekly webinar and saw that it was today. I joined and Kevin said I needed to submit this as a bug. He escalated it to the Tier 2 team and Oliver has reached out to me and is working on it.

Thank you so much, I’ll post later with more questions I’m sure once this issue is resolved.

 

Ruth

Userlevel 7
Badge +7

@ARCO ah ok, glad Kevin was able to assist! And Oliver is one of the best on our tier 2 team, you are in good hands.

Reply