I am trying to separate queries builds into different columns. For example below:
( country = 'US' AND state IN( 'AZ', 'KY', 'WA') ) AND ( industry IN('Construction') )
Would like to have a Country Column, State Column, and Industry Column (there are others as well). The issue I'm having is that there are so many variations that I cannot just do a simple parse. Because state isn't always selected. So for example, I was trying to do a substring for State but when I do the following I get the error: "Type mismatch, String provided where a number is required"
if Contains([query],"state") then Substring([query],"state IN(", FindString([query],")")) else "" endif
My thought behind it was search for "state IN(" then end at the next ")". It seems that I have to put a number either in the start or length portion of the formula, I can't put string values in both. Any thoughts on how I could work around this? Since state could vary in length depending on how many states are selected I can't just choose a length.
