I have been looking for this off and on for 3 years, can someone help? I've put a ticket in and they aren't able to help without charging and I just don't understand why this isn't a simple answer. I don't know know or have access to SQL and neither does anyone else at our organization.
What I need:
I need to be able to add address line 1 in list criteria so I can search based on the address containing a word, like a street. For example, I want to pull everyone with an address that contains "Tropical Av"
What I know/currently have tried:
I hope that comes through clear, but in summary I have added Address Street 1 to T_Keyword, Detail Tbl of vs_addrss and Detail Col of !.street1
I appreciate any help you can offer!
Jen
If you are in List Manager, you ought to be able to get your basic query in place using the out of the box criteria, save, and then, Show Query, and put something like this into the sql SELECT DISTINCT a.customer_noFROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK) INNER JOIN (SELECT a1.customer_no FROM VS_ADDRESS AS a1 WITH (NOLOCK) WHERE a1.street1 LIKE '%Tropical Av%' AND ISNULL(a1.inactive, 'N') IN ('N')) AS e
ON e.customer_no = a.customer_no
WHERE a.inactive = 1
I do not have access to SQL and would want to be able to search on all kinds of addresses.
OK, maybe you're locked out from this at your org, but just want to make sure we're talking about the same thing. Typically in List Manager, you can make a list with any old out of the box criteria, then save that list, then click Show Query, and edit from there. My example is Tropical Av, but just change those exact letters to whatever.
I leave a list similar to this in place for various things like address, eaddress, zip code, and then edit it to whatever the current thing I'm looking for is.
I have like 4 or 5 different "resuable" lists that I use over and over. Really cuts down on unnecessary list creation.
ok, i feel really dumb. I have NEVER used the query mode before. Probably because I've looked at it and have no idea how to change it. So once I do this mode I can't go back to add other criteria the other way? Thanks for your help with this. I just need to figure out how to add other criteria to this and this will work!!
Jen,
It might be easier if you add a List Manager element - we have one for just this purpose. In T_KEYWORD add a new item with the values:
That way, team members who're put off by adjusting code can use it.
Martin
Martin, thank you! This is what I was trying to do, but it wasn't working. I had all of this except the "category", so I guess that was it! Thank you thank you!
I had this too, but dm took another route. NOTE: w hen you train people on using it, be sure to train them to use % often. E.g. there are a lot of spelling variations for unknown like X, xx, xxxxx, not known, unsupplied, st instead of dr, etc in our data. I'm always adding some new variant to the extraction postal mail exclusions :)
absolutely! But at this time, I won't be training anyone as I'm the only one who currently pulls lists.
Got something for Gender? It's showing up on my list criteria, but giving me an error.
Thank you so much! This request came in today! I can't believe I stumbled on this looking for another conversation. I LOVE THE TESSITURA COMMUNITY!