Query Relationships to Yield ID's

Bob Wiebe

Seeking advice for updating Parent code:  I wish to change about 500 constituents from “Student” to “Former Student”, as they are neither Current Students nor Alumni.  Once I have done that, is there an efficient way to identify their parents and change them from Parent of Current Student to Parent of Former Student?  I have a query/export which will yields their names but not ID numbers which would allow me to make a global change. 

 

Elaine Tucker

create three different constituent queries. first is a student query as a static query. Then create a static query of parents of the students.  Do the global change on your students. Create a different query of Parnts who have a former student constituent code.  make this a static query. merge your first parent query with the second parent query using the operator AND... The resulting parents are the ones you need to change their code. CAUTION this is assuming the parents do not have any current students or alumni attending. if you need to check for current students then create a constituent query for parent of current students and then merge that with the former parents and you can see if there are any parents that should not be updated. I have made the assumption here that the parents also have constituent records.

Jane Owsley

Hello.  This step is perhaps the most tedious part of transitioning student records!  I hope the suggestion below is helpful.



I first create a Relationship Query to gather the newly graduated / left students by setting the correct date range for “Date Graduated” or “Date Left” in the criteria.  (E.g. Date Graduated is greater than or equal to 5/1/2015 or Date Left is greater than or equal to 5/1/2015).

           

I attach this Relationship Query to an Individual Relationship Export to export out the Import IDs for the Parent Constituent Code.  Make sure to export out the Relationship type since all the Indv Relationships found in the parent record will be exported.

              

Then I follow these steps to edit the Excel document to create a document that has an unduplicated row for each parent's record that needs a Date To value:

1. Sort by the Relationship Type column and delete all rows besides those defining the relationship to the Parent record as Child, Daughter, or Son.
2. Sort by the Date To column and delete any row/record if Parent Const Code already has the appropriate Date To date.  If date reflects a former student/child graduation date, edit in the newest Date To date based on their child’s Date Graduated or Date Left date.
3. Once you have an unduplicated row for each Parent's record to edit, add in the appropriate Date To date based on their child’s Date Graduated or Date Left date.
4. Prep spreadsheet to re-Import Parent Const Code, now with appropriate Date To added, using the Constituent Code Import.

Bob Wiebe

Jane Owsley:

Hello.  This step is perhaps the most tedious part of transitioning student records!  I hope the suggestion below is helpful.



I first create a Relationship Query to gather the newly graduated / left students by setting the correct date range for “Date Graduated” or “Date Left” in the criteria.  (E.g. Date Graduated is greater than or equal to 5/1/2015 or Date Left is greater than or equal to 5/1/2015).

           

I attach this Relationship Query to an Individual Relationship Export to export out the Import IDs for the Parent Constituent Code.  Make sure to export out the Relationship type since all the Indv Relationships found in the parent record will be exported.

              

Then I follow these steps to edit the Excel document to create a document that has an unduplicated row for each parent's record that needs a Date To value:

1. Sort by the Relationship Type column and delete all rows besides those defining the relationship to the Parent record as Child, Daughter, or Son.
2. Sort by the Date To column and delete any row/record if Parent Const Code already has the appropriate Date To date.  If date reflects a former student/child graduation date, edit in the newest Date To date based on their child’s Date Graduated or Date Left date.
3. Once you have an unduplicated row for each Parent's record to edit, add in the appropriate Date To date based on their child’s Date Graduated or Date Left date.
4. Prep spreadsheet to re-Import Parent Const Code, now with appropriate Date To added, using the Constituent Code Import.

Thanks for the reply, Jane.  I would welcome some further clarification.  The list I am working with does not at this point have the benefit of a complete set of data such as date graduated.  But I do have a group of students whom I can give a unique constituent code.  I could then use the relationship query (new to me) to identify their parents by name and code type and import ID.  What I want to do at that point, and need a solution for, is to take that import ID into a new query and then globally change the constituent code of those parents. I have not found that import ID's produce any output results, but maybe I don't know how to use them.  Any further advice?

 

Bob Wiebe

I did some more consultation with colleagues and realized that one can pull a good old fashioned constituent ID from the Relationship Export.  This may be what I need. 

Jane Owsley

Hello.  If you can assign a unique constituent code to the group of student, you can use this criteria in your Relationship Query to gather the group of students (Constituent Code = _____).  Then attach this Relationship Query to a Relationshp Export to export out all of the Student's Individual Relationships as well as the Parent's Import ID (or Constituent ID) and the Parent's Constituent Code ID.



The first attachment includes a screen shot of the data I export out with the Relationship Export.



Then, instead of a Global Change, use the Constituent Code Import to add in the end date you want for the Parent Const Code.  The 2nd attachment shows an example of the column headers needed to make the Constituent Code Import work (highlighted in yellow).



Hope this helps, willing to discuss more if this is the direction you want to take.



Jane

Bob Wiebe

Jane Owsley:

Hello.  If you can assign a unique constituent code to the group of student, you can use this criteria in your Relationship Query to gather the group of students (Constituent Code = _____).  Then attach this Relationship Query to a Relationshp Export to export out all of the Student's Individual Relationships as well as the Parent's Import ID (or Constituent ID) and the Parent's Constituent Code ID.



The first attachment includes a screen shot of the data I export out with the Relationship Export.



Then, instead of a Global Change, use the Constituent Code Import to add in the end date you want for the Parent Const Code.  The 2nd attachment shows an example of the column headers needed to make the Constituent Code Import work (highlighted in yellow).



Hope this helps, willing to discuss more if this is the direction you want to take.



Jane

Thanks for the options and the demo, Jane.