Looking for a way to associate a checkbox in one field with content in another.

Michele Frost

I am creating a log of all new, won opportunities. The goal is to catalog the types of services included in each new opportunity. Next, we need to understand the revenue split by service types. 

I created a web form that allows users to "check" the service types in the opportunity. Next, I need them to allocate the revenue associated with each type selected. 

What is the best way to do that? Is it possible to use the presence of a check box in one field to require users to populate another field? 

For example, let's say the opportunity will generate $1,000. It includes service A and service B: 2 of the 10 service types we offer. The form user selects the checkbox by type A and B to indicate the two types included. Next, the user needs to allocate the revenue by service type A and B; A is $600 and B is $400. We would use conditional logic to ensure the sum is $1,000.

The solution must allow us to "count" services offered by type and associated revenue. We need these two field to be related but independent to show service mix, trends, etc.

Thoughts? 

Shaine Greenwood

Hi Michele,

You might consider creating a web form with a dropdown field and text/number field, making both of them required fields in the form. Details on this can be found in our Help Center: https://help.smartsheet.com/articles/522221-using-web-forms

Adam Overton

I don't know the context of your scenario, so my answer may not be quite right. Fundamentally, it sounds like the check box and the revenue value are redundant information. For example, you could make the checkbox set by a formula that looks to see if the value is not blank and not zero and if so, is checked.

Consequently, all I would put in the web form is the revenue by service columns. The total revenue and the check boxes (if I still needed them) would be formulas based on this source data. You want credit for your the whole value of your opportunity? Better enter the values for each service.

For your data reporting, you can either count the checkbox columns, or use a COUNTIF to count the non-blank, non-zero revenue attributions. Obviously you can use a SUM to get the total value, and divide the SUM by the COUNT to get the average value for service A, etc. For convenience, I tend to put the summary rows under a one parent row, and the actual data rows under a Data parent. Just be aware the COUNT(CHILDREN()) returns the count of non-blank children.

 

J. Craig Williams

re: COUNT(CHILDREN()) returns the count of non-blank children

This may only be true of Checkbox type columns. Other column types will be cognizant of the blanks and return the count of non-blank children.

Craig