So I need to create a form that needs to set three values to determine who the form needs to go for approval. I have Division, Region, and Location. However I want to allow the user to just choose Division and Location and have Region set automatically in the event other Regions are created or Locations are moved into different Regions.
I have two lookup lists, one for Divisions and another for Locations. The Locations list has the Location name and ID as well as the Division and Region in the list. On my form I have a List Lookup for Division which then filters the locations so only the locations in that Division show up to be selected from. I want to have the Region field hidden in the form however automatically set based on the location the user chooses. I need that because each region has a different approving manager and the workflow will use that to set where the approval needs to go to.
Is this something that can be done in Forms or do I need to create something in the Workflow to get this done?
This is certainly possible. So to recap you have:
A division list.
A Location list with the columns Location Name, ID, Region, Division and more. I'm assuming Division is a lookup back to the division list.
In your approval form, ditch the automatically created region field and create a calculated value with the below formula. Bind/connect the calculated value to the Region field. i.e. when someone submits the form, the results of this value are saved to the region field
lookup("Location", "Location Name", parseLookup(Location), "Region") <-- note the red is the location control on the form.
Here's how it's constructed.
Lookup is going to lookup a value from a specified list. That list is the location list. The second parameter (Location Name) is the column we're querying and the fourth parameter (Region) is the value we want to return. If you're to read the help documentation, the third parameter supplies the value we're querying and by all rights, you should be able to simply supply the location control but this doesn't work (for me at least). The location control is also a drop down field which actually returns the item name as well as the ID in the format 1;#text. The formula is supposed to recognise this but doesn't so I use the parseLookup formula to only return the text component.
A couple of notes
Hope that helps.
Does the location list have to be on the same site? My Location list is on the parent site. I'm getting a #Value! error and I've got the formula exactly how you had it above:
To point to a list in another site, precede the list title with the server relative url path of the site, then delimit the list title with a pipe ‘|’ symbol, e.g. “/sites/siteCollection/siteA|customList”
Dang, I knew that...sorry, been a long week.
I updated it with the relative URL and it now doesn't show the error. But now it's blank regardless of what location I choose. Could that be because the Region Field in the Location Directory is a choice type column?
I've just tested that scenario (using a choice field instead of a lookup field) and it works. Just to be safe, try creating something similar to your location directory in the same site and just see if you can get any result. If you can, we can take it from there.
Still getting an empty field on the form even with the location directory in the same site...
I did notice that a "Loading..." message comes up in the Region field but only after I select the Division and after the form reloads to filter out the locations. After the locations are filtered I select the location and don't see the loading message at all. I thought maybe it would lookup the Region when I save it but it does not. Could the filtering be causing this by chance?