The student names are all in the database in this format:
Last Name(s), First Name(s) Middle Name(s)
If you need that precision, then create auto-enter text fields with this calculation:
Last_clean = Filter(Lastname; "ABCDEFGHIG...XYZabcdefg...xyz") // use all upper and lowercase a-z, of course
Filter will remove character NOT in the filter list.
Sort by the filtered field.
ps. create the auto-enter fields as needed. You might even have one that combines all the "name fields" in a sort-order you need. Since auto-enter is for NEW records, you will need to update the existing records with Replace()
Or maybe just create a single calculation text field field as
Let ( [
allowed = "abcdefghijklmnopqrstuvwxyz" ;
complete = Last & First & Middle
Filter ( Lower ( complete ) ; allowed )
So I created a new field and clicked calculation under Type. I used this formula:
It gave me an error when i tried to include the Last_Clean= part.
Student is the name of the field that contains the student names.
I cannot remove the apostrophes and spaces from the name because then it won't match up to official documentation, I just need it to ignore them when sorting.
But when I sort by the new field that contains that calculation it fixes the problem with names with spaces in them but still puts the names with apostrophes in the wrong order.
imho the allowed list should start with a space.
Won't that tell the system that spaces are allowed and bring the problem of missorting names with spaces back?
Well I'd like to have Brown Mark before Brownie Ann
if you exclude spaces it will come after.
Which is why we suggested putting in however one prefers the sort.