Alternative Layouts for Upload Files

The WP Issues CRM Upload function can upload csv-type files in many layouts. It requires a .csv ( or .txt ) formatted file, but using that basic format, there are a number of possible file layouts that it will accept for uploading different kinds of data.

This document explains the possibilities. You don’t need to plow through this unless you are trying to do something unusual. In most cases, the steps should make sense intuitively.

WP Issues CRM gives you step-by-step control over how your data is uploaded. In the examples below, the column headers chosen are just illustrative — you can map any column header to the appropriate WP Issues CRM field in the Map Fields step. You can also upload a file without column headers. Tip: At the Map Fields stage, you can see the underlying data in a confusing or system-supplied column header by hovering over the column header.
Adding Constituent Records

If you were starting from scratch, uploading the file below would create four new constituents.

fname, lname, city, dob
jack,jones,belmont,1990-01-01
john,jones,belmont,1991-01-01
jane,jones,belmont,1992-01-01
jill,jones,belmont,1993-01-01

When you are starting from scratch or know that you are adding constituents that are new to your database, you can “Run Express” and bypass the validation and matching stages.
Updating Constituent Records

Suppose you had a file of updated addresses for the same people. Jack Jones has moved from Belmont to Cambridge. Upload this file and select matching by first name, last name and date of birth. The addresses will be updated provided you give them the same address type as in the upload step. (Note that addresses will never be updated unless you uncheck “Protect Primary Constituent Data” at the Set Defaults stage.)

You could, instead, define all the new addresses as work addresses and then the people would all have two addresses, regardless of the protection setting.

fname, lname, city, dob
jack,jones,Cambridge,1990-01-01
john,jones,belmont,1991-01-01
jane,jones,belmont,1992-01-01
jill,jones,belmont,1993-01-01

WP Issues CRM will also accept a file that is a mix of updates and additions.

fname, lname, city, dob
jack,jones,Cambridge,1990-01-01
john,jones,Boston,1991-01-01
jane,jones,belmont,1992-01-01
jill,jones,belmont,1993-01-01
millie,murphy,New York,1963-01-01

Based on the match criteria you define in the Define Matching stage, WP Issues CRM will decide whether to add a new record or update an existing record. At the Set Defaults stage, you can elect not to update records that match (so just bypassing them, if for example, you thought they were just duplicates). Similarly, you can elect not to add records that don’t match (in effect, bypassing them as errors).
Adding Activity Records

As you do updates and additions of constituent records, you can, at the same time add activity records for the constituents.

fname, lname, city, dob, activity_type, activity_date, activity_issue
jack,jones,Cambridge,1990-01-01,1,2015-05-06,843
john,jones,Boston,1991-01-01,1,2015-05-06,843
jane,jones,belmont,1992-01-01,1,2015-05-06,843
jill,jones,belmont,1993-01-01,1,2015-05-06,843
millie,murphy,New York,1963-01-01,1,2015-05-06,843

In the example above, each of the constituents would get an activity added of type 1 on the date May 6, 2015 with respect to issue #843. The issue number is just the postID of a WordPress post (Issues in WP Issues CRM are just posts). Note that the issue must be open for activity assignment — it should be set to “Always appear in issue dropdown”. If not, the record will bounce as invalid. In this example, all the records had the same activity_type, activity_date and activity_issue — however, they could all be different.
Creating New Issues

You can create new issues as you upload data:

email,post_title
jack@jones.com,”Free Jim Jones”
john@jones.com,”Free Jim Jones”
jane@jones.com,”Free Jim Jones”
jill@jones.com,”Free Jim Jones”
milliemurphy@hotmail.com,”Save the trees”

The file above might be exported from your email client — a set of emails received on a given date — four on one subject, one on a different subject. Unless the titles matched existing titles, the upload would create two new issues with the titles “Free Jim Jones” and “Save the Trees.” You would be required to confirm new issue creation at the Set Defaults stage. If the title already existed, you would just create a new activity.

Whether or not the titles existed, this example would create an activity record for each email received.

Note that you could include in your upload the content of the email itself or some other content as the content of the new issue/post to be created. Or, you could go back in through WordPress or WP Issues CRM to fill out content for the post later.

Tip: If you are including content for new issue creation, repeat it on each record (as would normally happen if you downloaded a collection of emails). Among a set of records with the same title, it is not predictable which record the content will be taken from.

In this example, the only identifier supplied is email address. If the email addresses were not in the database, new constituents would be created with those email addresses. If the emails were already in the database, the new activity records would be added to the appropriate constituents.

Tip: Matching is not sensitive to email type, but the update process is. So, if the email address existing on the database is a “personal” email and you specify “work” email in Set Defaults for the upload, then the matching constituent will be updated with the new activity, but will also pickup a “work” email address record with the matching email. So, it’s good practice not to use an unnecessary multitude of email types.
Using Internal Identifier for Linking

If you wanted, for example to add phone numbers for your database using an external service, you could download your database (by entering a blank search) and then send it out for phone number addition. When the file comes back with phone numbers added, you could apply the purchased phone numbers to your database, by linking on ID (which is included in the standard WP Issues CRM download file). You could just delete all the other columns in the file and upload a file with just two columns.

id,phone
1,617-999-9999
2,617-999-9998
3,617-999-9999
4,6179999999
12312,6179999999

Note that in the 4th row, the phone number is formatted differently. That would not create a validation error. WP Issues CRM discards non-numeric characters for phone numbers, so the 3rd and 4th rows look the same. The fifth row has an out-of-sequence ID, also not a problem, provided there is a constituent with that ID.
Mixed Record Types

WP Issues CRM needs a consistent set of columns to map, so every line of the input file needs to have the same columns. The following would NOT work:

fname, lname, city,
activity_type, activity_date, activity_issue
jack,jones,Cambridge,
1,2015-05-06,843
john,jones,Boston,
1,2015-05-06,843

However, WP Issues CRM can handle files in which the columns are consistent, but for the constituent fields onlysome rows have blank values.

For example, if fname, lname, dob are the match variables, and there were no matches for jack and jane, the following file would create just two new constituent records combining across identical fname, lname, dob combinations.

fname, lname, dob, gender, occupation, employer
jack,jones,1980-01-01,,janitor,”Google, Inc.”
jack,jones,1980-01-01,m,,”Google, Inc.”
jack,jones,1980-01-01,m,janitor,””
jane,jones,1982-01-01,,executive,”Apple, Inc.”
jane,jones,1982-01-01,f,,”Apple, Inc.”
jane,jones,1982-01-01,f,executive,””

The result would be equivalent to:
fname, lname, dob, gender, occupation, employer
jack,jones,1980-01-01,m,janitor,”Google, Inc.”
jane,jones,1982-01-01,f,executive,”Apple, Inc.”

In the constituent creation process for unmatched records, records with the same identifiers are grouped together and the maximum values are taken for constituent fields. Note that the grouping process does NOT apply to address, phone, email or constituent details. So, for example, the following would create only one constituent record, but with an address record including only the city:
jane,jones,1984-01-02,”123 main st”,,””
jane,jones,1984-01-02,””,belmont,””
jane,jones,1984-01-02,””,””,02748

Leave a comment

Your email address will not be published. Required fields are marked *