Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
How do you select/filter using listbox from a concatenated field ? For example from the table below I want to see how many meetings have Employee A and B attended ?
Employee | Meeting Type | Meeting Hours(Minutes) |
A,B,C | Sales Pitch - XYZ Client | 60 |
A,B,H | Sales Pitch - XXX Client | 60 |
X,B,Z | Sales Pitch - YYY Client | 30 |
H,I,J | Sales Pitch - ZZZ Client | 30 |
D,E,F | Sales Pitch - AAA Client | 60 |
A,B,D | Sales Pitch - BBB Client | 30 |
M,N,O | Sales Pitch - CCC Client | 30 |
May be create a Link Table with a field which contain the distinct values of Employee in a column and you make selection in there.
Example Script:
Table:
LOAD * Inline [
Employee| Meeting Type| Meeting Hours(Minutes)
A,B,C| Sales Pitch - XYZ Client| 60
A,B,H| Sales Pitch - XXX Client| 60
X,B,Z| Sales Pitch - YYY Client| 30
H,I,J| Sales Pitch - ZZZ Client| 30
D,E,F| Sales Pitch - AAA Client| 60
A,B,D| Sales Pitch - BBB Client| 30
M,N,O| Sales Pitch - CCC Client| 30
] (delimiter is '|');
LinkTable:
LOAD Employee,
SubField(Employee, ',') as EmpSelection
Resident Table;
May be create a Link Table with a field which contain the distinct values of Employee in a column and you make selection in there.
Example Script:
Table:
LOAD * Inline [
Employee| Meeting Type| Meeting Hours(Minutes)
A,B,C| Sales Pitch - XYZ Client| 60
A,B,H| Sales Pitch - XXX Client| 60
X,B,Z| Sales Pitch - YYY Client| 30
H,I,J| Sales Pitch - ZZZ Client| 30
D,E,F| Sales Pitch - AAA Client| 60
A,B,D| Sales Pitch - BBB Client| 30
M,N,O| Sales Pitch - CCC Client| 30
] (delimiter is '|');
LinkTable:
LOAD Employee,
SubField(Employee, ',') as EmpSelection
Resident Table;
Do you mean to select from the front end?
Click the Employee list box and type
( *A*|*B*)
in the search box.
But you might be better off is you "de-concatenate" the field using SubField during the load.
EDIT: which Sunny has now added to his post
Thanks for your help Sunny. I created a link table as you suggested.
Thanks for your help Jonathan.