I have a dynamic form that is connected to a database. On it is a dropdown that I can populate with all of the records within a table in that connected database. However I want to only populate it with some of the records and not all of them. The records I want to populate it with will depend on what the user selects in two other dropdown fields. The database holds standards for food, pool, playground, etc. These also are broken down into what item they correspond to on the particular form they appear on. Here is how it works now. If the user picks a food form a checklist appears, and then the user could select item number 7 on the form, where a corresponding item appears dynamically on a separate page. It contains the dropdown I want to populate. I want the dropdown to list only those standards in the database that are associated with food standards and item number 7. I can do this using an xml file but it's hard to maintain so if I could do it using a connected database it would be easier to maintain. Hopefully this explains the situation well enough so that someone can understand it.
OK the process to do this was long and laborious and took a lot of research and guesswork but I was able to get the above done. The actual code used was very little. It basically searches for a specific text within each of the values within the dropdown and deletes it from the list if it doesn't match. I have the method I used below and hopefully this will help someone else and maybe they can make it even more efficient.
Fist the database needs a table I named Standards with at least three fields consisting of an Auto number, Standard Id, and Standard text fields. The standard Id field is a text field and holds the information in the following format: Form + Year + Item#, a comma, the Auto number, a forward slash, and finally a level of it being a critical standard from 1 to 7. So the Standard Id field will have an entry like FDA131,1/2. This is an FDA standard from the year 2013, associated with item number 1. It is the first standard in the database table and has a level 2 critical factor. Only the part up to the comma are needed for this process. The rest help with other parts of the form.
Second an ODBC connection is made and the form is linked to it. A dropdown field is created in the form and it uses this connection to create the list containing all the standards in the database. This will include all those not needed as well but the process gets rid of those not needed. More on that later. For the List Items property of the dropdown, I selected the Item Text property to be the field holding the standard text data and the Item Value property to be the field holding the Standard Id data.
Third I have an invisible field that holds the Item number placed in the same index as the dropdown field. It's value is set when I select the item on the checklist page. I have another field that holds the form the user selected. I then placed the following java-script code in the layout ready event for the drowpdown.
var ItemNum = ItemFieldNo.rawValue + 1; // Gets the Item number
var TheForm = CurrentForm.rawValue; // Gets the form the user has selected
var SearchOn = TheForm + itemNum; // This is the text that will be looked for in the dropdown's list
for (var j=0; j < 20; j++) // It has to cycle through this a few times because it doesn't remove all of the items on the first go // around. 20 can be reduced but I'll worry about that after I finish the form. I had well over 300
// standards just in the FDA 2013 group.
for (var i=0; 1 < this.length; i++) // This cycles through the entire list of standards associated with the dropdown
var FrmItm = this.getSaveItem(i); // This variable is set to the value of the current index's value
PosOf = FrmItem.indexOf(","); // Finds the position of the comma in this value
FrmItm = FrmItm.substring(0,PosOf); // Removes everything from the comma on
if (FrmItm < SearchOn || FrmItm > SearchOn) // If the value isn't the same and the text being searched on then ...
this.deleteItem(i); // ... it is deleted from the dropdown's list
This has done exactly what I was hoping it would do. Now when I click the dropdown field I only have those standards I want displayed and can select from just those and not every standard in the database.