Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
We discussed creating a static value list containing a preset range of values, but there's another way to populate the values in a Value List, and it's called creating a Dynamic Value List. Dynamic Value Lists are automatically created on the fly, based on data that's already in a field. This way the value in the field will be automatically updated each time a new record is added, or the chosen field is updated in an existing record. Then an alphabetized and de-duped list of values will be available in the Value Lists; these Value Lists use something that's called a Field Index to create the lists.
For example, if you click into any field in Browse mode and choose Command+I on Mac, or Ctrl+I on Windows, you can see a field's index. So this way, even if you have multiple records that all have the value of Pending or Shipped, for example, the word Pending will only show up once in your Value List. So when you use this field's index as your Value List, in the case of a Dynamic Value List, you will only see these values once, and you'll see them alphabetized. Now, in the Invoice Detail Layout, what you'll notice is that every record in the Invoice Detail is the child record from a Parent Customer.
If you'll recall from the Creating Relationships chapter, a child related record needs to have the Foreign Key field populated with the key field from the Parent. If you need a refresher, it's not a bad idea to go watch those videos again. So in each Invoice record, we would like to show a dropdown of all of the primary keys of the customers stored in our Customers table. This will allow a user to go into the Invoice Detail Layout, create a new record, but still associate this child record with the parent by choosing the right Customer ID and placing that into the Foreign Key field.
In order to show a dropdown of all of the primary key values from the Customer table, we would need to create a Dynamic Value List. Value Lists are created in the Manage Value Lists dialog, which can be accessed under the File menu, to Manage, and Value Lists. In this case, we'll hit the New button, and we're going to name our Value List. Let's call this one Customers. Now, instead of choosing the default Use Custom Values, which would create a static value list, we are going to choose the first option, Use values from field.
When we select it, we see a new window appear called Specify fields for the Value List "Customers". Now, in this first window, you see this dropdown will contain a list of tables. Once we choose a table, we're going to choose the field that we want to base our dynamic value list on. So in this case, since we want to show a list of all customers' primary key values, we'll select Customers. But you see I can choose from any one of the tables in the database. Now we're going to choose the primary key field.
Let's see what that looks like once we assign this to our field. We'll hit OK, OK, OK again, and in Layout mode, we will select the fkCustomerID field, because this is a local foreign key field defined inside of the Invoice record, and it needs to contain a primary key value from one of the Customer records. So when we select it, we see in our Inspector, in the Data tab, that we do have the fkCustomerID's field chosen, but now we are going to select a pop-up menu, and now we choose the Value List we want to show, and we'll say Customers.
And you see the pop-up menu has the option to allow entry of other values, but that's not going to make sense for the task that we are trying to perform here. So now in Browse mode we hit Save to commit our changes, and now we see, when we click in the field, we get a list which represents every primary key from all the customers in our Customer table. The problem is your users aren't going to recognize a customer by their Customer ID, so there's a way that we can modify this Value List to make it more intuitive to the users.
If we go under File, to Manage, to Value Lists, and either double-click on, or click and hit the Edit button on, the Customer Value List we created earlier, we hit Specify Field again, and now you'll notice on the right-hand side, we have the option to Also display values from a second field. What this allows us to do is not only display all the Customer Ids, and by the way, when a user selects one of these options, the value in the left-hand window, or in this case the primary CustomerID key field, will actually be the value that gets populated into the field.
However, if we want to give users sort of a guide, we can pick another field from that same table, let's say CompanyName, and allow the user to see those as well. We also have the option below to show values only from the second field, or to show the values from both fields but then choose which one to sort by. Since the users really don't need to know the IDs, we'll just select show values only from second field. Now we hit OK, OK again, and then one last OK, and now we see, inside of the field, even though the value of the field is actually the ID, what it's showing us is the value of the second field.
Also, when the user clicks on the Value List, all you are going to see are the names of Customers, instead of the IDs. In this case, you'll notice that our list is a lot shorter, and that's because there are only two records in our Customer table that actually contain Customer Name values. So if we wanted to show all of the customers, we want to make sure that for every record that has a primary key in it, it also has a customer name. Now you'll notice by toggling between the two choices, that the related fields that we have on this layout change values.
We'll talk more about related fields in the chapter on using relationships. By formatting a field as a dropdown menu based on a Dynamic Value List, you can still allow users to enter new values, but at the same time allow them to choose from previously entered values. The way that a new value could be entered in this dropdown is by navigating over to the Customer List and creating a new record, making sure of course that the new record you create has both the primary key value and a company name.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.