COMPLETE - dynamic field creation within ViciDial

[Trying]

I told you he had something good!

[williamconley]

LOL. Only a Single person could claim not to know who "The Boss" is.

[Trying]

Ahhh, now that makes sense!

[gerald_lyc]

Hi i have experience in vocalcom tech. I did a really nice architecture.

Why not making vicidial creating a database named from the campaign naming, then in the database it will create 2 tables name lead list and customer table. When we upload the leads for this particular campaign all the leads will be inserted in the lead list table (where vicidial would dial from this list for that particular campaign) as well as in the customer table making the list id being the primary key. Then for each field that we want to create for this campaign will be inserted in the customer table for this particular campaign. With that we can query the table for each specify campaign to manipulate our output using adodc connection to each database. These are my suggestions..

I had thought about that, but I wanted to allow for multiple sets of fields per campaign potentially because then you could have a different set of fields for each in-group for example, because new calls are tied to a list_id, not a campaign and you can have many in-groups handled by a single campaign. So doing it based on List ID allows for more flexibility that you wouldn't get doing it by campaign. Also since this includes templating, it will not be difficult to import new leads with the same custom fields as you have already done for other lists in a campaign.

[williamconley]

and then when we search for a lead/phone/name ... we search ALL campaign tables with "join" commands? uh-oh ...

[mflorell]

I decided a while ago that this was going to be list-based, not campaign-based. There are just too many reasons in favor of doing it list based and not enough reasons against it.

[williamconley]

i still like the "goldmine" and "sugarcrm" method of creating a 2nd table for "custom" fields.

in addition, for mimimizing the "hit" on the database size, the creation of a "per campaign" and/or "per table" custom field header for the changeable names of fields in the 2nd table would be viable

thus allowing the custom field headers to be visible on-screen in Agent view, and recycling these custom fields across all lists/campaigns. probably only need to add "new fields" to the table if you use 'em all up.

have an auto-"list 'em in order" routine for the agent screen (up to the user to put in a "list order" entry to allow them to look proper on the agent screen, otherwise they just show up in the order they appear in the table, and drop to the next line if they run out of room).

and all this means the original table is untouched (and the logical interaction of the existing system need not be touched).

[gerald_lyc]

We can search in a specific table if we know the campaign or else in the global database (where all the call records are kept containing the campaign name information also) the global database is where reports are made etc.

"goldmine" and "sugarcrm" are not free.

Can someone provide the database architecture of vicidial?

[williamconley]

Can someone provide the database architecture of vicidial?

the database architecture of vicidial is available via phpMyAdmin after loading the software. easy and free.

the database engine is myisam, therefore the relations are not "enforced" by the database engine, but rather by the code.

i do not believe there is a published "map" of the database beyond this.

[williamconley]

"goldmine" and "sugarcrm" are not free.

SugarCRM is free.

i was not suggesting using the software in question, just mentioning two implementations of the method i was suggesting that we use. considering the size of the research teams in question for those two packages, i'd say reinventing that wheel would be ... well, not cost-effective.

re-implementing that wheel (to fit the vicidial architecture), would certainly be viable, however.

[williamconley]

We can search in a specific table if we know the campaign or else in the global database (where all the call records are kept containing the campaign name information also) the global database is where reports are made etc.

This sounds like you want to keep the Data in both the global database and in a sep table also, but doesn't explain how the field names are handled when the data resides in the global tables (and how the restructuring of the existing vicidial_list table would be implemented to handle LOTS of fields, for that matter).

The reason behind creating a 2nd table system would be that there would ultimately be no reason create a record in it if there were no custom fields in a particular import. Which means that loading data with NO extra fields would be no different than it is now, and take up no extra room. The only time a record would be created in the new table would be if there WERE extra "custom" fields required for this list.

For those who ALWAYS have lots of extra data, this won't represent a savings OR a loss. But for those who never have extra data (or rarely) it would represent a significant savings in drive space and database performance while allowing others using the same system to reap the benefits. And everyone still works from the same codebase.

[mflorell]

