Custom Field values based on data in other custom fields

[SteveVa]

I am interested in having a custom field auto populate based on the value in another custom field. For example, I have one field that defines the level of project complexity as a Tier 1, 2 or 3. I have another field that captures the minimum expected revenue for such a project to help our salespeople have a consistent baseline. Is there a way for the expected revenue field to be auto-populated with an amount based on the tier level selected? Thanks for your help.

[ShellyLo]

You can. I am attaching screen shots of the Custom Form setup I used to make this work. I hope this helps!

[VernPh]

This is really cool and thanks for the example. I have a slight variation of this that perhaps someone could help me with: I would love to be able to show a dropdown of options based upon the user's selection from an initial drop down. So, for example, if the user picks "A" from the first drop down, a second dropdown will allow "A1", "A2", "A3" or "A4" to be selected. One approach would be to have another custom field that turns on or off based upon the selection. But the secondary requirement is the need to develop reports and/or views that can show, group by, and be searched by the consolidated set of all potential options of the second dropdowns. I don't know of any way to allow that type of functionality. Not sure if there is a solution to this, but it would solve a problem we have with one of my groups of users. Currently, we have a lengthy set of options for the second dropdown and the user needs to sort through the list to find the one they are interested in. Thanks, Vern Phipps

[GregBe1]

I think one option to do this is with also using Calculated fields. As you stated, you could have separate drop-downs for the second choice, based on what was chosen for the first, but then you could add a custom field that populates whatever was chosen. Let's say your top level drop-down has 1, 2, and 3 as options. If "1" is chosen, then you'll display drop-down "Child1", which has values of 1a, 1b, and 1c. If "2" is chosen, then you'll display drop-down "Child 2" which has values of "2a", "2b", and "2c". In your calculated field, you'd say if top level drop-down = "2", then the value should be from drop-down 2, etc. IF({top level drop-down}="1",{Child1},{Child2}) Then you could do all your reporting and display from the calculated field.

[DavidCo3]

Hi - I like Greg's solution.

The only thing I would add is: If you are using a dropdown, you can set the type to 'Number' then click on 'Show Values'. This then lets you store a meaningful label for each option in text, and it will store a related number that you specify which you can then use for calculations and sorting, etc. See attached screenshot.

Cheers, David

[ShellyLo]

David - Much cleaner look! Thanks for sharing.

[SaraJa]

Hi Shelly, I am hoping you might be able to describe how I might modify this for my current situation. I have a custom field that describes the technical difficulty ("TD") of a project but not necessarily the complexity. We were hoping to create a calculated field that multipled the TD by a certain complexity score. There are five possible "TD" options, which are a combination of numbers and text. I was planning to first create a Complexity column base on the project "TD", with the following code, but it is not working (perhaps it is too many IF statements strung together?). IF(TD=3,1,IF(TD=1,3,IF(TD="FT",0.75,IF(TD="Play",0.5,IF(Box=2,2,""))))) And then I was going to multiply the two columns together. Any thoughts you might have on this are much appreciated! Thanks, Sara

[DavidCo3]

Hi Sara, I think the issue is in the right-most brackets where it says IF( Box =2,2,"") If Box is not a valid field, that would be why it is failing. If you change that to TD it will be fine. Regards, David

[SaraJa]

Hi David, Great eye! Unfortunately I just copied the code over incorrectly. I have "TD" and it still does not seem to be working.

[EricHa3]

Hi Sara, I am guessing, but it could be that you are mixing the data types of TD. What happens if you try them as all string comparisons like this: IF(TD=”3”,1,IF(TD=”1”,3,IF(TD="FT",0.75,IF(TD="Play",0.5,IF(TD=”2”,2,"")))))

[DavidCo3]

Hi Eric, I thought the same thing, but I tried replicating what Sara had and it seemed fine to me with a mix of quotes and no quotes. Regards, David.