Virtual fields are the key to avoiding manual entry. There are many possibilities ranging from simple profile additions, to complex logic that can help make your imports do all your hard work!
Adding a Field to Every Record in Your Import
This is the simplest way to use a virtual field, but the most common. You can set the function to Static, which means the text in the “Virtual Field” column will be imported to the field mapped in the “Raiser’s Edge Field” column. This is used when you want to add the same data to every record in your import. This example would add a Constituent Code of “Member” to every record coming in through your data file:
You can use virtual fields to add anything to your import such as Letter Codes, Campaigns, Funds, or Appeals.
Using a Field to Populate Not Just One, But Two Fields in RE
By using the “Copy” Function, you can copy the incoming data from an existing field in your import and map it to an additional field. Copy Functions require that you select a “Seed”, which is the original field you are going to copy from. In the example below, I want to copy Gift Date, and map it to GL Post Date. Gift Date is already mapped in Field C in my profile, so I can’t map field C to GL post date. Therefore, I create a Virtual Field mapped to GL Post Date:
Using a Field to Conditionally Populate an Additional Field in RE
This is one of the more complex ways you can use Virtual Fields. By utilizing functions and dictionaries, you can populate almost anything you need in your Virtual Field! In this example, you are importing a Constituent and up to 2 child relationships.
For each relationship, you want to add Parent/Child as the relationship and reciprocal. Therefore, you add 4 Virtual Fields (one for relationship and reciprocal for each Child). However, it’s possible that the constituent only has one child, and the data in columns E and F is blank. If you were to make all the Virtual Fields have a Static Function, it would try to populate the relationship/reciprocal field for the second Individual Relationship, even if there was no data in column E and F. Therefore this would only work if EVERYONE had two children:
Under the assumption that some parents only have one child, we can’t use the above example, and would instead need a conditional Virtual field to add the Parent/Child Relationship and Reciprocal only if there is data coming in for the Child.
To do this, we would use a Copy Function instead of a Static Function. The Seed would be field D or F, which is the Child’s last name. The child last name field is what will determine if the Relationship and Reciprocal are populated.
Since we don’t want the actual Last Name to be the relationship/reciprocal, we would then need a dictionary that translates the child’s last name into either Child or Parent. To do this, we need one dictionary that says “If there is anything in the Child Last Name field, translate that data into Child” and another that says “If there is anything in the Child Last Name field, translate that data into Parent”. This will require a regular expression dictionary. When using regular expressions, make sure to select the “Use Regular Expressions” checkbox at the bottom of the dictionary. You can view the most commonly used regular expression dictionaries here.
For the relationship field, we want to translate Child Last Name into “Child”, or leave as blank if there is no incoming data. Here is the dictionary for “If Anything, translate into Child:”
For the reciprocal field, we want to translate Child Last Name into “Parent,” or leave as blank if there is no incoming data. Here is the dictionary for “If Anything, translate into Parent:”
Now we are ready to apply these dictionaries to our Virtual Fields. Ensure that you have appropriately grouped your fields for each relationship, applied the correct dictionaries, set the function to “Copy” and applied the seed field:
This will ensure that the child relationship is added with a Relationship and Reciprocal, but ensure you don’t get any errors if the second relationship doesn’t exist. Now, go have some fun with Virtual Fields and see how much manual data entry you can avoid!
