Kids To Move¶
This script is subject to change and is still being tested.
This script does not run the OrgSearch BlueToolbar but it can be added to the main menu. The list of children comes from regular members of any Organization with the ExtraValue Integer value on the org called MinMonths and MaxMonths.
These values, MinMonths and MaxMonths, are the range of month ages for children in the org inclusive.
You would set up a list of these organizations, one per range, with no gaps. In other words, 0-3, 4-6, 7-8 etc.
The report shows which children will be outside of the appropriate range on the next Sunday from the current date when the report is run. And it will display what organization they are in now, and what the appropriate organization should be. These Organizations are labeled FromOrg and ToOrg. All children in these Organizations will be displayed with a true/false indicator in the last column called Moving. The ones to move are sorted to the top by age.
The report can be used in a Python Script to actually do the moves, either manually or on a schedule. Keep in mind that once the children are moved, the report will no longer show any kids to move for this week. So to document which kids were moved, you may want to save the report or the Excel download before moving.
The Python script to Move Kids is documented at Move Kids
The recommended name is
Use the following code to Create the SQL Script. See How to create a SQL Script.
WITH kids1 AS ( SELECT FromOrgId = om.OrganizationId ,FromOrg = o.OrganizationName ,om.PeopleId ,p.Name ,BirthDate = CONVERT(DATE, dbo.Birthday(om.PeopleId)) ,NextSunday = CONVERT(DATE, dbo.SundayForDate(DATEADD(WEEK, 1, GETDATE()))) FROM dbo.OrganizationMembers om JOIN dbo.People p ON p.PeopleId = om.PeopleId JOIN dbo.Organizations o ON o.OrganizationId = om.OrganizationId WHERE om.MemberTypeId = 220 AND EXISTS(SELECT NULL FROM dbo.OrganizationExtra e WHERE e.OrganizationId = om.OrganizationId AND e.Field = 'MinMonths') ), kids2 AS ( SELECT FromOrgId ,FromOrg ,PeopleId ,Name ,BirthDate ,MosAge = dbo.AgeInMonths(PeopleId, NextSunday) FROM kids1 ), orgs AS ( SELECT ToOrgId = o.OrganizationId ,MinMos = oemin.IntValue ,MaxMos = oemax.IntValue FROM dbo.Organizations o JOIN dbo.OrganizationExtra oemin ON oemin.OrganizationId = o.OrganizationId AND oemin.Field = 'MinMonths' JOIN dbo.OrganizationExtra oemax ON oemax.OrganizationId = o.OrganizationId AND oemax.Field = 'MaxMonths' ), kidstomove as ( SELECT p.FromOrgId ,p.FromOrg ,p.PeopleId ,p.Name ,p.BirthDate ,p.MosAge ,o.ToOrgId ,ToOrg = oo.OrganizationName ,o.MinMos ,o.MaxMos ,Moving = CAST(CASE WHEN p.FromOrgId <> o.ToOrgId THEN 1 ELSE 0 END AS BIT) FROM kids2 p JOIN orgs o ON o.MinMos <= p.MosAge AND o.MaxMos >= p.MosAge JOIN dbo.Organizations oo ON oo.OrganizationId = o.ToOrgId ) select * from kidstomove where 1=1 order by moving desc, maxmos
Modify image link with secure protocol.