Lookup the management level based on job title

[MalyMajo]

Hello all,

in our organization we have a 13 level management structure. Level 13 is a new associate and level 1 is CEO.

I would like to map the levels based on some keywords available in the jobTitle of the person.

So for example, for level 6, the keywords are [Sr. Manager] and [Senior Manager].

For level 7 the keyword is [Manager].

The job title contains these words + some definition of the topic.

Example of job titles: "Cloud Transf & Arch Manager"; "Cloud Transf & Arch Sr. Manager"; Application Development Manager"; "Application Development Senior Manager".

If the jobtitle contains only word manager, the level should be 7. If it contains Sr. Manager or Senior Manager, the level should be 7.

Is it possible to lookup to new column the level via Power Automate?
I can imagine creating it in other tools such as Power BI or Excel, but the client requirement is to have it straight in the SharePoint list.

[v-jefferni]

Hi @MalyMajo ,

Basically the solution is to run Excel Script to perform RegEx (Regular Expressions). There are quite a lot tutorial blogs/videos you can find from internet, e.g.

https://www.youtube.com/watch?v=S22719J_yXw

https://www.youtube.com/watch?v=JUbhurUc7ss

https://www.tachytelic.net/2021/04/power-automate-regex/

However, I would say this is quite a complex process because of the 13 levels. If possible, it would be much easier to achieve your goal by adding a column to the list with numbers representing management levels.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[VictorIvanidze]

Hi @MalyMajo,

I think you can try to create the custom property "Level" in Delve:

https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-display-additional-properties-in-publi...

[MalyMajo]

Unfortunately I don't have admin rights in SharePoints created via Teams, but thanks, it is good to know that there is a way in normal SP.

[MalyMajo]

Hi @v-jefferni,

thanks for proposal with regex, I will look into it.

What do you mean by adding column with numbers? Like to update it manually instead? That is how it is done currently and I would like to avoid this, because it is very time consuming considering that some of the projects have over 1,000 resources. 

[v-jefferni]

Hi @MalyMajo ,

Not manually update, but could you please explain more about your goal? What would you like to achieve using the management levels and job titles?

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[MalyMajo]

Management level of resource is an input to another bigger Excel report, where billings are calculated based on that information. There is around 1,000 resources and most of them reoccur so there is a lookup table. However, the levels as well as job titles change for some resources every 6 months so it is quite a struggle to keep it up to date.

However, in the meantime I have found a workaround. The wording in the job titles has always some keywords - such as Sr. Manager or Senior Manager for level 7. The workaround is that only the job titles will be pulled to the report and then the correct level is looked-up with VBA.

If you see any simpler solution which could be covered with Power Automate itself without human interference, I will be really happy to learn. 🙂
I was thinking of changing the lookup from VBA to Excel Scripts.