How to build other SharePoint columns from the Title column in a SharePoint List using Javascript or workflow

Question

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)

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!!! :)

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.

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.

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.

After running the workflow, you should get output like this: