Import sets and data management

[Earl L]

We're going through some of the imports for users, departments, locations, etc. to get our instance ready for go-live. Right now I'm focusing on users and departments.

There was an initial import of users and departments done but they weren't quite right in terms of the data that was used and the references between the tables. There was a very quick/dirty import of departments and the department name column actually contains the department number. There are no department names loaded into the table, so when you look at a user record you see this department number rather than the name of the department. This needs to be changed so department names appear rather than that number.

My question has to do with the best approach for dealing with this kind of "data anomaly", in a general sense, but we'll use my current situation as a specific example. What I thought I would do is the following:

1) Either by import or server-side script run through the entire users table and null out the department column values.
2) Delete all the department records in the table as they stand currently.
3) Setup the import set for departments correctly and complete the import.
4) Adjust the user import to account for the new department name information (honor the reference from sys_user.department column to cmn_department.name).

First, is the the correct approach to fixing these kinds of problems? If not, what would you recommend?
If I do need to remove the department values from the sys_user table is it practical/better to do it with a script or is the import the best mechanism?

Any comments or suggestions are greatly appreciated. Thanks.

Earl

[cwilker10]

It sounds like you added the Departments from the User Record import which would have taken the "Name" as the Display of any Departments being added. What you could do is use the "Referenced value field name:" from within the Transform Map Field Map to get that to work out correctly. You could then come back and fill in the rest of the Department information like name and managers, etc. if you have that information elsewhere.

My suggestion is that since you're not live, you can clear out the Department table and then do the import again. If you wanted to Update the data, you could run the transforms again and see where it leaves you given your data sources as it should "Update" the necessary records, but might not give you exactly what you're looking for. If you need do, you could run a quick Scripts - Background to move values around, but I think you should look at your Transform Map closer before running a maintenance script.

[Earl L]

Chris,

Thanks for your response. I'm pretty new to this whole thing so I'm not sure what you mean by "Referenced value field name: from within the Transform Map Field Map".

I've worked with the transform mapping and the mapping assistant so I know what the transform mapping and field map are, but not sure about the "Referenced value field name." If this is something in the SN UI I'm not sure what you're referring to. Can you help me understand that? Thanks again for your response.

Earl

[cwilker10]

Earl,

If you open up one of your Field Maps(After doing the Mapping Assist), you should see the field that I'm talking about on the RIGHT column.

-Chris

[Earl L]

OK Chris, got it now. It's a column in the field map list that wasn't shown in our table so I personalized the list layout and displayed it. Interestingly enough there are no values set in this column for our user data import.

I'll poke around with this and see if I can make more sense of it and let you know if I have more questions. Thanks for the tip.

Earl