Working with Value Lists
Thank you for joining me for this video working with value lists. For Sir Boy University, I’m Bob Cusick. In this lesson, we’re going to take a look at value lists. You can use value lists to display values in a combo box, or to display checkbox or radio button values, and you can also use them to display a piece of related information in a field, either on a form or on a report. Once you get started, come over to the value list node, right click, and say create value list. You have the value list in name, so in this case, we’re going to make one that shows the customer name based on the customer ID. So we’ll call it customer. And then click OK. Now value lists can be made up of custom values, which are hard-coded values that you enter. They can come from a global method. They can come from a table. Or they can come from related values. So let’s start with custom values. Custom values are simply values that you enter. So I can type red, blue, green. And then the value list would have simply those three values. Now, there may be times that you want to display red, blue, or green, but return a different value. So for example, I may want to return the integer value, zero for red, one for blue, and two for green. So to do that, using custom values, it’s very easy. We come over here and put a pipe character. And then the return value. So red will return zero. Blue will return one, and green will return two. You can also use I18N or internationalization values by putting the message and the key rather than the hard-coded word red. That way you can have internationalized custom values. We’ll talk more about I18N in a separate video. And most of the time, you’re not going to use custom values. You may have a few value lists that have a few static values that you want to pull. But in general, you’re going to either want to set your value list based on your own custom SQL query, or you want to show values that are inside of a table. So let’s take a look at the next method, which is using a global method to return your values. So here, if you check global method, it’s going to prompt you to choose a global method. Now I don’t have any global methods defined here in this particular solution. So I’m going to say create a global method. Now sort of what it will suggest a get data set for value list global method. And I’m just going to click OK. And what it’s done is it’s actually created a fully commented sample for me to go by. So I’m going to click OK here. Now what we’ll do is we’ll pop over to our global’s JS. And I open that up by coming over here to global’s open in script editor. So let’s click that and see what it looks like. So sort of what it created for me, a fully commented method. And this global method will go ahead and automatically be called every time that value list needs a value. And basically what the code is doing is it’s doing a custom query. And it will return that data every time that the list needs it. Right now the takeaway is that you can create a global method and let the value list use that rather than custom values. Let’s get back to our value list. One of the most common uses for value list will be to display table values. And that’s what we’re going to do in our particular value list. So click table values and then choose the table that you want it to be based off of. In this case we’re going to use the example data connection and our customers table. Let me click OK. Now down at the bottom there are three different lists. And each one have the same values. They’re all showing the columns from the table that I chose, which is example data customers. And there are check boxes. Show in field list and return in data provider. This means that you can choose up to three values to display three different fields of information to display. And you can return a different value than what’s displayed. So for example, in this value list, we don’t want to show the customer ID. But we do want to return that value into the field that we’re choosing. And we do want to show the company name. And we also want to show the contact name from that company. And we want to separate the two values that are being shown with let’s do a space and a hyphen and a space. So let’s just stop right there and we’re going to save this value list. Now we’re going to come over to our orders form. I’m going to show my properties here. And let’s take this customer ID field. And see what it looks like in the smart client. So as you can see, this is just the ID of the customer. But what we want to display is the company name and the company contact. So let’s assign our value list to this field and see how it changes. To assign the value list, I click on the customer field, scroll down to the bottom of the properties and double click on value list. Now here’s our customer value list that we just created. I’m going to click OK. Once I save the form, I can view it in the smart client again and let’s see how it changes. And here it is. Here I’m seeing the customer, a hyphen, and then the contact name. Now I could have created a relationship between the orders and the customers. And I could have placed two related fields on here just as easily because all I wanted to do is display the value. But you can use a value list. And the great thing about this is now I can reuse this value list for pop up menus or radio boxes or whatever I want. So sometimes it’s way easier to just create a value list to display related values than creating relationships and placing related fields. All right, let’s continue to explore the value list options. Back on our value list here, customer, let’s close this. There are a number of other values that we can set as well. One of them is the fallback value list and allow empty and we can have a sorting definition as well. So let’s take the fallback value list first. Why would you ever need a fallback value list? Well, let’s take the case of showing a value list of only the active users. So you define it either through a global method or you set it yourself or you have a relationship that shows only the active users. And then let’s say in six months, one of the users that was previously active is now not active. But what’s going to happen is that their name won’t show up anymore. So we’ll just have the ID value because the current value list doesn’t have them as being active. So if you use a fallback value list, the fallback value list would be a different value list that contained all the values for all the users. That way, even though a particular user is no longer marked as active, their value will still show up in the field. The allowed empty value is when you want to give the users a choice to choose nothing. And the sorting definition is actually pretty cool because in our value list, we’re displaying the company name and the contact name. So most of the time you want to create your sort to contain the same fields that you are showing in the value list. But you don’t have to. You can sort of buy any other field in the table even by related values. We’re done with this value list, so we’re going to save it. So we’re going to create another value list. We’re going to right click on value list, say create value list. This one will be custom values. And we’ll just go ahead and use our red blue green example. Red’s going to return zero. Blue’s going to return one. And green’s going to return two. So we’ll keep that custom value list so we can see how to display it a little bit later. Let’s say that one. Now let’s create another value list. Because on our orders form here, we also have the employee ID. So what we want to do is show the employee name. So we’re going to do the same thing again. We’re going to create a new value list. We’re going to call it employee. And we’re going to also use table values. This time we’re going to take them from the employee table. So in our example data employees. And again, we’re not going to show the employee ID, but we’re going to choose to show two fields. We’re going to show the first name. And we’re also going to show the last name. We’ll go ahead and sort this as well. We’re going to sort this by last name and then by first name. And we’re going to allow them to choose nothing. So let’s save that. And now let’s come over here to our orders form. Show our properties. And on the employee ID, we’re going to choose our employee value list. We’re going to choose employee here and say, OK. Now we’re going to format this text field as a combo box. So let’s come here and choose combo box. When you have a value list on a combo box, you can either make the field editable or not editable. In the case of a combo box, having an editable field when you have a ID and a different display is not a good thing. So we’re going to make this not editable. Now this will not make the field uneditable. What it will do is it will prevent the user from entering their own value or choosing it from the value list. So let’s go ahead now and save our form. And let’s see how this looks in the smart client. So now we have the employee. And I have a drop down list that sorts them by last name. Notice we’re displaying two different values, the last the first name in the last name, but it’s sorted by last name. And here’s our allow none. So I can go ahead and choose a different person. Now all the user here is seeing is the first and last name of the employee. But what’s actually being chosen is the ID of that employee. All right. So let’s see what else we can do with value lists. Let’s hide our properties here for a minute. Now on this form, I have also, I’ve created a couple of form variables as well, form var one and form var two, just so we can test out different ways to display value lists. So for form var one, what we’re going to do is we’re going to change the display type from a text field to check boxes. And we’re going to assign that value list that that simple value list that we created called custom values. We’re going to click OK. Now let’s see how let’s save the form and see how that looks in the smart client. Well, not exactly cool. I have a scrolling list now of three check boxes. But what I wanted to show is I wanted to show those check boxes going across the screen. Hmm. So what’s happening? Well, let’s take a look. If I come to this field now and take a look at the other options like scrollbars, it says horizontal when needed and vertical when needed. If you want to force the values to go across, then what we need to do is turn off the scrolls. So we’ll set the vertical scroll to never and the horizontal scroll to never. And now what we see is sure enough, our values are going horizontally across the screen. If we wanted to display them vertically, so let’s move this up here. Change the orientation. Now as we change the orientation of the field, the check boxes change their orientation as well. So the hot tip is if you’re using radio buttons or check boxes, set the horizontal and vertical scrolls to never. I can also go ahead and just change these to be radio buttons, again, by changing the display type. So here, if I go to check and change it to radios. Now I have radio buttons that will allow me to select only one of them. Check boxes will allow you to select more than one value. When you have a check box that allows more than one selection, the data that gets stored in the database is a returned limited list of all of the items that you’ve chosen. So let’s change this back to check boxes. And I’m going to put another copy of the field. So I’m going to choose control C for copy, V for paste. I’ll put this right next to it and we’ll change this back to a text area. So we can see what’s going on. Let’s go ahead and click the save button, open it up in smart client. So now I have red, blue and green. Notice how it’s giving me the return values of 0, 1 and 2. And as I uncheck, that will change. All right, let’s format them now as radio buttons and see how that changes things. Now the radio buttons, I’m going to save it. Go back to the smart client. And now as I change them, a single value will be returned into our field. All right, now to see just how far you can take this, I’ve created another form. And it’s not showing, but here there’s a little two. So in this show list, I can navigate to the things that can’t be currently viewed. So the things in bold, there’s a form called customers and a form called products that aren’t being viewed. So I’m going to choose to show the customers form. And on here, I’ve created a drop down and I’ve also created a return value so we can see what gets returned. But I wanted to show you just how far you can take these value lists. So I’m going to create one more new value list. And I’m going to call this customer products. Let’s hide this. And now I’m going to use related values. So related values, let’s you choose any relationship. So what I want to do is I want to show a value list of all of the products, all of the unique products that this customer has ever ordered on any invoice. So I have a couple of relationships that I’ve set up. One of them is customers to orders, which makes sense. And then I’ve also created orders to order details so that I can show the details on the order form. Now I’ve also created another relationship called order details to products. So we’re going to choose order details to products and click OK. So it’s customers to orders, orders to details and then details to products. I can now show a pop up menu that has all of the unique products that my customer has ever ordered. Pretty cool. So we’re going to do the same sort of thing. We’re going to choose to return the product ID, but not show it. And we are going to show the product name and we’ll also sort it by the product name as well. Let’s save this. Come back to our customers form here, show our properties. And then on this form variable, we’ll go ahead and attach the value list. We’ll double click in this called customer products and click OK. We’ll save it. And open up the smart client. And now we’ll navigate over to the customer’s form. And so here I have the customer and when I click the value list, it will show me a list of all of the unique products they have ever ordered. And as I change records, the value list will automatically change for every single customer. Let’s see another example here. So I don’t know about you, but I think that that’s pretty cool. You can use chained relationships to show very complex pieces of data that are multiple files away. And this will work regardless of how the value list is formatted. Again, you can use the same value list to either display values to use it in a combo box, check boxes or radio buttons. Well, that about does it for value lists. Thanks for watching.