Wrong membership ID for new members

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….)

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

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.

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

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?

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.

Yes. Change data via phpmyadmin seems easier to me

So go ahead with that approach

Tuan

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?

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

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