Populating SharePoint Fields

[JeffC]

I created an IT New User SharePoint list that is populated by a Microsoft form that HR uses.  For this sharepoint list I have a power automate job that creates a username and email address that is based off of the new employees first name and last name.  This username and email then updates a row in the same sharepoint list.

Example

New employee Name: John Smith

Username Generated: smithjo

Email generated: john.smith@company.com

This power automate job works great but I have made some modifications in case a new user joins and the username and email address is already taken.  

Therefore, I have modified my PA job to check if the username already exists and passes the value to a  condition (Yes/No)  If the username doesn't exist then create the username as smithjo.  If the username does exist, then created it as smithjoh.  However, when the PA job runs and there is more than one line in the sharepoint then the first condition that is met is then used for all of the different SharePoint lines.  It's like the Apply to each command isn't working with a Yes or No condition.

Any suggestions would be greatly appreciated as I have spend a few hours trying to figure this out.

Thank you!!!

Jeff

[schwibach]

You can have the user name written in one field and require that field to have unique values in SharePoint.

In PowerAutomate you throw out the condition, but set a flow step to create an item with the user name you want.

That flow step fails if the user name already exists (because that name cannot be created).

In the next step in Power Automate you have your alternate user name created (maybe with a 1 in the end). You set the 'run after' conditions in that step to only run if the previous one failed... You can insert as many of those steps as you like.

The next one after that should run if the previous one succeeded or was skipped.

[JeffC]

I think I am following you.  I will see what I can put together.  If you have an example that would be greatly appreciated. 

[schwibach]

Here you go.

[grantjenkins]

I can't say I've ever seen this sort of solution to manage Active Directory users, etc. The raw data is normally sent to the Active Directory team who manage the usernames directly when creating the account (system will automatically tell you if a user with that name already exists, etc., and often auto-append a number for you).

A few questions for you.

Will all your Active Directory users be within your SharePoint List?

If the answer is no, then you would need to check in both your SharePoint list and Active Directory to see if that username has already been taken.

What happens when the user has special characters in their name? An apostrophe for example like O'Hare.

You might want to strip out certain special characters to ensure these don't become part of their username.

What happens when their original username already exists, and their next username also already exists?

You would need to think about a loop that continues until it finds an available username.

What happens if their first username already exists, and they only have 2 characters in their last name? Ma for example.

If one of your users only has 2 characters in their last name, then your flow will fail if they already exist when you try to extract out 3 characters. It would likely be better to just append a number to their name if they already exist.

What happens when the email address is already taken?

In your example you check to confirm a unique username, but then see that their email address is just their first name and last name. What do you do when that email is already taken?

My thoughts: I would consider just sending the user's details to your Active Directory team and get them to manage the usernames and email addresses that they could then send back to you/HR.

[JeffC]

@grantjenkins Thank you grant for your thoughts.  The purpose is to automate the process and not send the problem to sendone else.  Regardless, my company doesn't have an active directory team.  I pull data from a SharePoint list and then export it out to CSV and use powershell to create the accounts.  I generate the username and email addresss in sharepoint.  It works well for us.  I prefer not to use numbers in usernames or email addresses..

[JeffC]

@schwibach This is a great idea but I don't have all my usernames in the SharePoint list.  There are only about 20 in the SharePoint list out of 250 users.

[schwibach]

I agree with Grant in that in larger organisations this is handled differently.

However, when our schools started out with automating the account creation for students, they did something quite similar. And it got them on the track to automate their processes.

What we did with some of them was this:

have a step create the account according to the specs that they wanted.

If that step fails, try with the second option, if that fails, try the third option. If that fails, email to the admin to create the account manually.

That will not give you a completely automated process that handles all exceptions, but it sure beats creating all accounts by hand. And if later on you develop more skills, you can build a better process to handle more exceptions and make it smarter.

[JeffC · Accepted solution]

I figured it out.  I used a length expression instead in the condition to verify if the username existed and it resolved the problem.