• Replies 8 replies
  • Subscribers 414 subscribers
  • Views 517 views
  • Users 0 members are here
Related

V15 Bulk Change Primary worker?

Megan Peet
$organization

Hello,

Is there a way to bulk change the primary worker on a plan in v15? We just had a fundraiser leave, so I need to transfer all of her plans over to other gift officers. They're not all going to the same person. I already used the worker tab in the plans maintenance module to add the new workers onto the plans, but the departed officer is still the primary.

Any advice would be appreciated. I'd also be open to using SQL if anyone has ideas on how to structure it.

Thanks,

Megan

Parents
  • $organization

    I do this through SQL, Megan. Here's what I've worked out so far, you'll need to change some id#s to suit your organization but hopefully it'll get you started.

    Fullscreen Plans - assign new Primary Worker.sql Download 
    --compare current 0Moves Worker 
select *
from T_LIST_CONTENTS a
left outer join T_PLAN b
on a.customer_no=b.customer_no
left outer join TX_CUST_PLAN c
on b.plan_no=c.plan_no
join T_CUSTOMER d
on c.customer_no = d.customer_no
where a.list_no='5544'
and b.campaign_no = '1456'--0moves campaign
and c.role_no = '2'--Worker
and c.primary_ind = 'y'


begin tran
--1. change Primary status on plans where id# is already a Worker and make current Worker into Secondary Worker

-- 1a. make old Worker NOT primary and type Secondary
update TX_CUST_PLAN
set primary_ind='N', role_no=6 --Secondary Worker 
where plan_no in 
(select b.plan_no from T_LIST_CONTENTS a
left outer join T_PLAN b on a.customer_no=b.customer_no
where a.list_no='5544' and b.campaign_no = '1456')--which plan id#s to update - 0moves plans for accounts in this list
and customer_no!=1041964 --new primary worker
and role_no = '2'--which worker assigned to plan to update

--1b. make new Worker primary
update TX_CUST_PLAN
set primary_ind='Y'
where plan_no in 
(select b.plan_no from T_LIST_CONTENTS a
left outer join T_PLAN b on a.customer_no=b.customer_no
where a.list_no='5544'and b.campaign_no = '1456')--which plan id#s to update - 0moves plans for accounts in this list
and customer_no=1041964 --new primary worker
and role_no = '2'--which worker assigned to plan to update



--2. change current Primary Worker to new id# without keeping old worker
update TX_CUST_PLAN
set customer_no=1041964 --new primary worker
where plan_no in 
(select b.plan_no
from T_LIST_CONTENTS a
left outer join T_PLAN b
on a.customer_no=b.customer_no
where a.list_no='4275'
and b.campaign_no = '1456')--which plan id#s to update - 0moves plans for accounts in this list
and role_no = '2'and primary_ind = 'y'--which worker assigned to plan to update

commit

rollback

