Send more columns of data through the Multiple Choice Dropdown

Action Form. Pack more data into your Multiple Choice Dropdown, unpack it when an option is chosen

Previous programing experience with MS Access spoiled me with the ability to pack multiple columns of data into each combobox or listbox, and the ability to grab and use each of the columns from the row of the user's chosen item. 

In Action Form, you can pack the additional columns into the value portion of each row in the Multiple Choice field. This ability saves you the need to create additional round trips to the server to look up additional columns from the user's choice in the combobox.

Structure your SQL Stored Procedure to assemble the additional columns into a string that conforms to the JSON data format. Here is a simple stored procedure example.

CREATE PROCEDURE [dbo].[_LIS_GetChaperoneNamesMCWithJSON]

@TripID int

AS 

SELECT C.LastName + ', ' + C.FirstName                                         --The label portion of the data for the Multiple Choice field
+ '|' 
+ '{'

+ '"ChaperoneID":"' + CONVERT(nvarchar(10), C.ChaperoneID)       --The rest of this assembles the other columns of data that is needed for each row
+ '",'
+ '"LastName":"' + C.LastName 
+ '",' 
+ '"FirstName":"' + C.FirstName
+ '",' 
+ '"ChaperoneGender":"' + C.Gender
+ '",' 
+ '"Email":"' + C.Email
+ '",' 
+ '"Mobile":"' + C.Mobile

+ '"}' 


FROM  _LIS_Chaperones C 
WHERE C.fkTripID = @TripID
ORDER BY C.LastName

Here is what this data looks like and is what the MC field uses for data.

Azak, Surish|{"ChaperoneID":"56","LastName":"Azak","FirstName":"Surish","ChaperoneGender":"Female","Email":"NotOnMyWatch@gmail.com","Mobile":"(555) 555-5555"}

Johnson, Howard|{"ChaperoneID":"57","LastName":"Johnson","FirstName":"Howard","ChaperoneGender":"Male","Email":"HJUnreal@gmail.com","Mobile":"(555) 555-5555"}

On the form, the MC data simply looks like LastName, FirstName data, like this:

Azak, Surish
Johnson, Howard

Back on the form, create an additional textbox for each additional column in your data. So in this example, we would have a text box for the following columns:

LastName
FirstName
ChaperoneGender
Email
Mobile

Then on the MC field, in the settings for Bind Expressions > On Change/Click, enter the javascript that will redistribute the columns to the additional fields, like this

//Fill the Chaperone Fields with the one chosen
var txt = '[ChaperoneID]';                                  //Puts the value chosen into a variable
var obj = JSON.parse(txt);                                 //Parses the JSON data into a javascript object
[HiddenChaperoneID]= obj.ChaperoneID;           //Sets the value of the text box named HiddenChaperoneID to the value for ChaperoneID from the object.
[ChaperoneLastName] = obj.LastName;              //Sets the value of the text box named ChaperoneLastName to the value for LastName from the object.
[ChaperoneFirstName] = obj.FirstName;             // and so on...
[ChaperoneGender] = obj.ChaperoneGender;
[Email] = obj.Email;
[MobileNumber] = obj.Mobile;

_________________

Now you have 6 columns of data from the user's Multiple Choice field choice instead of just one, and there was no round-trip to the server to get that data so it is fast and responsive experience for the user.