• Replies 6 replies
  • Subscribers 415 subscribers
  • Views 134 views
  • Users 0 members are here
Related

Remove End date/change to 00/00/0000 on a Constituency in bulk

Alison Lambert
$organization

Hi all,

I'm having a mental block about how I did this a year ago and can't get it to work!

I need to keep a constituency active on accounts, but the end dates are now all in the past. I've tried all the options in the Manage Constituency Utility, but I can't get the end dates to change to 00/00/0000 (and therefore remain an active constituency). Removing and adding again, still results in an end date of today, even when I leave the field as 00/00/0000. 

There's over 2000 const records, so I'd rather not go through them manually to change them all! 

I know I did this last year, as I can see blank end dates on the constituency I was using for that year. I know I didn't do any fancy SQL work for it either and I don't remember doing it manually one by one...

What am I missing please?

Thanks,

Alison

Parents
  • $organization

    Hello folks,

    As far as I know this isn't possible using the utility. When using it to add the constituency, you can apply a blank end date by leaving the Constituency End Date parameter as 00/00/0000 but the only way to clear the end date is via SQL. Your DBA should be able to do this for you. You can use the code below - you'd need to know the ID of the constituency and the number of the List. I have to admit I do this regularly but have never got round to creating my own utility.

    Martin

    update TX_CONST_CUST
    set end_dt = null
    where constituency = TR_CONSTITUENCY.id --replace this with the constituency ID
    and customer_no in (select customer_no from T_LIST_CONTENTS where list_no = T_LIST.list_no) --replace this with the list number

Reply
  • $organization

    Hello folks,

    As far as I know this isn't possible using the utility. When using it to add the constituency, you can apply a blank end date by leaving the Constituency End Date parameter as 00/00/0000 but the only way to clear the end date is via SQL. Your DBA should be able to do this for you. You can use the code below - you'd need to know the ID of the constituency and the number of the List. I have to admit I do this regularly but have never got round to creating my own utility.

    Martin

    update TX_CONST_CUST
    set end_dt = null
    where constituency = TR_CONSTITUENCY.id --replace this with the constituency ID
    and customer_no in (select customer_no from T_LIST_CONTENTS where list_no = T_LIST.list_no) --replace this with the list number

Children