Wrong membership ID for new members

  • Nico Bouwman
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
More
3 weeks 3 days ago #132773 by Nico Bouwman
Wrong membership ID for new members was created by Nico Bouwman
I like to give new members a membership id which contain the year and a 4 digit number.
I have recently imported 8500 members, they got the right membership ID: Year of subscription + a 4 digit number from the export.
But we have some new members signing up and they get a strange membership ID like: 202020194409
It looks like they get 2 times YEAR and a 4 digits number.
This has te be: 20204409

What is going wrong?

See screenshots.
Using Membership Pro 2.19.0 (yes I have to update….)
Attachments:

Please Log in or Create an account to join the conversation.

More
3 weeks 3 days ago #132774 by Tuan Pham Ngoc
Replied by Tuan Pham Ngoc on topic Wrong membership ID for new members
Hi Nico

I'm afraid of it causes by wrong data from the import. When you import, you need to remove the year part from the membership_id

So the data before importing, instead of 20204409, it must be 4409 only

To correct this issue, I am afraid of you will have to export subscriptions, then correct the Membership ID and import it back to the system

Tuan

Please Log in or Create an account to join the conversation.

  • Nico Bouwman
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
More
3 weeks 3 days ago #132775 by Nico Bouwman
Replied by Nico Bouwman on topic Wrong membership ID for new members
Hello Tuan,
No the problem is not the import. I do have reomve the year part of the membership_id. The problem now is that when a new subscriber gets a membership ID it has to get 2020xxxx but it gets: 20202019xxxx
And I really do not know why.
So only when someone new is subscribing.

Please Log in or Create an account to join the conversation.

More
3 weeks 3 days ago #132776 by Tuan Pham Ngoc
Replied by Tuan Pham Ngoc on topic Wrong membership ID for new members
Hi Nico

It's because in the database, there is already members with that large membership id number

So when the next person registers, the system will get that largest number + 1 to use as Membership ID for that new member

So you will have to correct data in the database (export, and correct, then import as I said)

Tuan

Please Log in or Create an account to join the conversation.

  • Nico Bouwman
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
More
3 weeks 3 days ago #132779 by Nico Bouwman
Replied by Nico Bouwman on topic Wrong membership ID for new members
OK I understand. So I have exported all the members, changed 1 member id (202020194400 - the first one when it went wrong) and changed it to 4484 and import the whole list. Change is OK when I checked Membership Pro.
But, another problem. The numbers of the invoices is changed. It was 2020xxxx now it is 20202020xxxx. So the YEAR is placed before the invoice number. How can I undo this? (I have a database backup)
And how to avoid this when I an changing the other membership-ids?

Please Log in or Create an account to join the conversation.

  • Nico Bouwman
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
More
3 weeks 3 days ago #132780 by Nico Bouwman
Replied by Nico Bouwman on topic Wrong membership ID for new members
To change the membership_id I think it is better to change it direct in the database (phpMyAdmin) so no other items like invoicenumber will be damaged. And it is about 10 members. Is this a good choice?
I still have to repair the invoice-numbers.

Please Log in or Create an account to join the conversation.

More
3 weeks 3 days ago #132781 by Tuan Pham Ngoc
Replied by Tuan Pham Ngoc on topic Wrong membership ID for new members
Yes. Change data via phpmyadmin seems easier to me

So go ahead with that approach

Tuan

Please Log in or Create an account to join the conversation.

  • Nico Bouwman
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
More
3 weeks 3 days ago #132782 by Nico Bouwman
Replied by Nico Bouwman on topic Wrong membership ID for new members
Hello Tuan,
How to repair the invoice numers? They all get an extra YEAR before the number.
So 20200720 gets 202020200720 (in Membership Pro backend)
In database it is 20200720 and I think before my export-import action it was 0720.
Any solution for this?

Please Log in or Create an account to join the conversation.

  • Nico Bouwman
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
More
3 weeks 2 days ago #132807 by Nico Bouwman
Replied by Nico Bouwman on topic Wrong membership ID for new members
Hello Tuan,

I have corrected the membership-id but the export-import action in Membership Pro messed up my invoice numbers...

Could you help me to correct them?
I think the best way to do is a SQL-query in the database:
change all records by removing the first 4 digits if this number is not 0.
(0 = no invoice number)
If invoice number > 20200000 then subtract 2020 - but leave the leading zeros.
So 20200207 should become: 0207
SQL query on: md7e9_osmembership_subscribers column: invoice_number – int(11)
Can you help me with an SQL query?

Screenshot: invoice numbers, just a small part with 2019 (I can correct them manually) and a big part starting with 2020xxxx
Attachments:

Please Log in or Create an account to join the conversation.

More
3 weeks 2 days ago #132808 by Tuan Pham Ngoc
Replied by Tuan Pham Ngoc on topic Wrong membership ID for new members
Hi Nico

From what I see, you will need to run two questions (make a backup of the table before running the query)

UPDATE #__osmenbership_subscribers SET invoice_number = invoice_number - 2020000 WHERE invoice_number > 2020000;

UPDATE #__osmenbership_subscribers SET invoice_number = invoice_number - 20190000 WHERE invoice_number > 20190000;

(The first query needs to be run first, then the second one). Also, replace #__osmenbership_subscribers with correct table on your site database)

Tuan

Please Log in or Create an account to join the conversation.