Pulling multiple values from one field

Unfortunately in the database I'm working in there's one large table where a lot of data gets dumped because our developers were too lazy to create the tables and fields properly. One report that I've created pulls a number of items such as

Vehicle Model, Circumstance, Incident

Those 3 example fields above are in a table with this and a lot more data, so for example, to get a report of the above info I need to write a formula for each item

Formula Column 1
if Userfield.Description = 'Model' then Userfield.Userfieldvalue

Formula Column 2
if Userfield.Description = 'Circumstance' then Userfield.Userfieldvalue

etc...

Now the end user also wants to be able to select or type in specific Models, Circumstances, and Incidents. So either they type or I create a drop down which let's them select something like

Vehicle Model = Ford and Circumstance = Rollover

I can setup the parameters and get the records to display where EITHER the model is a Ford OR the circumstance is a rollover, but not both. This is giving me a hard time because Ford and Rollover come from the same exact field name. I'm looking to do this in the 'Select' statement, I haven't tried but figure this can easily be done by suppressing the groups that don't have these values, however then the report takes much longer since the query digs through the entire table. Any suggestions? Hopefully I've explained my scenario clearly. Thanks!

Sounds like the information is coming from a table Userfield and a field called Description, so you can set up a record selection formula like:

{Userfield.Description} = "Ford"
or {Userfield.Description} = "Rollover"

If you wish the user to do the selection criteria, then set up two parameters like Choice1 and Choice2 and replace the hardcoded criteria like

{Userfield.Description} = {?Choice1}
or {Userfield.Description} = {?Choice2}

Quote:

---

Originally Posted by rasinc

Sounds like the information is coming from a table Userfield and a field called Description, so you can set up a record selection formula like:

{Userfield.Description} = "Ford"
or {Userfield.Description} = "Rollover"

If you wish the user to do the selection criteria, then set up two parameters like Choice1 and Choice2 and replace the hardcoded criteria like

{Userfield.Description} = {?Choice1}
or {Userfield.Description} = {?Choice2}

---

Right but the problem there is that what if I want to get ONLY Fords that had a Rollover. Using the statement above will also return anything that was Ford and Fire or Rollover and Dodge, because both of those results are true for the above statement.

I guess that's one of the problems with non-normalized databases. The best solution would be to redesign properly, then create the reports. You might try some combination of the LIKE function with INSTR using something like

Code:

---

if {?AndOr} = "AND" then
InStr ({Userfield.Description}, "Ford")
and InStr ({Userfield.Description}, "Rollover")

else

InStr ({Userfield.Description}, "Ford")
or InStr ({Userfield.Description}, "Rollover")

---

Of course you can use parameters for the Ford and Rollover and set up another prompt to allow the user to determine the choice.

Hope this helps

Quote:

---

Originally Posted by rasinc

I guess that's one of the problems with non-normalized databases. The best solution would be to redesign properly, then create the reports. You might try some combination of the LIKE function with INSTR using something like

Code:

---

if {?AndOr} = "AND" then
InStr ({Userfield.Description}, "Ford")
and InStr ({Userfield.Description}, "Rollover")

else

InStr ({Userfield.Description}, "Ford")
or InStr ({Userfield.Description}, "Rollover")

---

Of course you can use parameters for the Ford and Rollover and set up another prompt to allow the user to determine the choice.
Hope this helps

---

Ok I'm going for the second option of creating a parameter with all of the drop downs. Works perfectly if you pick one of each, now what happens if you want let's say just Ford, but ALL of the other options so Rollover, Fire, Collision, etc...?

You can try a blank option as a default and see if that helps.