Was wondering all you intelligent folks out there can help me on this one.

Overview: We have a SharePoint Library "Reports" that gets populated automatically with folders and files using another external system(don't have control to this system). The folders and files are copied to the library through "open in explorer" way. The structure of the folders are this way, Eg : Folder A610 - Folder pcl - File name (A610-pcl-May-16) Folder Structure

As you can see the Title has the format A610-pcl-May-16

Requirement from Business: The business requires a custom landing page with three filters to search the files in the SharePoint library, the following are the filters,
1. Centre - which Corresponds to the A610 of the file name ( also they need full name like "A610 Visitors in Department"
2. Report Type - which Corresponds to the pcl of the file name
3. Division - this is tricky, for example they need a parent group say A6000 which consist of say A610, then A7000 which consist of A611 in them, so when users filter based on A6000 it should only return A610. Now this parent group is not available from the file name, will need to somehow use logic to create the metadata for it.
Solution tried: So firstly from the file name A610-pcl-May-16, I was able to create two new Calculated columns in the library called Centre and Report Type which strips out the A610 and pcl from it. That was great and working.
Now my challenge was to add say the full name to the Centre column like "A610 Visitors in Department" and to add a Division column which consist of the parent group name "A6000",
So ultimately when users see the filters, they would select
Division - A6000
Centre - A610 Visitors in Department
Report Type - pcl
this should return them the file name A610-pcl-May-16
I have tried to use lookup columns and was failing to use them. Is there a workflow or JavaScript solution for it?
Would be nice if somebody could guide me in the right path Thanks in Advance

Also new to StackExchange and excited!!! :)

1 Answer 1


I would create a separate list that contains every Centre and its parent Division. Columns would be 1) Title (this would be the code that is in the file name), 2) Centre Name (this would the friendly name you want to show in the dropdown) 3) Division.

lookup list

Then I would create a lookup column (called Centre) in your library that looks up to the Title field, and also brings along the Centre Name and Division fields.
enter image description here

Then I would create a SharePoint Designer workflow that runs when a new item is added to the list. It would parse the filename, get the Centre value, and set the lookup column to that value. The connected lookup fields would then also show the Centre Name and the Division. enter image description here

After running the workflow, you should get output like this: enter image description here

  • firstly thanks, I was thinking in the same direction as creating a separate list and then using lookup column to grab the values, as mentioned earlier I tried parsing the file name by using a calculated field say "Centre Calculated" then using the workflow was trying to copy the value of it and setting it in the lookup field (Centre). Is that the way to go? Are you able to provide some example using SP designer and how to set a lookup field which is quite different from other normal fields?
    – Matthew
    Feb 22, 2017 at 8:52
  • updated my original answer with more details and screenshot.
    – Robin
    Feb 23, 2017 at 19:09
  • Thanks Robin for the clear instructions and screenshot. I was able to apply the same logic and generate the metadata from the file name. Only thing I noticed is because the files are added through file explorer as files in folders it was giving the error in workflow "The item is currently locked for editing. Waiting for item to be checked in or for the lock to be released". I fixed this by adding the logic to check in the item in the workflow. Thanks for your prompt solution and marking answered :)
    – Matthew
    Feb 24, 2017 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.