How to change order of info in relational DB

[garrettks]

I have two databases. The 1st which is a Staff Directory that includes the name, extension, and e-mail of everyone in my organization. The 2nd database is used to track correction requests to our website, usually these requests are made by other staff members. The way I want the information listed in the (1) Staff Directory is LastName, FirstName. Then when I enter the employees extension in the (2) Corrections database I want it to automatically pull the employees name into the name field of that database (which I have figured out how to do) I want the name to be FirstName LastName in this database though. As of now its pulling Last Name, FirstName because that is how its formatted in the (1) Staff Directory. How would I go about doing this?

[LiveOak]

I'm a little confused. Are you referring to the Sort order you wish or the order in which records are displayed in a Portal? In the first case, just sort the file. In the second case define a sort order for the relationship.

-bd

[garrettks]

I'm talking about the way the info appears in the (Portal?) the Name field in the 2nd database. So I need to change the sort type, how would I change from LastName, Firstname which is the data it pulls from database 1, to FirstName LastName which is what I want in database 2? Can you provide more details, I'm still kind of new at this...

Thanks much!

Garrett

[garrettks]

One very important thing I forgot to mention. The LastName, FirstName is in one field called name. Therefore when I pull the info into my 2nd database its LastName, FirstName in one field. I need to somehow rearrange the data in that field only in the 2nd database.

Garrett

[AndriesV]

If you don't want to change a lot you could create a calculated FirstNameLastName field in the MasterFile as:

Middle([LastFirst], Position([LastFirst], "," ,1 , 1) + 2,

Length([LastFirst]) - Position([LastFirst], "," ,1 , 1) +1)

& ", " & Left([LastFirst], Position([LastFirst], "," ,1 , 1) -1)

This however will only work as long as all [LastFirst] Fields are properly filled with ", " as separation, so you may need to modify the calculation a bit to handle exceptions....

To avoid this (and probably more future) issue(s) with FirstName & LastName create 2 separate field to hold the FirsName and LastName. For displaying them together just create a calculated (text) field as: [LastName] & ", " & [FirstName]. To convert existing records create a 1 time script that loops through all records with similar calculations as above to strip out First-and Lastname from the combined field.

In general to sort the same related date differently in 2 portals you need 2 relationships to the same related file but with different sort orders, name the relationships so that it's obvious which sort-order is used for which relationship.

[garrettks]

Andries,

You're awesome. One more question now the calculated field is coming up FirstName, LastName. How do I get rid of the comma in between? I've tryed playing with the calculation by replacing the "," with " " but that doesn't seem to work.

Thanks,

Garrett

[garrettks]

Figured it out. Thanks again!