Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Use The Crosstable Load to transform your data
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
];
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.
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?
Yes, this will go in the script. And for selection, you will be using Supervisor field.
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.
I don't think so...
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
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];