Merging lists

[dspaan]

I have a campaign made up of 4 lists. I have decided that i want to merge the lists so that i have 2 lists remaining. What would be the best way to do this?

If i simply export the calls and import them then i would lose the dialstatus of those numbers.

Something related i wanted to ask:

Suppose i delete a list, will this influence the outbound calling report history?

[williamconley]

Code: Select all

update vicidial_list set list_id='xxx' where list_id='yyy'

beware: combining is easy. splitting ... not so much.

reports are drawn from "logs" not from "the list", depending on the report you are pulling. any time you have a question like that, it is best to TEST. create a fake campaign fake lists and perform a single sample transaction. check your report. make your change. see what happens to the report. remember that "logic" only goes so far, and any answer you get on a topic like that COULD always be wrong. empirical evidence is sometimes more reliable, as misunderstandings often occur and generalizations can kick your butt.

[dspaan]

I tried this:

mysql> use asterisk
Database changed
mysql> update vicidial_list set list_id='996' where list_id='995'

But i don't see anything happening. Lead count for both lists stays the same.
Btw i want the leads from list 996 added to 995, did i do it right?

[DarknessBBB]

Code: Select all

update vicidial_list set list_id='xxx' where list_id='yyy'

Please double, triple and quadruple check before using sql queries. My "update vicidial_list set first_name = "BOB", last_name = "SMITH";" query kept me at home last weekend to take everything from backups

[dspaan]

The listnumbers are testlists. Can this query still mess up my operational campaigns and lists? What is wrong about the query in this case?

[williamconley]

1) Always do a backup before running queries. Too easy! Why skip that step? (mysqldump asterisk > DATE_TIME.asterisk.sql) you may need -p if your root mysql user has a password.

2) where list_id='995' is essential. The DarknessBBB error was that he inadvertently left off the "where" clause. This means it will perform the function on ALL records. Easy mistake to make, simply forgetting that where clause for a second ... and *poof* your entire db is borked. Did I mention that backing up your data was a good idea?

[dspaan]

ok i will do a backup first. But what was wrong about my query. I did mysql -p.

[williamconley]

queries must end with ";"

[dspaan]

Alright, after making the backup i tested the merging by using two test lists. The problem i noticed that after the lists were merged the custom field data of list 991 is gone.

mysql> UPDATE vicidial_list SET list_id = '990' WHERE list_id = '991';
Query OK, 10 rows affected (0.01 sec)
Rows matched: 10 Changed: 10 Warnings: 0

When browsing in phpmyadmin i notice there are tables called:

custom_991 and custom_990

What should be the query to merge the data in those two tables?
Sorry for the noob mysql questions but my knowledge of this is very limited.

Don't want to mess up my database.

[williamconley]

are these custom field tables identical?

[dspaan]

yes they have the same fields only when i download the lists the columns are in a different order

[williamconley]

if you create an insert query, you can copy the data from the "deleted" list's table into the "still used" list's table by using field NAMES, which will file them correctly upon landing.

look up mysql insert query with select statement.

and limit your first practice run to a SINGLE record for practice (ie: where list_id ='XXXX', something that will resolve to a single record you can just delete afterwards!)

[halami]

did anyone come up with a solid solution for this? i am trying to merge list as well...have 10 list would like to merge them down to around 3-4

[dspaan]

I believe a query is still the only option. Beware that custom fields should be the same.

[williamconley]

did anyone come up with a solid solution for this? i am trying to merge list as well...have 10 list would like to merge them down to around 3-4

1) Welcome to the Party!

2) As you are obviously new here, I have some suggestions to help us all help you:

When you post, please post your entire configuration including (but not limited to) your installation method (7.X.X?) and vicidial version with build (VERSION: 2.X-XXXx ... BUILD: #####-####).

This IS a requirement for posting along with reading the stickies (at the top of each forum) and the manager's manual (available on EFLO.net, both free and paid versions)

You should also post: Asterisk version, telephony hardware (model number is helpful here), cluster information if you have one, and whether any other software is installed in the box. If your installation method is "manual/from scratch" you must post your operating system with version (and the .iso version from which you installed your original operating system) plus a link to the installation instructions you used. If your installation is "Hosted" list the site name of the host.

If this is a "Cloud" or "Virtual" server, please note the technology involved along with the version of that techology (ie: VMware Server Version 2.0.2). If it is not, merely stating the Motherboard model # and CPU would be helpful.

Similar to This:

Vicibox X.X from .iso | Vicidial X.X.X-XXX Build XXXXXX-XXXX | Asterisk X.X.X | Single Server | No Digium/Sangoma Hardware | No Extra Software After Installation | Intel DG35EC | Core2Quad Q6600

3) Since I don't know your Vicidial Version, I'll answer as if you have "latest". In the latest Vicidial version: Admin->Reports-> (bottom left corner) Admin Utilities.

Basic Lead Management Tools | Advanced Lead Management Tools | List Split

[mflorell]

We added a List Merge utility to the Admin Utilities section recently in svn/trunk

[dspaan]

Nice! Going to try that out for sure.