Calculating login username using given name and the first letter of surname? (1 Viewer)

Sampoline

Member
Local time
Today, 21:06
Joined
Oct 19, 2020
Messages
161
I have a table of contacts that has the first and last names of employees. I want to use the full given name and the first letter of the surname to create a login username.

For example:

1607648931266.png


Wondering if I can make the username field as calculated and create an expression to use the names to create the username. Wasn't sure how to go about making the words all lower case as well. Is this possible?
 

plog

Banishment Pending
Local time
Today, 05:06
Joined
May 11, 2011
Messages
11,611
I don't think it should be a calculated field for 2 reasons:

1. They shouldn't be used in a database. Instead when you need a value calculated you should calculate it in a query and reference the query when you need that value.

2. They won't always be calculable. John Smith and John Stevenson would have the same username if you relied on a function to determine them. Most likely you would then use johns2 or johnst for Stevenson's' username.

Because of #2 I think you should keep the username field as it is (not calculated) and instead rely on the input form you use to populate that table to validate and/or suggest usernames.

The functions you would need are:

Mid - https://www.techonthenet.com/access/functions/string/mid.php

LCase - https://www.techonthenet.com/access/functions/string/lcase.php
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:06
Joined
Oct 29, 2018
Messages
21,357
I agree. How were you planning to handle potential duplicate usernames?
 

Sampoline

Member
Local time
Today, 21:06
Joined
Oct 19, 2020
Messages
161
That's a good point. Our department is small in size and I know them all by names. So I know that none of them will run into duplicate usernames. But still I would like to do it the right way. I'll try to do it as you suggested plog. Thanks. Will update.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:06
Joined
Sep 21, 2011
Messages
14,038
That's a good point. Our department is small in size and I know them all by names. So I know that none of them will run into duplicate usernames. But still I would like to do it the right way. I'll try to do it as you suggested plog. Thanks. Will update.
And you are never going to employ anyone else EVER? :)
 

Sampoline

Member
Local time
Today, 21:06
Joined
Oct 19, 2020
Messages
161
And you are never going to employ anyone else EVER? :)
No you guys are completely right, I should never treat the employee list as rigid. Anything can happen and staff can change anytime.
 

Cronk

Registered User.
Local time
Today, 21:06
Joined
Jul 4, 2013
Messages
2,770
If network usernames are allocated, why not use that in your db?
 

Sampoline

Member
Local time
Today, 21:06
Joined
Oct 19, 2020
Messages
161
If network usernames are allocated, why not use that in your db?
Hi Cronk,

So how would I populate my contacts table with those user names? Is that simply done with some query or..?
 

Cronk

Registered User.
Local time
Today, 21:06
Joined
Jul 4, 2013
Messages
2,770
The network username is readable by access when the user opens the database. Checkup
environ("Username")

Populating automatically depends on whether there is a standard for network usernames that has been applied across all users and of course, can't pick up situations like johns9 or jsmith2
 

Users who are viewing this thread

Top Bottom