---find plans not updated--probably because current primary worker has different role or no primary worker to update
select a.customer_no,b.plan_no
from T_LIST_CONTENTS a
left outer join T_PLAN b
on a.customer_no=b.customer_no
where a.list_no='4275'
and b.campaign_no = '1456'
and b.plan_no not in 
(select b.plan_no
from T_LIST_CONTENTS a
left outer join T_PLAN b
on a.customer_no=b.customer_no
left outer join TX_CUST_PLAN c
on b.plan_no=c.plan_no
join T_CUSTOMER d
on c.customer_no = d.customer_no
where a.list_no='4275'
and b.campaign_no = '1456'
and c.role_no = '2'
and c.primary_ind = 'y')

  • $organization in reply to Jim Reynolds

    This is enormously helpful! Thanks especially for the good notes. For my purposes (and for anyone else reading this looking for advice), I think I'm going to have to make several lists depending on campaign and new primary worker, because not all plans in a given campaign have the same new worker. I'll report back. 

  • $organization in reply to Megan Peet

    You're welcome. Exactly that - our prospect team gave me a list of accounts and asked me to change the primary worker on our main prospecting campaign, so yes, for this one specific Plan campaign, make this one specific person the primary.  In some scenarios, it was just moving a bunch of accounts to a new employee in which case we wanted to keep the existing worker but as non-primary. In other scenarios, this wasn't necessary. As always, run in TEST first, wear safety goggles, etc etc.

  • $organization in reply to Jim Reynolds

    Since I had already added the new worker to all of the plans I had to adjust, I ended up paring this down quite a bit to just remove the Primary indicator from the old worker and add it to the new worker. It's working so far in TEST.  I'm not sure how to insert the code the way you did it, but here's what I have:

    update TX_CUST_PLAN
    set primary_ind='N'--change primary to non primary
    where plan_no in
    (select b.plan_no from T_LIST_CONTENTS a
    left outer join T_PLAN b on a.customer_no=b.customer_no
    where a.list_no='62308' and b.campaign_no = '506')--which plan id#s to update - 0moves plans for accounts in this list
    and customer_no=379542 --old worker



    update TX_CUST_PLAN--change primary worker
    set primary_ind='Y'--Primary worker
    where plan_no in
    (select b.plan_no from T_LIST_CONTENTS a
    left outer join T_PLAN b on a.customer_no=b.customer_no
    where a.list_no='62308' and b.campaign_no = '506')--which plan id#s to update - 0moves plans for accounts in this list
    and customer_no=333079 --NEW worker

  • $organization in reply to Megan Peet

    Thanks Jim & Megan for your super helpful code! I was able to adapt the code to also include adding the exact role I wanted and also in a second step remove other unnecessary workers too. 

    Switching primary workers (while simultaneously removing the current primary worker (I also included the view table so that you can see your progress as you update:

    update TX_CUST_PLAN
    set customer_no=201754, role_no=3 --new primary worker, staff solicitor role id
    where plan_no in
    (select b.plan_no
    from T_LIST_CONTENTS a
    left outer join T_PLAN b
    on a.customer_no=b.customer_no
    where a.list_no='28413'
    and b.campaign_no = '299') --campaign id for moves management plans
    and customer_no=200890 and primary_ind = 'y'--which worker assigned to plan to update

    select a.list_no, a.customer_no, b.plan_no, c.primary_ind, c.role_no,
    d.customer_no AS worker_no, d.fname, d.lname
    from T_LIST_CONTENTS a
    left outer join T_PLAN b
    on a.customer_no=b.customer_no
    left outer join TX_CUST_PLAN c
    on b.plan_no=c.plan_no
    join T_CUSTOMER d
    on c.customer_no = d.customer_no
    where a.list_no='28413'
    and b.campaign_no = '299' --Moves Management
    Order by primary_ind Desc, worker_no

    Removing unnecessary workers: 

    DELETE From TX_CUST_PLAN
    where plan_no in
    (select b.plan_no
    from T_LIST_CONTENTS a
    left outer join T_PLAN b
    on a.customer_no=b.customer_no
    where a.list_no='28411'
    and b.campaign_no = '299')
    AND customer_no=86830 
    Or customer_no=200891 -- if you have more than one worker you want to remove
    AND primary_ind='N' -- Included this so that I wouldn't accidentally delete the primary worker if they were duplicated in the plan because of multiple roles


    select a.list_no, a.customer_no, b.plan_no, c.primary_ind, c.role_no,
    d.customer_no AS worker_no, d.fname, d.lname
    from T_LIST_CONTENTS a
    left outer join T_PLAN b
    on a.customer_no=b.customer_no
    left outer join TX_CUST_PLAN c
    on b.plan_no=c.plan_no
    join T_CUSTOMER d
    on c.customer_no = d.customer_no
    where a.list_no='28411'
    and b.campaign_no = '299' --Moves Management
    Order by primary_ind Desc, worker_no

Reply
  • $organization in reply to Megan Peet

    Thanks Jim & Megan for your super helpful code! I was able to adapt the code to also include adding the exact role I wanted and also in a second step remove other unnecessary workers too. 

    Switching primary workers (while simultaneously removing the current primary worker (I also included the view table so that you can see your progress as you update:

    update TX_CUST_PLAN
    set customer_no=201754, role_no=3 --new primary worker, staff solicitor role id
    where plan_no in
    (select b.plan_no
    from T_LIST_CONTENTS a
    left outer join T_PLAN b
    on a.customer_no=b.customer_no
    where a.list_no='28413'
    and b.campaign_no = '299') --campaign id for moves management plans
    and customer_no=200890 and primary_ind = 'y'--which worker assigned to plan to update

    select a.list_no, a.customer_no, b.plan_no, c.primary_ind, c.role_no,
    d.customer_no AS worker_no, d.fname, d.lname
    from T_LIST_CONTENTS a
    left outer join T_PLAN b
    on a.customer_no=b.customer_no
    left outer join TX_CUST_PLAN c
    on b.plan_no=c.plan_no
    join T_CUSTOMER d
    on c.customer_no = d.customer_no
    where a.list_no='28413'
    and b.campaign_no = '299' --Moves Management
    Order by primary_ind Desc, worker_no

    Removing unnecessary workers: 

    DELETE From TX_CUST_PLAN
    where plan_no in
    (select b.plan_no
    from T_LIST_CONTENTS a
    left outer join T_PLAN b
    on a.customer_no=b.customer_no
    where a.list_no='28411'
    and b.campaign_no = '299')
    AND customer_no=86830 
    Or customer_no=200891 -- if you have more than one worker you want to remove
    AND primary_ind='N' -- Included this so that I wouldn't accidentally delete the primary worker if they were duplicated in the plan because of multiple roles


    select a.list_no, a.customer_no, b.plan_no, c.primary_ind, c.role_no,
    d.customer_no AS worker_no, d.fname, d.lname
    from T_LIST_CONTENTS a
    left outer join T_PLAN b
    on a.customer_no=b.customer_no
    left outer join TX_CUST_PLAN c
    on b.plan_no=c.plan_no
    join T_CUSTOMER d
    on c.customer_no = d.customer_no
    where a.list_no='28411'
    and b.campaign_no = '299' --Moves Management
    Order by primary_ind Desc, worker_no

Children
No Data