Custom Fields SQL FILTER

[Nefariousparity]

How does one go about using SQL FILTERS on custom fields. For instance, we can get custom sql filters to work with pre-loaded fields. But if we create a custom field for say if someone has a pool in their backyard, and then try to filter on that. It does not work. The only information in the column for said field is Y. There is nothing other than that...and N will show up as null in the DB. Please advise if this is even possible, and if so how would I go about getting this to work.

[mflorell]

That is not currently a feature, the main reason is that it would slow things down tremendously in the hopper and manual dialing.

[Nefariousparity]

It is possible, because we are doing it. The feature is built into vicidial as there is options for it.

[mflorell]

I'm confused, first you say you want to know if it is possible, then you are saying you are already doing it?

[Nefariousparity]

I don't think you understand the question that I stated. So for me to use custom filters. I have to load data into pre built fields when I am loading a list. So for instance for my custom field to work like peple with pools the two field options are Y or N. I have to load that into a prebuilt field that we are not using like address 3. Get it? If I were to create a custom field to show next to address 3 like pools. When I do my custom SQL Filter it does not seem to work on those.

[williamconley]

using custom fields can be as easy as this (as an addition to an existing sql filter, we'll assume you already have something like "state NOT IN ('AL','FL')" and you'll be adding a custom field requirement.

To add "in_ground_pool" must be "Y":

Code: Select all

state NOT IN ('AL','FL') and lead_id IN (select lead_id from custom_1111 where in_ground_pool = 'Y')

Downside: List ID is hard coded. To get multiple lists included, you'll need to perform a "join" inside the "select" or use multiple nested "or" statements, but be careful to nest them properly.

If you require a "null" value record to be returned instead of ignored (ie: omit those with "N" but accept "Y" and NULL):

Code: Select all

state NOT IN ('AL','FL') and lead_id IN (select lead_id from custom_1111 where in_ground_pool = 'N' or in_ground_pool IS NULL)

Because of the way custom field tables are created, for maximum custom field flexibility, this is cumbersome but at least moderately possible.