[SOLVED] No Data after replace the Custom Fields

[ichigo]

i had this very very big mistake, the boss ask me to change and add some custom fields to the form of vicidial and done it smoothly.

then i go to Copy Custom Fields and replace the Custom Field with a the new one

this is my problem all the data in it are all gone, even the called one.

can i retrieve this?

Thanks in Advance
----------------------------------------------------------------
A BIG Thanks to blackbird2306

and also to dspaan

[ichigo]

is there a way to retrieve the leads are been called?

[blackbird2306]

No chance. The custom fields were deleted (dropped) with REPLACE copy option. Do you have any backups?

[ichigo]

that is the worst thing i don't have but the untouched data

[blackbird2306]

I'm really sorry for you. You should have at least the call history with actual status, comments or recordings to reconstruct some details about the leads.

[ichigo]

yeah the data has still the recordings and status in it even the phone numbers

[ichigo]

i have the status and the phone numbers, i downloaded the list and updated the blank column in the sheet

how can i upload this back to fill up the blank data of the customized fields?

[blackbird2306]

How many leads? Are we talking only about custom fields you need to reimport? There are various ways depending on your answer.

[ichigo]

How many leads? Are we talking only about custom fields you need to reimport? There are various ways depending on your answer.

more or less 12k leads, how can re-import all the data?

[dspaan]

Don't you have daily backups enabled? You mention 12K leads but are all of them conversations were data was entered in the fields during a conversation?

[ichigo]

yes after that i've got that problem by replacing the customized fields and i got blank all of the data that they are all calling,

not sure if there is enabled backup, if there is where will if find that backup?

[dspaan]

You enable backup by editing the following line through crontab -e:
### nightly backup
0 3 * * * /usr/share/astguiclient/ADMIN_backup.pl

And you can find the backups in this folder:
\var\log\astguiclient\archive\temp

There is also an option to send the backups to an FTP server.

If you didn not enable backups and you used the custom fields replace option you are screwed. Personally i think that option should come with a warning or special permission, it should not be possible for inexperienced admins to use this feature. We have experienced the same problem before....

If you don't have backups all i can recommend is listen to the recordings for the important calls and retrieve the data that you need for the custom fields from there.

[ichigo]

yeah that's true am screwed.

is there a way that i can reimport all the data i have, the recordings are all there, the phone numbers, lead id even the statuses, but all the customized fields are all gone.

[dspaan]

Which data do you want to import? I don't understand.

[thephaseusa]

If you had a custom field called shoe_size and you called all 12,000 leads and got their shoe_size and filled it in that custom field, and then you changed the list and changed the custom fields, the 12,000 shoe size entries are gone. Like they are telling you in this thread, you could listen to all 12,000 recordings, and manually put in the corresponding shoe_size entry. Or you can just call them again, and get that shoe_size custom field value again.

You can still call them right? Phone number address city state zip etc etc are still there. It’s just the custom fields you deleted by mistake. And yes if you had been making a daily backup of your asterisk database you would be able to restore that list. If you weren’t then you can’t.

[ichigo]

the good thing with that is the statuses, the phone numbers, lead id, agent user are all in there.

we can't run it to call them again, but what is the command on how i can update the list with my original data.

[dspaan]

Everything that was not in the custom fields should still be there?

Also you're using quite an old vicibox version. Which SVN are you using? The more recent versions work better with linked custom fields so you can actually import your data in most of the standard fields and display and edit them in the custom fields.

[ichigo]

yeah right all that was not on the custom fields are all there, the phone code, phone number, statuses.

this is a new single server
ViciBox v.8.1.1 180928 | VERSION: 2.14-692a | BUILD: 180927-0018 | Asterisk 13.21.1

-- sorry did not update my signature

what command should i use?

[dspaan]

I don't understand what you want to do?

[ichigo]

i have this list id 2018 with custom fields, with 12k leads been called, and i have this list id 2018 csv file with the same info with my list id 2018 with custom fields except with the statuses

with the list id 2018 csv file can i update the list id 2018 with custom fields?

[dspaan]

You can't do that via the vicidial interface, you need to do a MySQL query and find someone who's good with MySQL who can do it for you. We normally use Navicat to do stuff in the database but still you need to know what you're doing. It's not really complex but has to be done with caution.

[ichigo]

i think ill update this manually

thanks dspaan

[blackbird2306]

Ok I'll help you with this problem. You can do this with non-agent api (update_lead function) or manually with "/vicidial/admin_modify_lead.php?lead_id=xxx" one by one lead. But now let's start with my sql query solution:
1. First backup your system before you start (be careful and go exactly my described way)
2. Add a new list with exactly the same settings as your old broken list (name e.g.: 2019)
3. Copy or create all custom fields to 2019 exactly the same as your old broken list (name: 2018) [very important they must be identical--> RANK,LABEL,NAME,TYPE]
3.1 Recreate the old custom fields in your broken list 2018 if not done already
4. Add one new extra custom field to 2019 with name "old_id"
5. Download the old list "2018" with all fields and fill in with your missing custom fields
6. VERY IMPORTANT PART: create a new column "old_id" in your downloaded and edited file. Copy "lead_id" (this is the original lead_id of your broken list) column data to "old_id" column in your file.
7. Import the edited file into your new list "2019" with Load New Leads (setting: No Duplicate Check!!!)
8. Now you should have a fresh vicidial list "2019" with all the missing custom fields, but NEW status and new lead_ids. Verify that the "old_id" custom field contains the original lead_id of your broken list 2018. This will later be used as identifier
9. BE VERY CAREFUL WITH NEXT STEPS:

For example (change everywhere "2018" with your real broken list_id):
2018 (list_id of your old broken list)
2019 (list_id of your new added list)

Type from linux cli:

Code: Select all

mysql -D asterisk -e 'DROP TABLE `custom_2018`;'

Code: Select all

mysql -D asterisk -e 'CREATE TABLE `custom_2018` LIKE `custom_2019`;'

Code: Select all

mysql -D asterisk -e 'INSERT `custom_2018` SELECT * FROM `custom_2019`;'

Code: Select all

mysql -D asterisk -e 'UPDATE `custom_2018` SET `lead_id` = `old_id`;'

After this procedure your custom fields will be recovered.

[ichigo]

blackbird2306, very thankful.

i downloaded the list from the list id 2018 should i delete some column like vendor id, rank, owner and some original fields? or leave it as is ?

how about the recordings of the list id 2018?

[ichigo]

blackbird2306 Thank you very much!!!!

It's working, all the data is now recovered....
its a very very big Help
SOLVED thanks again
Also thanks to dspaan

[dspaan]

Nice job Blackbird!