Combined List Box?

[peterderrington]

Hi,

Hoping someone can point me in the right direction....

I have an excel Spread sheet that shows information about transactions; each transaction can have one (or two) of 3 possible supervisors. I can currently show which of the supervisors relate to which transaction but what I was hoping to be able to do was create one list box that will allow me to pick just the transactions that that supervisor relates too.

Example of data:

ID Object    Supervisor 1     Supervisor 2      Supervisor 3

1  Frog        Bill                    Fred                   None

2  Ball         None                Bill                      John

3  Bat         John                  None                  Steve

I'd like a list box that would just show:

Supervisor

Bill

Fred

John

Steve

Any ideas?

Thanks,

Peter

[sunny_talwar]

Use The Crosstable Load to transform your data

[sunny_talwar]

Like this

Table:

CrossTable (Level, Supervisor, 2)

LOAD * INLINE [

    ID, Object, Supervisor 1, Supervisor 2, Supervisor 3

    1, Frog, Bill, Fred, None

    2, Ball, None, Bill, John

    3, Bat, John, None, Steve

];

[Peter_Cammaert]

Sunny can probably suggest a merged-field listbox trick, but I know of a simple script extension like:

:

SupervisorN:

LOAD Supervisor1 AS SupervisorN,

     ID

RESIDENT ExampleOfDataTable;

CONCATENATE(ExampleOfDataTable)

LOAD Supervisor2 AS SupervisorN,

     ID

RESIDENT ExampleOfDataTable;

CONCATENATE(ExampleOfDataTable)

LOAD Supervisor3 AS SupervisorN,

     ID

RESIDENT ExampleOfDataTable;

:

Now you can create a Listbox from field SupervisorN that shows all possible transaction for which the selection is present in any combination of Supervisor1-3 fields.

[peterderrington]

Thanks Sunny,

I'll give this a try. Do I need to put it in the 'Edit Script' Section? What field would I select in the list box field selector?

[sunny_talwar]

Yes, this will go in the script. And for selection, you will be using Supervisor field.

[peterderrington]

Excellent, ok.

I don't need to go through each line though do I? Theres over 500 IDs and only a combination of about 10 Supervisors.

[sunny_talwar]

I don't think so...

[rwunderlich]

Don't put the inline data as Sunny showed in the script as a demonstration.  You CrossTable load will read from the same source where you get your data.

-Rob

[peterderrington]

Sorry,

I've been away at a conference the past couple of days and this is the first chance I've had to look at this.

I have added the below element to the script and now when I add the List Box there is a new Field called 'Supervisor' which when I select, then doesn't show anything in the list box.

Two of the fields mentioned in the script now show little key symbols in them which I think means I'm a big step closer to doing what I want but obviously its not quite working.

Any ideas?

CrossTable (Level, Supervisor,2)
LOAD *INLINE [Supervisor 8-5, Supervisor 4:30-8, Supervisor 8-8];