List Box - Filter Data from middle section

[#1]

Hi,

I currently have a straight chart which holds all the data from a database.  In the straight chart is a column called 'fixcode'.  I want to be able to look at the third part of the fixcode to determine the software I am using.  The data in the fixcode shows as such:

FLT0 - CRPR - RCL0 - SSWO

RQST - RSFT - RRCL - THR1

RQST - RSFT - RRCL - NWSP

The third piece of data eg: RCL0 and RRCL, represent the software which is used to log calls.  So RCL0 = Oracle, RRCL = Cyborg, RTNT = Office 2010 etc.

I want to create a list box which has a value of Orace, Cyborg, Microsoft and if I click say Oracle, is pull up everything that has RCL0 in the third section, and pick Cyborg anything with RRCL comes up etc.

Is this Possible?

P.S. They are separated by a dash -

[RedSky001]

Something like this, where "blah" is your field name:

load *,

          if((Blah2) = 'RCL0','Oracle',

          if((Blah2) = 'RRCL','Cyborg',

          if((Blah2) = 'RTNT','Office 2010')))           as company

;

LOAD Blah

,KeepChar(SubField(Blah,'-',3), 'ABCDEFGHIJKLMNOPQRSTUVXYZ1234567890><') AS Blah2

FROM

[Example.xls]

(biff, embedded labels, table is Sheet1$);

Mark

[#3]

Hi Mark,

Is there a way to do this in an expression as I already have all the data in a listbox called software.

[RedSky001 · Accepted solution]

Yes,

Create a list box expression:

if((KeepChar(SubField(Blah,'-',3), 'ABCDEFGHIJKLMNOPQRSTUVXYZ1234567890><')) = 'RCL0','Oracle',

          if((KeepChar(SubField(Blah,'-',3), 'ABCDEFGHIJKLMNOPQRSTUVXYZ1234567890><')) = 'RRCL','Cyborg',

          if((KeepChar(SubField(Blah,'-',3), 'ABCDEFGHIJKLMNOPQRSTUVXYZ1234567890><')) = 'RTNT','Office 2010')))     

Mark

[#5]

Thanks for the help Mark, it is exactly how I wanted it to show.