Set field value based on other field

[MikeS]

I have a requirement to set or update a lookup column based on a text field in the same list. For example: the text field can be any of 25 values. Based on one of these values I want to set up a rule that would then update a name in a lookup column (single lookup).

Scenario: If Column A is 1, 2, 3, or 4, update lookup field with Name 1. If Column A is 5, 6, 7, or 8 update the lookup field with Name 2, etc. If the user changes Column A than change the lookup field accordingly. I could make the lookup column a plain text field if that would be easier. So it’s just IF-THEN logic where changes in one field drive changes in another field.

The Add days to a date field solution worked well for changing a date field based on another date field entry. Seems like this should be easier.

Thanks for your help.

• This topic was modified 5 years, 6 months ago by MikeS.

[Keith Hudson]

My javascript skills do not come anywhere close to Alex’s, but here are two approaches to solve this issue. I would create a rule that fires a javascript function every time the value of Column A changes, and use a javascript function to to set the field value of the lookup field.

Version A: uses a javascript switch statement (You can read up on what a switch statement is here: https://www.w3schools.com/js/js_switch.asp) (Although this is not the most efficient way to meet your need, I’m including it because it is so useful for situations very similar to yours.)

Here’s the javascript function, using sample values:

function updateColumnA(){
var colA = getFieldValue(‘ColumnA’); //make sure you use the internal field name
switch(colA) {
case ‘1’:
setFieldValue(‘LookupField’,’Name 1′);
break;
case ‘2’:
setFieldValue(‘LookupField’,’Name 1′);
break;
case ‘3’:
setFieldValue(‘LookupField’,’Name 1′);
break;
case ‘4’:
setFieldValue(‘LookupField’,’Name 1′);
break;
case ‘5’:
setFieldValue(‘LookupField’,’Name 2′);

etc.

default:
//code block (you may or may not need a default code block)
} //end of switch statement

} // end of function

NOTE that the tick marks around the case values is required if your values are strings, and NOT required if Column A is a number column. You’ll have to create one ‘case’ statement for each value of ColumnA, which in your case (you are mapping several values of ColA to a single value in LookupField), is a bit repetitive, so you might prefer approach B, below.

Approach B:

function updateColumnA(){
var colA = getFieldValue(‘ColumnA’); //make sure you use the internal field name
if (colA === ‘1’ || colA === ‘2’ || colA === ‘3’ || colA === ‘4’){
setFieldValue(‘LookupField’,’Name 1′); //use the field internal name for LookupField
} //end of first if statement
if (colA === ‘5’ || colA === ‘6’ || colA === ‘7’ || colA === ‘8’){
setFieldValue(‘LookupField’,’Name 2′); //use the field internal name for LookupField
} //end of second if statement
etc.
} // end of function

Use as many if statements as needed. If ColA holds numbers, don’t use tick marks around the values in the if statement.

Good luck.

[Alexander Bautz]

Hi,
The approaches Keith suggests are both good solutions and I would use a similar method myself.

Best regards,
Alexander

[MikeS]

Much appreciated Keith. Your tips enabled me to write a custom JS function for DFFS that works great when using the forms.

However, is there a way to have custom JS loaded vis DFFS work in SharePoint 2013 Quick View (aka Data Sheet)? My users work in that view frequently to quickly enter data and the custom JS does not work as you move from row to row.

Any way to just load the JS outside of DFFS when in Quick View?

Thanks

[Alexander Bautz]

Sorry, but the DFFS Custom JS only lives in the new, disp and editform. If you want to apply code to a list view you would have to write custom code and add it to a script editor web part in the view.

Alexander

[Keith Hudson]

As Alex indicates, the custom javascript function you wrote in DFFS will not work in the Quick Edit view, because it relies on the underlying DFFS code that Alex wrote only for use on the new, edit and display forms.

To illustrate, I will refer to the two fields you are dealing with as the Source field and the Target field. From your original post, it sounds like your Target field is in fact a SharePoint lookup field. If that is true, then my first idea below will not work.

First idea:
Change the Target field to a calculated field (by deleting the original Target field and creating a calculated field of the same name). Use nested if statements to set the value of the Target field as you did in your custom javascript function. You can’t use a switch statement, but you can use the AND, OR and IF functions in a calculated field. (Lots of info on those functions in google).

If that approach WILL work for you and you have trouble getting the formula for the calculated column set up, post here and I’ll try to offer some help.

Second idea:

If the Target field must remain a Lookup field, you could remove it from the view on which users are making their changes (or simply instruct them to ignore it) and use a SharePoint Designer workflow that fires every time an item is changed to apply the logic that will update the Target field with the value you want it to have.

[MikeS]

Thanks for the tips Keith. Your First Idea worked great once I built up the nested statements in the Calculated Column. A SPD 2013 workflow isn’t suitable as we bulk load data via Access nightly and workflows can fail to keep up (with unpredictable results).

[Keith Hudson]

Mike, I’m glad the nested if statements in a calculated field worked for you. You mentioned that workflows can fail to keep up (I assume you mean that they may not run in a timely manner reliably). *IF* you are the farm admin, and if the lack of reliability is due to long delays in workflows executing, you MIGHT be able to make them more reliable. There is a setting in Central Administration for how many workflows can be processed simultaneously. The default is 8. I have seen a white paper written by someone who tested increasing that setting to 25,000 with no observable performance issues in their environment. Just a thought. Most of us are NOT the farm admin, so we are stuck with whatever settings the farm admin chooses, and most farm admins wouldn’t know about that setting, or would be afraid of breaking a site by changing it.

[MikeS]

Keith, I’m not the farm admin. I’ll query our admins to check on this and if it can be increased. Thanks for the tip.

[Author]

Posts