AnsweredAssumed Answered

Search Script for 2 Fields and/or

Question asked by bburdeen on Oct 10, 2017
Latest reply on Oct 11, 2017 by philmodjunk

I have a DB with a table that has about 80 fields. The 2 fields I'm working with are "State" and "County."

 

The "State" field contains the 2 character abbreviation for states. The "County" field contains county names such as "Jackson" and "City of Chicago - South."

 

I'm trying to create a script that will search for specific counties in 1 or more states, but not necessarily all states. Example: Find states MO and IL and then find the counties (predetermined as part of the script) in one of those state. So I have a few questions and problems...

 

I started out like this:

Enter Find Mode

Set Field [ Table::State ; "MO"]

Set Field [ Table::County ; "Andrew"]

Duplicate Record/Request

Set Field [ Table::County ; "Cass"]

Duplicate Record/Request

Set Field [ Table::County ; "Jackson"]

etc.

 

 

This is working even though the list of counties that I'm looking at currently happens to be 56. Is there a better way to do this? For this I'm basically searching for MO and containing Andrew, or Cass, or Jackson, etc.

 

The problem I'm having is that I want to search for "MO" AND "IL" AND the counties I have for each specific state. Jackson happens to be in MO and IL, but I only want to search for Jackson in MO, not IL. There are other county names that are in multiple states, so ideally I would want to contain the listed counties I have to the specific state. I need control over this because we may not want to see the county of Jackson tied to IL, only tied to MO. Make sense?

 

In the end I'm looking at 6 different sets of filters for 6 different territories.

 

I've thought about having a calculation field that would combine the State and County, wondering if this would give me more leverage for searches, but at this point I'm not sure what is best or how to script this type of search.

 

Any help would be greatly appreciated!

Outcomes