Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a scenario where I need to get the field selections only up to the previous selection.
Example: As seen in the image below, upon selecting the employee names, Ben, Hill, John and Lou, my expression has to display only Ben, Hill and John and not Lou.
Similarly, If we select Ben, Hill, John, Lou and Maria, my output has to be this : Ben, Hill, John, Lou
How can this be achieved ? Any help will be much appreciated.
PFA of the Test Application.
Thanks,
Umar
see this
=mid(Concat(DISTINCT Emp_Name,','),1,index(Concat(DISTINCT Emp_Name,','),',',-1)-1)
see this
=mid(Concat(DISTINCT Emp_Name,','),1,index(Concat(DISTINCT Emp_Name,','),',',-1)-1)
Thank you very much Kushal ! Works perfect.
I also have a another issue with this, its that the values have to change dynamically as per the sort order of the list box !
I have changed the sort order now as seen in the image below,
As per this, the output has to be : Lou, John and Hill.
Is it possible to achieve this ?
Thanks,
Umar
Yes, you can but you need to define the sort order in the script either ascending or descending. In your case you have choose descending so, you have to do order by descending in the the script see below
Table:
load * Inline [
Emp_ID, Emp_Name, Budget,Sales
1, Tom, 100, 50
2, John, 200, 60
3, Hill, 300, 70
4, Ben, 400, 500
5, Maria, 500, 600
6, Lou, 600, 1000
7, Stu,400, 350
8, Vlad,600. 500
];
New:
LOAD *,
AutoNumber(Emp_Name) as Sort
Resident Table
Order by Emp_Name desc;
DROP Table Table;
Define your Sort filed as Sort value in Concat fucntion like below
=mid(Concat(DISTINCT Emp_Name,',',Sort),1,index(Concat(DISTINCT Emp_Name,',', Sort),',',-1)-1)
It does not seem to work ! Maybe I am going wrong somewhere.
Could you please post an example ?
Thanks,
Umar
I have already attached the application. You can see the script as well
Table:
load * Inline [
Emp_ID, Emp_Name, Budget,Sales
1, Tom, 100, 50
2, John, 200, 60
3, Hill, 300, 70
4, Ben, 400, 500
5, Maria, 500, 600
6, Lou, 600, 1000
7, Stu,400, 350
8, Vlad,600. 500
];
New:
LOAD *,
AutoNumber(Emp_Name) as Sort
Resident Table
Order by Emp_Name desc;
DROP Table Table;
=mid(Concat(DISTINCT Emp_Name,',',Sort),1,index(Concat(DISTINCT Emp_Name,',', Sort),',',-1)-1)
No need sort the field. Try like below.
=Replace(GetFieldselections(FieldName,', '), ', '& SubField(GetFieldSelections(FieldName,', '),', ',-1),'')
or simply use without sorting
=mid(GetFieldSelections( Emp_Name,','),1,index(GetFieldSelections(Emp_Name,','),',',-1)-1)
Perfect Nagaraj ! Thank you very much.
Now I do not know which answer to mark as correct since both are right !
Will go with Kushal for the timely response.
Regards,
Umar
Umar,
That's okay. I don't mind it and I really care whether my suggestion helps you or not in any way. Happy weekend.