Hi Jacob,
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
- I'm assuming your location list is denomalised but it's clean. i.e. there aren't any anomalies where a location may have different regions. If this is the case, then you'll need a way of uniquely identifying the location+division combo you want to look up. You can do this by another calculated value.
- If you have the approving manager in the location list, you can look them up in exactly the same way
lookup("Location", "Location Name", parseLookup(Location), "Approving Manager")
Hope that helps.
Cheers,
Chris
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:
Hi Jacob,
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”
Cheers,
Chris
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?
Hi Jacob,
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.
Cheers,
Chris
Hi Chris,
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?
