Kavi Help CenterKavi® Members Help
| Chapter 24. How to Edit and Delete Records through Upload Data | ||
|---|---|---|
 |
Part I. Kavi Members Concepts and How-tos | 
|
Table of Contents
The Upload Data tool can be used to edit or delete large numbers of companies or users simultaneously anytime the organization needs to manage records in bulk. This document walks Super Admins through the data file preparation process.
The Upload Data tool tests the data for validity before accepting it for upload, but since it is designed to upload records in bulk, it can't check each record individually in the same way that other Kavi Members Admin and User tools can. Errors introduced into the database through the Upload Data tool have the potential to affect multiple records, so there is no substitute for careful file preparation. This requires a great deal of knowledge and careful attention to detail on the part of the Super Admin. You'll get better results if you read and follow these instructions each step of the way, then study the results file before committing the changes to the database.
Note
First time or historic memberships can be added by editing company or user records through the Upload Data tool, but the memberships themselves cannot be edited or deleted.
There are a couple of rules you need to know before you begin.
-
Every user has to be assigned to a valid company, and changes that affect the company record can affect the records of all users associated with that company. This has profound implications for the data upload process.
If you want to change the company name or purpose, edit the company record BEFORE downloading the person.csv data file so that the company name and user purpose fields will already be set to the new values. You may edit the company record through the Upload Data tool or through the Manage a Company tool in the Admin Area.
If you delete a company, all it's users are automatically deleted, so there's no need to delete them separately. On the other hand, if you want to retain these users because of a company merger or similar situation, you must add the company to which they belong, then assign them to this new company by editing the company_name field in the person.csv data file and uploading the person.csv file BEFORE deleting their former company.
The item_key value must be present for each record that you want to edit or delete. If this value is missing or altered, the item_key must be retrieved from the database and the correct value entered before the record can be uploaded.
You can perform multiple actions on a single data file, editing some rows while deleting or adding others, for example. If you want to do this, instructions are provided at the bottom of this document. See Preparing a Mixed-Action Data File.
Instructions for adding memberships during an edit action are provided in a separate document: How to Add Memberships through Upload Data. If you want to add memberships while performing other edits (such as changing the purpose), follow the basic procedures outlined here, then refer to the instructions for adding memberships.
Because the data upload process is complex, there is quite a lot of documentation available.
There are four guides that provide detailed information about all the default data fields, including whether the field is required or not, what value will be inserted in the record if the value isn't specified and tips on how to format the data for best results. You probably want to work with one or more of these guides open in a browser window for handy reference. The data guides are divided into sections on Company Data Fields, Membership Data Fields and User Data Fields.
If you haven't prepared data for this tool before or would like some tips on working with comma-separated values, see Preparing CSV Files for Upload.
When your files are prepared and you are ready to proceed with the upload, consult the Upload Data page help for instructions.
It is possible to perform more than one action in a single upload—adding some records while editing or deleting others. It's generally best to combine only the edit and delete actions, since you usually want to remove all the columns that aren't needed for the edit process to minimize unwanted edits..
To create files with mixed edit and delete actions you might retrieve the data files separately, then combine them in the spreadsheet application or text editor. Prepare the records you want to edit first, since the edit action requires more columns than the delete action.
If you do add records, you'll probably have to leave columns in the data file that you don't want to edit. You'll also have to add rows for each of the records that you need to add. Remember to set the 'upload_action' value to 'add' and leave the 'item_key' field blank.
Back to topAny edits performed on company data have the potential to affect users who belong to that company, particularly if the company's purpose, status or types change. When you are ready to upload your data, upload the company.csv data file first except when you only need to edit user data or you need to move users from one company to another.
To edit a company or user through the Upload Data tool, the record for that company or user must have a valid item key (i.e., a unique ID generated by Kavi Members) in the 'item_key' column.
If the data file is missing a column that appears in the database, or the column is present but no value is specified for that field (the field is empty), whatever value is set in the database is retained (assuming one exists).
Values from the data file are assigned to the company, user or membership (providing the value is valid). If a value already exists in the data file, it is overwritten if a new value is uploaded. If the value in the uploaded file is invalid, the Upload Data tool will return an error message during the validation check.
If required fields are left empty in the data file, the value will be set to the default. The behavior of required fields is often configuration dependent. If the field is required during the signup process, then it is required during data upload. Kavi Members has built-in default values for default fields, but required custom fields may also have default values.
To prepare data files for upload, download the company.csv data file through the links provided on the Upload Data page. Contruct your query to retrieve the narrowest set of records that will suit your purposes to minimize the amount of work it takes to weeding out unwanted records by hand. The downloaded data file will contain all the columns in the database plus the upload_action column, which is prefilled with the 'edit' value.
If you want to add new memberships for companies that already exist in the database, follow the additional file preparation instructions in How to Add Memberships through Upload Data.
Table 24.1. Fields Required by Upload Data for Editing Companies
| Column name | Instructions |
|---|---|
| upload_action |
The Upload Data tool automatically added this column to the company.csv data file and prefilled it with the 'edit' value. Leave this value set to 'edit' for every record (row) you wish to edit. You should remove any records (i.e., rows) that you do not want edited in order to streamline the upload process and prevent the accidental introduction of errors into these rows. You may delete records in this same data file upload. Follow the instructions in Delete companies. |
| item_key | This field must contain a valid item key generated by Kavi Members. |
Table 24.2. Other Required Fields for Editing Companies
| Column name | Instructions |
|---|---|
| display_publicly | Companies that want to be included in the directory should be set to '1'. Set the field to '0' (zero) for any members that should be hidden in order to protect company privacy. The default is '1'. |
| show_on_signup_form | Set this field to '1' if this is a member company that should be listed on the Company Representative Account Signup form. Set this field to '0' for companies that shouldn't appear on the signup form. The default is '1'. |
| status | If this is set to 'active', users who belong to the company to be able to access the website (providing they have roles that confer access). Set the status of companies whose user's shouldn't have website access to 'inactive'. The default is 'active'. |
| signup_date | This field contains the date the company became involved with the organization as a member, prospective member, staff company, registered nonmember, etc. (in YYYY/MM/DD format). It is usually set when the record is added to the database and, once it has been assigned, it shouldn't be overwritten because it is part of the historic record of this company's relationship with the organization. Remove this column from the data file unless you specifically need to set signup dates. |
| company_type | You may edit the Company Types assigned to a company but make sure that you assign all the types the company needs because the value you set here will overwrite whatever is currently in the database. Multiple types appear as a comma-delimited series, separated by commas but no spaces. You may edit the types retrieved from the database by adding or removing types from the series. Types assigned through membership should NOT be assigned here. They should be assigned through regular membership processing, which automatically adds or revokes these types based on membership level. |
Since Kavi Members handles some user management tasks through the company to which they are assigned, it's always a good idea to edit company data before editing user data, so that changes to the company that affect its users will have been propagated to the user records before your edits are introduced.
If you want to add new memberships for individuals who already exist in the database, follow the additional file preparation instructions in How to Add Memberships through Upload Data.
Table 24.3. Fields Required by Upload Data to Edit Users
| Column name | Instructions |
|---|---|
| upload_action |
This is usually the first column in the data file. If you download the 'person.csv' data file from the link on the Upload Data tool it will already be present and the value 'edit' will have been prefilled for every record in this column. If you download a User Data Report, you will have to add this column. For every record (i.e., row) you wish to edit, set the value of the 'upload_action' field to 'edit'. Remember to remove any rows that you do not want to edit in order to streamline the upload process and prevent the accidental introduction of errors into these records. You may delete records in this same data file upload. Follow the instructions in Delete companies. |
| item_key | This field must contain a valid item key generated by Kavi Members. |
Table 24.4. Other Fields Required to Edit Users
| Column name | Instructions |
|---|---|
| company_contact_types | Every user must be assigned at least one Contact Type to establish their relationship to their company. You may edit the Contact Types assigned to a user, but care must be taken to assure that this field contains all appropriate types becuase the value you set here overwrites whatever is currently in the database. Multiple types appear as a comma-delimited series (separated by commas but no spaces). You assign or revoke types by adding or removing types from this series. |
| company_name | The 'company_name' must exactly match the name of an existing company. You can change the user from one company to another by editing the company name in the person.csv data file, but your edits won't affect the company name in the company record stored in the database. If you enter a company name in this column that doesn't exactly match the name of a company in the database, you either have to edit the name in this file or else correct the name in the database using the Manage a Company tool before you can successfully upload this record. |
| display_publicly | Set this field to '1' to display the user's information on the roster. If this user has opted out or your organization's policy is to assume user opt-out, set this to '0' (zero) to have the user's information omitted from the roster to protect privacy. The default is '1'. |
| member_types | These are actually User Types. You may edit the User Types assigned to a user, but care must be taken to assure the user has all appropriate types when the upload completes. Multiple types appear as a comma-delimited series, separated by commas but no spaces. You edit types by adding or removing types from this series. Types assigned through membership are not ordinarily added or deleted during an upload, since these are automatically assigned or revoked through memberships acquisition or expiration. |
| primary_email | If your data includes any primary email address changes, be sure to include this in the data file. Each email address must be unique. If accepted domains are enforced, the Upload Results Email will contain a warning if a user's primary email address doesn't use an accepted domain. |
| receive_email | This is one of the privacy options that users can set if the email opt-out feature is enabled. Set this field to '1' if this user should receive general announcements and email from the Members list or '0' if this user opted out. If a user has indicated they don't want to receive announcements or organization policy specifies that announcements should not be sent to this type of user, set this to '0'. The default is '1'. |
| signup_date | Remove this column from the data file when performing 'edit' actions to avoid overwriting the signup date. |
| status | You may activate or deactivate users by editing this value. Set this to 'active' if the user should be allowed to login, or 'inactive, if not. The default is 'active'. |
| username | Omit this column from the data file when performing 'edit' actions to avoid overwriting usernames. |
Imagine an organization has decided to change Kavi Members configuration to enforce accepted domains, so the Super Admin is adding or editing the records of users whose primary address doesn't contain an accepted domain. The Super Admin has collected a list of accepted domains and user email addresses for each member company. The Accepted Domains field has already been populated in a separate company.csv data upload process. The Super Admin now opens the person.csv data file in a spreadsheet application.
Sample file description:
The required 'upload_action' column is present and already prepopulated with the value 'edit' because the file was downloaded through the Upload Data tool.
The 'item_key' column is present in the downloaded file and the values in this column must not be edited. If the Super Admin accidentally edits an item key field she might download a report containing just that record so she can copy and paste in the correct item key value.
The 'company_name', 'first_name' and 'last_name' columns aren't required by the Upload Data tool, but the Super Admin uses this information since it is more human-recognizable than the 'item_key'. In this scenario the Super Admin would probably resort the rows in the data file by company name so it would be easier to check each user's primary email addresses against the list of allowed domains for their company.
-
The only other column that the Super Admin needs to keep is the 'primary_email' field where all the edits will take place. All other columns are removed from the data file. The Super Admin checks the 'primary_email' field for each user to see whether the address uses an accepted domain. If the row (i.e., record) contains a primary email address with an accepted domain, the Super Admin removes this row from the data file to streamline the upload process and minimize the possibility of introducing errors.
If the primary email address contains some other domain, the Super Admin corrects the email address for that user. If a correct email address isn't available for a specific user, the Super Admin removes that row, also. The Super Admin might store these email address, then send out an email blast inviting these users to login and update their accounts.
In the sample data file below, the first row contains the column headings and the second and subsequent rows contain only those records where the email address is going to be edited.
Here is a simplified version of a sample data file.
| upload_action | item_key | first_name | last_name | primary_email | company_name |
|---|---|---|---|---|---|
| edit | abb1234 | New | Bie | new.bie@example.com | Example Co. |
| edit | arz4567 | Old | Timer | old.timer@example.com | Example Co. |
The Upload Data tool provides the Super Admin with a lot of information about errors in the data files. Hopefully you have only a few errors in your data file and the information provided at the validation step or in the results file is enough to help you remedy any errors. If you have a lot of errors, you may want to go back and reread the instructions while checking your file thoroughly to be sure that it is properly structured, all appropriate fields are present and all values adhere to the expected formats. If your data appears to be complete and accurate and you are still receiving error messages, here are a couple of errors that have been encountered during data upload.
If the Upload Data tool rejects a row because the company or user does not exist and you're quite certain that it does, there may be a typo in one version of the name. To retrieve the record from the database, enter some part of the name that doesn't include any white space and let Kavi Members search for partial matches. If the name in the retrieved record mysteriously appears to be an exact match for the name in the data file, it's possible that the there may be a hidden character in the white space. Double spaces, tabs or carriage returns may display normally when viewed through a browser, so copy the name into a text editor or word processor to see if there are any discrepancies between the name in the Kavi Members database and the name in your upload file.
You may encounter problems uploading files with special characters, even if the character is in the ISO8859-1 character set and the data file was saved in this format. Special characters are problematic and are not supported as standard input to the Upload Data tool.
Since every user must belong to a company, deleting a company will delete all users assigned to that company. Deletion is permanent. If you merely want to hide the company or user from directories, signup forms or rosters, set the company or user status to 'inactive'.
Your file must include the 'upload_action' column. This column is prefilled with the value 'edit', so you have to change this to 'delete' for every record that you want to delete. You can perform multiple actions on a single data file, editing some rows while deleting or adding others, for example. If you want to do this, instructions are provided at the bottom of this document. See Preparing a Mixed-Action Data File. Any rows that should not be changed in the data file should be removed prior to upload in order to streamline the upload process and prevent the accidental introduction of errors into these records.
To delete a company through the Upload Data tool, the record for that company must have a valid item key (i.e., a unique ID generated by Kavi Members) in the 'item_key' column.
The delete action is very simple. It is also very permanent. Deleting a company permanently deletes all users associated with that company. If you merely want to hide the record, set the company or user status to 'inactive'.
Delete actions only require the 'upload_action' and 'item_key' columns, but you may want to include the company name ('name') and other fields so you can see whether you're deleting the right companies.
A Super Admin might want to remove companies from the database that haven't participated in the organization for several years to reduce the database footprint. The search criteria could include companies whose status is 'inactive' and the 'Date Is Before' field of the Membership Expiration Date option could be set to December 31st two years ago (or whatever date is most appropriate).
Sample file description:
This sample shows a set of records for inactive companies that have membership expiration dates more than two years in the past. When the file was downloaded, the 'upload_action' field was prefilled with the value 'edit'. This has to be changed to 'delete'.
When performing a delete action, the only columns you really need are 'upload_action' and 'item_key', but you can leave as many columns as you want since the entire row will be deleted anyway. This example includes the 'name' and 'company_last_modified' fields which have been left in the file for reference. The Super Admin visually checks the 'company_last_modified' field to make sure there hasn't been any activity in that record in the past year. If there is, the Super Admin might look up the company in a report or the Manage a Company tool before deleting the company. As each row is checked, the Super Admin changes the 'upload_action' to delete for those companies that are to be deleted and removes any rows that contain companies that shouldn't be deleted for some reason.
Here is an example of a data file for deleting companies. Unlike the other data files, a file used for a delete operation really is this simple.
| upload_action | item_key | name | company_last_modified |
|---|---|---|---|
| delete | 12345 | Defunct Co. | 2001/05/22 |
| delete | 23456 | Also Defunct Co. | 2002/07/12 |
Deletion actions are simple and permanent. If you merely want to hide the user from rosters and directories, set the user or company status to 'inactive' or archive the membership. These actions will also revoke the user's access to the website.
If you want to delete users who belong to a company that is no longer associated with the organization, just delete the company and all users who belong to that company will be deleted automatically. This doesn't apply to individual members who belong to "companies" with the purpose of 'Company for Individuals'. These companies cannot be deleted, and these users must be removed by deleting their individual records.
Delete actions only require the 'upload_action' and 'item_key' columns, but you may want to include the company name ('name') and other fields so you can see whether you're deleting the right users.
If a Super Admin wanted to remove all inactive nonmember individuals from the database, the search criteria could include users whose Status is 'inactive' and whose Purpose is 'Nonmember Individual'.
Sample file description:
This sample shows a set of records for inactive users that meet the criteria for deletion. When performing a delete action, the only columns you really need are 'upload_action' and 'item_key'. As in all downloaded CSV data files, the 'upload_action' values are preset to 'edit', so the Super Admin changes this to 'delete' in all rows.
This example includes the 'status' and user name information which have been left in the file for reference by the person preparing the data file. When this file is uploaded, every one of these records will be permanently removed from the database.
Here is an example of a data file for deleting users. Files used for delete operations are very simple, so this is a fairly realistic example (although yours would probably have more rows).
|
|
 |
|
| Chapter 23. How to Add Records through Upload Data |  |
Chapter 25. How to Add Memberships through Upload Data |