Search Multiple Columns in Table - Best/Fastest Method?

[Mike Mackinnon]

I am looking at doing a search through a custom table that has multiple fields for an item number.
Example table fields: Item1, Item2, Item3, Item4, Item5, Item6, Quantity, Location, etc.

I want to return ALL records that have an item number in any of the "item fields". Currently I have DD defined field (FC Field - LITM) that I am using for user entry of the item number to be found. I then use events rules in "Grid Record is fetched" to check the value of the FC Item field to compare to all field BC values and hide/display as necessary (on IF with multiple OR clauses). It turns out that this takes a fair bit of time to return the grid results and was wondering if there is a better way to search multiple fields for data like this??

I found that for the application the fastest method is the manual 'user searching' each QBE field individually works best.
However, I would maybe like something more 'elegant' as it might be a bit of a pain for user to search each column for his/her item data.

Thanks!

 

[nkuebelbeck]

the sql equivalent of SELECT * FROM table WHERE useriput IN(item1, item2, item3, item4); ?

I think you'd need to get into jde C land. Personally never done this but using ER does seem painful.

 

[BOster]

the sql equivalent of SELECT * FROM table WHERE useriput IN(item1, item2, item3, item4); ?

I read it as the sql equivalent of SELECT * FROM table WHERE fld1=1234 or fld2=1234 or fld3=1234...

Either way, this is easily done using the JDEBASE C APIs. Not sure if your tools release has it but there is also the C API XRCS_parseAppQueryWhere to extract the WHERE clause from an APPL query (much the same way we have always been able to extract the WHERE clause from a UBE section in C using ubeSection_GetDataSelection) and then you can append stuff like the item search criteria. What gets harder is then returning that result set to the APPL grid. Its possible but its not always pretty. There is an ER system function called "Set Selection Group" which, theoretically, would allow you programatically append your item search to the user's QBE/Filter critearia. Something like:

select *
from table
where (field1=UserFilterValue and field2=UserFilterValue) AND (ItemField1=1234 or ItemField2=1234 or ItemField3=1234)

I say "theoretically" because "Set Selection Group" is kind of half baked. I have used it with varying success. You might get it to work, just review the resulting SQL in your JAS log to make sure it is doing what you want.

What you want is the above, what you will probably get is the following... which will still work.

select *
from table
where (field1=UserFilterValue and field2=UserFilterValue) AND (ItemField1=1234 or (ItemField2=1234 or (ItemField3=1234)))

 

[DSauve]

I see that you're on E1 apps 9.0 with tools 9.1.4.4, so this won't help you now, but once you're on apps 9.2 with tools 9.2+ (exact subrelease I'm not sure of), you can use the EnterpriseOne Search to search multiple columns in a table or view and return all records matching any of those columns.

 

[Larry_Jones]

Mike,

consider a virtual table approach where you:
1. Create a DB View using SQL that normalizes your data from 1 row with 6 Item# fields to upto 6 rows with a a single item # field.
2. Describe the view / virtual table in JDE as a table and Business View.
3. Build a normal Search/Select form against the BSVW.

Alternatively fix the design and normalize the source data.

 

[Mike Mackinnon]

Thank you!!
I did not think there was anything that I could do (on current release) using standard JD tools/ER...but I though maybe someone ran across this before.
I thought about creating a SQL view and using that as the basis for a bus view but decided against it. I may go back to it is the user finds the current solution too cumbersome.

Thank you all for the replies!

* The SQL equiavalent would have been:
select * from PRODDTA.F550000 where FIELD1 = 'ITEM' or FIELD2 = 'ITEM' or FIELD3 = 'ITEM' or FIELD4 = 'ITEM' or FIELD 5 = 'ITEM' or FIELD6 = 'ITEM'

 

[Marcia HR]

It may not be much better than they are currently doing, but based on your SQL stated above, couldn't they use a query setup against the app that has all those fields listed with the condition "Match Any' selected. Then they would go into the query and change the value for each selection to the item number needed and run it to get all the records at once instead of one at a time. Just a thought.

 

[nkuebelbeck]

gotta ask. why do you have 6 fields for item number?

 

[Mike Mackinnon]

We have a setup for warehouse locations that store some smaller parts/items.
A location can be split into possibly 6 sub locations (small items like screws, nuts, etc).

So the record looks like this (main fields) - Location, Aisle, Bin, Location03, Location04, Item1, Item2, Item3, etc.:

H06F21 H 06 F 21 Q32527/14WV4SE R38917/36XT4PV R38609/26ZE8JN 020-1261-008 SUPPORT HFXJDE15 118311 120000
H06F23 H 06 F 23 860-6801-210 SUPPORT HFXJDE15 118311 120000

So for example I want to look in table to find if Part "R38917/36XT4PV" has a defined location and what it is (I want to search all records and all 'item' fields).
This lets the user know if there is a defined location already for the item being received and where to put away.