User Input List - Multiple Text Values as Selection

[Anonymous]

Hello,

I know this question has been posted here before but my data is somewhat different than most. I am an engineer, not a developer. I have large data sets of technical (not sales) data dealing with information about vehicles (identified by their unique 17 digit alphanumeric VIN). VIN = Vehicle Identification Number, e.g. 1A2B3C4DE5F987654. Each VIN is unique but each VIN can have thousands of records in the data. I need a way for the user to input a list of VINs and pass those into the selection for the 'VIN' field. This is only one of several ways that VINs can be selected so I need to be able to do it without reload (preferably copy and paste out of an Excel sheet). From what I have read on other posts, this is not an easy task.

What I've tried so far (unsuccessfully):

1. Pasting list from excel into a single line of a list box > using a button with concat() function to pass the list box variable (which a tab delimited list of VINs) into the selection for VIN
2. From Is there a way to have an input field allow for multiple lines of text?
   1. List box to text box
      • User must enter values line by line
      • I don't believe this will allow me to pass the values into selection.
      • Requires a variable for each line of input > not practical because I need to allow at least 75-100 VIN inputs

Again, I am engineer doing a developer's job so I am not proficient with macros/VBA. Any help here would be GREATLY appreciated.

Thanks,

CB

[steinrich]

Hi Casey,

I appreciate if you have a solution working and would really like to now if QV is feasible to handle multiple input  values for selection.

Please let me know.

Richard

[martynlloyd]

Hi Casey,

Did you solve your user input problem? I have a very similar requirement to be able to paste in a list of chassis numbers to be selected.

Kind regards,

ML

[Anonymous · Accepted solution]

Yes I did actually solve this after many months of trial and error. So here is how it works:

1. Create a variable vVIN_List and an input box for it. User copies and pastes their VIN list from Excel into this input box so the varible vVIN_List becomes something like this (these are all made up VINs):  1A23DEFG32H123456  1B45DEFG32H789456  1C67DEFG32H654987  2A89DEFG32H123456  2B10DEFG32H987321  2C11DEFG32H789123  3A12DEFG32H147963  3B13DEFG32H258741  3C14DEFG32H369741
2. Set up a variable event trigger that converts this into a search mask and passes it into the selection in the VIN field. So the variable event trigger for vVIN_List looks like this:
        Trigger: OnInput
        Action: Select in Field
        Field: VIN
        Search String: ='(' & Replace(Replace(Replace(Trim(vVIN_List), '  ', '|'),' ', '|'), chr(10), '|') & ')'
3. This converts vVIN_List to this and passes this search mask into the VIN field:
   (1A23DEFG32H123456|1B45DEFG32H789456|1C67DEFG32H654987|2A89DEFG32H123456|2B10DEFG32H987321|2C11DEFG32H789123|3A12DEFG32H147963|3B13DEFG32H258741|3C14DEFG32H369741)

NOTE: This has to all be done in one step because the variable event trigger actions are not done sequentially. This was an issue for me in my previous attempts.

I also created a text box which pops up when a user inputs a VIN list that displays the number of VINs they pasted and the number of VINs that were actually found/selected. This is the text box expression:

     =GetSelectedCount(VIN)&' of '&vNoPastedVins &' pasted VINs found (Click to dismiss)'

where the variable vNoPastedVins =len(purgechar(vVIN_List,' '))/17

So far I haven't found a limit to the number of VINs that can be pasted either! It can handle tens of thousands at a time. Hope this helps.

[Anonymous]

See solution below.

[martynlloyd]

That's fantastic,

many thanks Casey!

Marty.

[steinrich]

Great job. Thanks to find this out and share to the community.

[shane_spencer]

Casey that is so awesome - saved me a load of time/coding! Thanx!

[userid128223]

Your solution works perfectly except for when input has spaces. How do you handle that. In my case the input with spaces did not get selected and gets ignored.

for example.

If you have:

temp

temp1

temp3

temp 12

in above case temp1, temp3 gets selected and "temp 12" get ignored. How do you make changes to your script so it can handle spaces in input box.