We do create a second table for custom fields, but it is by list ID not by campaign, the main reason for this is that lists can be easily moved from campaign to campaign, and when loading leads you load them by list not by campaign, so not doing it this way would make lead maintenance problematic, especially when moving a list from campaign to campaign.

For example, for list ID 101, the custom table is called "custom_101" in MySQL, this dynamic table creation and modification is already in place in the admin code that is in SVN right now. The definitions that build the table and that contain the human readable name and description of each field is stored in a separate table, but that data is not needed when querying for the data. The custom data table will be empty if there is no custom data populated for the each lead. The primary key in the custom table is the lead_id from the vicidial_list.

As for SugarCRM, yes the Open Source version is free, but they actually removed some functionality from this OS version to put in their Professional version which you do have to pay for. This was one of the reasons for the creation of the Vtiger fork of SugarCRM by people outside of the SugarCRM company. It should also be noted that Vtiger is faster than SugarCRM on the same hardware, and Vtiger has more features.

I must also mention that the Sugar/Vtiger method of storing custom field data is quite inefficient when you start talking about large datasets(over 100,000 accounts), the design of the ViciDial custom fields are much more efficiently designed with large datasets in mind.

[williamconley]

i agree their functional implementation is inefficient, but that is primarily their coding method of "one-off" with relation tables and is primarily necessary for the ease of interaction and customization. so for their purpose, of course, it is efficient. but for massive data storage and manipulation, such as is a regular occurrence in Vicidial, another method is absolutely required.

so instead of having a "custom field" table with modifiable headers per "list", we have implemented a "custom TABLE" for each table requiring custom data?

What if two tables have the same data structure, and would like to "share" searchable data?

the method I suggested would allow for this, and would not require an actual alteration of the data set for each custom table (unless you added more fields which went beyond the limit of what is already available in the custom set). of course, adding new fields would require expansion of ALL records in the "custom" table (inefficient), so it's a bit of a trade-off. But i've implemented it before and it seemed to work very well with extremely large data sets (since i took goldmine as an example before making the decision on my own).

is this in 2.4 already so i should just look?

[mflorell]

Well, one of the parameters early on for this first version of custom fields is that the custom fields are not searchable. Not that it would be that difficult to add in the future.

Yes, there is a separate custom table for each list_id that has custom fields even if the format is shared with another list. Given all of the ways that different companies use lists and campaigns this may not be the most efficient solution for everyone, but for many of the clients of ours that have large datasets this method does make the most sense.

Yes, this is in 2.4 (SVN/trunk) right now, you just have to enable custom fields in the system_settings(manually through the DB not the GUI). Hopefully I will be able to start working on the agent side of things in a few weeks.

[mflorell]

I have completed phases 2 and 3 and committed to SVN trunk. You can now test the feature and let me know if anything is broken or needs to be changed. More details as well as screenshots here:
http://www.vicidial.org/VICIDIALforum/v ... hp?p=49364

[Trying]

EXCELLENT!! This is one of the top features to date!! Well done Matt, AWESOME work!

[mflorell]

Just added the ability to use custom list fields in web forms, scripts and dispo call urls.

[Trying]

Very nice!

[slider]

I plan on using custom fields soon and I originally committed a bounty. Feel free to collect by sending me an IM.

[arvindsandilya24]

hi.. please find a video demo for the earlier mentioned UI for dialer, please let us have feedback
part1 http://www.youtube.com/watch?v=0fZpuEDmYU8
part2 http://www.youtube.com/watch?v=aS7SSc86FB0
part3 http://www.youtube.com/watch?v=o12xcAQ8UEo

thankyou

[mflorell]

Doesn't look like it will be forward compatible with ViciDial 2.4

[arvindsandilya24]

hi matt, we are porting to goautodial2.0 now, will be completed in coming days.. 2.4 when it is out!!

[ruben23]

@ arvindsandilya24

deadlink:

part1 http://www.youtube.com/watch?v=0fZpuEDmYU8
part2 http://www.youtube.com/watch?v=aS7SSc86FB0
part3 http://www.youtube.com/watch?v=o12xcAQ8UEo

[Trying]

Videos are unavailable.

[williamconley]

Well, they are 3.5 years old ... and the previous poster mentioned they were unavailable ... so ...