1 00:00:04,790 --> 00:00:11,420 Now let's create our operator screen where our production defect tracking records are created and the 2 00:00:11,420 --> 00:00:19,610 operators catching the defect will put in the category of the defect and the reason their name, their 3 00:00:19,610 --> 00:00:23,540 station name and their notes, which we forgot to add to our review. 4 00:00:23,540 --> 00:00:30,710 So we're going to head over to SQL Management Studio looking at our records, the production defect 5 00:00:30,740 --> 00:00:33,950 tracking table stores relationships. 6 00:00:33,950 --> 00:00:40,370 So it's got links to the primary keys for our operator station category and reason. 7 00:00:40,370 --> 00:00:42,650 And this is what has to be put in the database. 8 00:00:42,650 --> 00:00:50,090 Then of course we have information about our repair person, our supervisors and the root cause. 9 00:00:50,090 --> 00:00:56,630 Now if we want to go add the operator notes to our view, we'll just right click on the view and choose 10 00:00:56,630 --> 00:00:57,560 design. 11 00:00:57,560 --> 00:01:04,580 And all we really have to do is go to our production defect tracking, check the operator comments, 12 00:01:04,580 --> 00:01:11,240 hit our execute command, and then you can see for our record the operator comments are now there. 13 00:01:11,240 --> 00:01:13,550 So we'll save this. 14 00:01:13,550 --> 00:01:19,640 And then going back to graphics designer, if we refresh our main page then our operator comments are 15 00:01:19,640 --> 00:01:21,380 going to be shown. 16 00:01:21,380 --> 00:01:24,080 And this is a benefit of using a view. 17 00:01:24,110 --> 00:01:26,060 We can go back and change that. 18 00:01:26,060 --> 00:01:29,150 And the interface is automatically going to show that. 19 00:01:29,150 --> 00:01:33,740 So as we said on the operator screen we're only going to be looking at key fields. 20 00:01:33,740 --> 00:01:40,700 So we're going to use drop down lists to allow the user to select the operator, select the category 21 00:01:40,700 --> 00:01:41,750 and the station. 22 00:01:41,750 --> 00:01:45,560 And we've already done this on our Manage Staff page. 23 00:01:45,560 --> 00:01:48,440 But we manually configured the drop down. 24 00:01:48,440 --> 00:01:51,290 So let's go back and remember how we did this. 25 00:01:51,290 --> 00:01:53,990 And then we'll get started on our operator screen. 26 00:01:54,470 --> 00:02:02,900 So back on our managed staff page on the combo box, which is this object here under the standard palette 27 00:02:02,900 --> 00:02:03,800 Smart Objects. 28 00:02:03,800 --> 00:02:04,850 This is a combo box. 29 00:02:04,850 --> 00:02:06,530 So basically it's a drop down list. 30 00:02:06,530 --> 00:02:10,550 And there's three properties that we need to be concerned with. 31 00:02:10,580 --> 00:02:13,730 Number one under geometry is number of rows. 32 00:02:13,730 --> 00:02:18,350 And this defines how many items are in the list of the dropdown. 33 00:02:18,350 --> 00:02:25,700 So when we go get our list of operators for our operator screen, this number is going to have to be 34 00:02:25,700 --> 00:02:27,500 the number of operators in that list. 35 00:02:27,500 --> 00:02:29,870 If we do right click what's this? 36 00:02:29,870 --> 00:02:35,210 The HTML help will show us how we address this property in a script. 37 00:02:35,210 --> 00:02:38,180 It's not always going to be the property name. 38 00:02:38,180 --> 00:02:44,990 And so we're going to have to access the number lines property in order to manipulate the number of 39 00:02:44,990 --> 00:02:45,500 rows. 40 00:02:45,500 --> 00:02:52,310 Then under our Miscellaneous properties topics we have selected box and selected text. 41 00:02:52,310 --> 00:02:57,830 And again we can look at our help and selected box. 42 00:02:57,830 --> 00:03:02,690 We use cell index in script to manipulate this. 43 00:03:03,020 --> 00:03:08,360 And then for our selected text of course we're going to use cell text. 44 00:03:08,360 --> 00:03:17,300 And so the script first is going to write a query on all of the operators in the staff table on our 45 00:03:17,300 --> 00:03:18,320 operator screen. 46 00:03:18,320 --> 00:03:23,750 And then it's going to manipulate the number of rows to reflect the number of operators that we have. 47 00:03:23,750 --> 00:03:30,920 And then it's going to numerically assign numbers to selected box one, two, three, four, five, 48 00:03:30,920 --> 00:03:34,430 and then the name of the operator to each one. 49 00:03:34,430 --> 00:03:43,220 Now this kind of poses a problem because we can't put the actual ID into selected box because as we've 50 00:03:43,220 --> 00:03:45,950 said before, the IDs are not sequential. 51 00:03:45,950 --> 00:03:51,500 So that's a problem that we're going to have to address is storing these IDs somewhere. 52 00:03:51,500 --> 00:03:57,470 But also are we just going to use the last name or are we going to use the last name and first name. 53 00:03:57,680 --> 00:04:04,730 Well, let's write a query here first that is going to give us one name field and one ID field. 54 00:04:04,730 --> 00:04:07,820 So I will just do select ID comma. 55 00:04:07,820 --> 00:04:11,240 And let's just start with last name from. 56 00:04:11,240 --> 00:04:14,450 And this is going to be our staff table. 57 00:04:14,450 --> 00:04:16,940 And then we only want operators. 58 00:04:16,940 --> 00:04:21,650 And operators are ID of one in our roles table. 59 00:04:21,650 --> 00:04:28,760 And so we will say where role ID equals one which is operator. 60 00:04:28,760 --> 00:04:32,840 So if we just execute this that's going to give us the last name. 61 00:04:32,840 --> 00:04:35,870 But we could have several people with the same last name. 62 00:04:35,870 --> 00:04:40,010 So what we want to do is have say last name and first name. 63 00:04:40,010 --> 00:04:47,870 We can actually go in and use the plus addition operator and then do a single quote and a comma, and 64 00:04:47,870 --> 00:04:53,780 then we'll do another plus and we'll do first name and then we'll give this alias. 65 00:04:53,780 --> 00:04:56,000 And then let's just call it OP name. 66 00:04:56,000 --> 00:05:03,290 And basically this is going to give us one text field that has the last name comma first name. 67 00:05:03,290 --> 00:05:06,860 And this is a much better interface to use. 68 00:05:06,860 --> 00:05:10,850 So I'm just going to copy this SQL script. 69 00:05:10,850 --> 00:05:15,830 And we'll go back to Graphics designer to our operator page. 70 00:05:16,220 --> 00:05:20,300 And so on the operator page I'm going to get rid of our placeholder. 71 00:05:20,300 --> 00:05:25,190 And I'm just going to paste in the visual components for our screen. 72 00:05:25,190 --> 00:05:29,690 And so this is the screen you're going to be building in the next assignment. 73 00:05:29,690 --> 00:05:32,600 These are just static text labels. 74 00:05:32,600 --> 00:05:42,440 And for inserting a new record it's just like our table here where we have to have the operator ID field 75 00:05:42,440 --> 00:05:45,440 station defect and reason and notes. 76 00:05:45,440 --> 00:05:50,030 We're going to need a drop down list so that they can pick which operator they are. 77 00:05:50,060 --> 00:05:55,370 They're going to have to select the category, and then they'll select the station they're at. 78 00:05:55,370 --> 00:05:56,630 Now let's think about reason. 79 00:05:56,630 --> 00:06:02,390 Reason is going to be a little bit different because our reason table going back here, if we go look 80 00:06:02,390 --> 00:06:06,080 at our reasons each reason has a category ID. 81 00:06:06,080 --> 00:06:09,950 We can't really fill out our reason until a category has been selected. 82 00:06:09,950 --> 00:06:11,390 So I have to handle that. 83 00:06:11,390 --> 00:06:15,530 And then we just have a tag for the comments and then we have an insert button. 84 00:06:15,530 --> 00:06:22,400 We'll also give the operators the ability to update any given defect entry just by putting in the ID 85 00:06:22,400 --> 00:06:26,390 field and filling out this information and hitting update. 86 00:06:27,130 --> 00:06:31,780 So for now, let's just focus on the operator drop down. 87 00:06:31,780 --> 00:06:37,930 And I have created a function to handle drop down lists that we're going to go over. 88 00:06:37,930 --> 00:06:40,450 And then you guys are going to implement in the assignment. 89 00:06:40,450 --> 00:06:42,670 So I'll open our VBS editor. 90 00:06:42,670 --> 00:06:47,740 And in our SQL functions there is now a load combo box function. 91 00:06:47,740 --> 00:06:54,490 And basically we send this a combo box on a screen and a SQL query. 92 00:06:54,490 --> 00:06:59,080 And the SQL query needs to return an ID and a description. 93 00:06:59,080 --> 00:07:03,370 So it could be an operator name, it could be a defect reason or a defect category. 94 00:07:03,370 --> 00:07:10,450 And what it does is it executes that SQL query, and then it configures the combo box to show a drop 95 00:07:10,450 --> 00:07:12,970 down list with those text fields. 96 00:07:12,970 --> 00:07:18,760 It also is going to store our ID field so that we can use that on our insert. 97 00:07:18,760 --> 00:07:21,760 So let's take a look at how this script works. 98 00:07:21,760 --> 00:07:29,890 First we just set up our normal Ado objects and then we create a reference to the combo box that is 99 00:07:29,890 --> 00:07:30,850 passed in. 100 00:07:30,850 --> 00:07:38,170 And it is just looking at the base screen for wink and at a certain picture window. 101 00:07:38,170 --> 00:07:43,720 And then through that picture window it is going to attach to the combo box. 102 00:07:43,720 --> 00:07:46,630 So let's take a quick look at that in runtime. 103 00:07:46,630 --> 00:07:49,480 This defect tracking screen is the base screen. 104 00:07:49,480 --> 00:07:52,330 It's the start picture for the application. 105 00:07:52,330 --> 00:07:57,190 And then this picture window here is the screen it's looking for. 106 00:07:57,190 --> 00:07:59,710 If we go and take a look at this. 107 00:07:59,710 --> 00:08:02,410 This picture window here is named. 108 00:08:03,630 --> 00:08:11,700 PWA managed defects, and so any screen being shown in here can be accessed by that script. 109 00:08:11,700 --> 00:08:19,020 So going back to our code it's going to our defect overview screen through the picture window. 110 00:08:19,020 --> 00:08:21,420 And it's looking for that combo box name. 111 00:08:21,540 --> 00:08:24,240 Then of course we're going to disable automatic error reporting. 112 00:08:24,240 --> 00:08:25,230 So we can handle it. 113 00:08:25,230 --> 00:08:27,870 We're going to connect and open our connection object. 114 00:08:27,870 --> 00:08:31,440 Then we're going to execute our SQL query. 115 00:08:31,440 --> 00:08:36,720 And then as long as there's no errors and as long as we have records, we're going to set the number 116 00:08:36,720 --> 00:08:39,390 lines property to how many records we have. 117 00:08:39,390 --> 00:08:45,780 So if we've got five operators then we need to set the number lines property of combo box to five so 118 00:08:45,780 --> 00:08:47,520 we can show the five records. 119 00:08:47,520 --> 00:08:53,910 And then we're using a record index counter because that is going to be the selected index value for 120 00:08:53,910 --> 00:08:55,890 each record 12345. 121 00:08:56,340 --> 00:09:00,810 And so we're going to loop through our records until we find end of file. 122 00:09:00,810 --> 00:09:03,870 We're going to set our index to the current value of index. 123 00:09:03,870 --> 00:09:09,990 So the first time it's going to be one for our first text, it is just going to take the first result 124 00:09:09,990 --> 00:09:11,490 from our query. 125 00:09:11,490 --> 00:09:15,990 And then we are going to create a list of the IDs. 126 00:09:15,990 --> 00:09:21,420 And that is going to be the IDs of our operators here. 127 00:09:21,420 --> 00:09:22,890 And so right now we only have one. 128 00:09:22,890 --> 00:09:24,270 We're going to add a couple of more. 129 00:09:24,270 --> 00:09:27,150 And then for each record it's just going to add to this list. 130 00:09:27,150 --> 00:09:28,710 And it's going to be comma delimited. 131 00:09:28,710 --> 00:09:30,600 And then we're going to increment our index. 132 00:09:30,600 --> 00:09:31,800 We're going to go to the next record. 133 00:09:31,800 --> 00:09:33,990 And we're going to do that for all the records. 134 00:09:33,990 --> 00:09:39,900 And then this list of IDs which is just a comma delimited string we're going to store in the user data 135 00:09:39,900 --> 00:09:41,310 property of this combo box. 136 00:09:41,310 --> 00:09:44,280 And that is just a place I can just stick some values. 137 00:09:44,280 --> 00:09:49,470 And I also put it in the tooltip text so that you can have a kind of a visual aid. 138 00:09:49,470 --> 00:09:55,920 When I explain how this script works, and it's just going to be a list of the IDs that go along with 139 00:09:55,920 --> 00:09:57,450 our text records. 140 00:09:57,450 --> 00:10:03,360 And then of course, if there were no records, then we only have one drop down value. 141 00:10:03,360 --> 00:10:06,780 And that's going to say no reason given for value number one. 142 00:10:06,780 --> 00:10:10,500 And then we'll handle any errors and we will close out. 143 00:10:10,500 --> 00:10:14,580 Now at this point the combo box is completely configured. 144 00:10:14,580 --> 00:10:18,330 And so let's go back and configure this and test it. 145 00:10:18,600 --> 00:10:20,160 Go to our operator screen. 146 00:10:20,160 --> 00:10:23,700 And we're going to do this on the open picture event. 147 00:10:23,700 --> 00:10:28,110 So that way when the picture opens it's going to configure these dropdown boxes. 148 00:10:28,110 --> 00:10:30,060 And I'll go to VBS action. 149 00:10:30,060 --> 00:10:32,400 Then I'm just going to paste in the code. 150 00:10:32,400 --> 00:10:36,000 This is our SQL string that is going to give us an ID. 151 00:10:36,000 --> 00:10:38,910 And then one string value is our operator name. 152 00:10:38,910 --> 00:10:43,380 This is going to be only operators because we're looking for role ID one. 153 00:10:43,380 --> 00:10:50,520 And we're going to send our load combo box CMB operator, which is the name of our combo box and our 154 00:10:50,520 --> 00:10:51,360 SQL query. 155 00:10:51,360 --> 00:10:55,890 And so looking back at this, you can see that the name of this combo box is CMB. 156 00:10:55,890 --> 00:10:56,760 Operator. 157 00:10:56,760 --> 00:11:00,720 So I'm just going to save this and we'll go to runtime. 158 00:11:00,720 --> 00:11:06,390 And first notice how I mouse over and there's an 11 there and a comma. 159 00:11:06,390 --> 00:11:09,900 That's because we right now only have one operator. 160 00:11:09,900 --> 00:11:12,300 So let's go add a couple of operators. 161 00:11:12,300 --> 00:11:13,830 I'll go to manage staff. 162 00:11:13,830 --> 00:11:17,760 I will just put in a couple more operators. 163 00:11:17,760 --> 00:11:20,940 So we have Karen Manning and we'll do. 164 00:11:21,720 --> 00:11:25,920 Insert and then we will have George Carlin. 165 00:11:26,070 --> 00:11:28,350 And he is also an operator. 166 00:11:28,350 --> 00:11:34,170 And we'll do one more a mole chase and we'll hit insert. 167 00:11:34,170 --> 00:11:45,180 So now if we just leave that repair screen and come back now our list has all four of our operators. 168 00:11:45,180 --> 00:11:47,880 And if I mouse over you'll see that we have. 169 00:11:47,880 --> 00:11:56,460 1147 4849 and so those numbers correlate to the IDs for each of these, just like they do here when 170 00:11:56,460 --> 00:11:57,300 we come in the table. 171 00:11:57,300 --> 00:12:03,150 So when we do the insert, it's this number that we have to put into our production defect tracking 172 00:12:03,180 --> 00:12:03,750 table. 173 00:12:03,750 --> 00:12:12,090 So we'll go back to graphics Designer and we have cab category cab station and reason. 174 00:12:12,090 --> 00:12:20,130 So if we go back to our open picture we're going to need to do our SQL commands for each of these. 175 00:12:20,190 --> 00:12:23,400 So let's do category three and we'll go back. 176 00:12:23,400 --> 00:12:32,040 I'll just do a new query window and we will do select star from and we'll go to defect categories. 177 00:12:32,710 --> 00:12:36,340 These are always going to go in order, but we may be changing these. 178 00:12:36,340 --> 00:12:41,590 So I would rather do ID description from defect category. 179 00:12:41,590 --> 00:12:46,750 And then we'll just go ahead and do order by ID just so that they're always in order. 180 00:12:47,050 --> 00:12:50,710 And then we're going to need one for our stations. 181 00:12:50,710 --> 00:12:56,230 And so I could probably just copy this probably going to be the same thing. 182 00:12:56,350 --> 00:13:00,910 But I'm going to replace this with stations. 183 00:13:00,910 --> 00:13:03,070 And then let's see what we get. 184 00:13:03,070 --> 00:13:03,520 Yep. 185 00:13:03,520 --> 00:13:05,380 That's going to be about the same thing. 186 00:13:05,380 --> 00:13:08,440 So I am just going to copy both of these. 187 00:13:08,440 --> 00:13:11,650 Go back to our operator table. 188 00:13:12,420 --> 00:13:19,530 We'll just reuse this and we'll say, okay, our SQL string is going to be that that's going to be our 189 00:13:19,530 --> 00:13:20,520 stations. 190 00:13:20,520 --> 00:13:24,750 And then I'll just copy this here and it's going to be. 191 00:13:25,890 --> 00:13:27,120 Station. 192 00:13:27,120 --> 00:13:29,790 And then I'm just going to copy this right here. 193 00:13:29,880 --> 00:13:32,310 And it's going to be. 194 00:13:35,330 --> 00:13:37,310 And CMB category. 195 00:13:37,340 --> 00:13:37,760 All right. 196 00:13:37,760 --> 00:13:41,930 So we have our station SCL, CMB station. 197 00:13:41,930 --> 00:13:44,960 We have our defect category, CMB category. 198 00:13:44,960 --> 00:13:48,560 Let's go check and see if this works. 199 00:13:48,560 --> 00:13:50,870 There's CMB category CMB station. 200 00:13:50,870 --> 00:13:52,100 We got our names right. 201 00:13:52,100 --> 00:13:53,840 We're going to save this. 202 00:13:53,990 --> 00:13:55,310 Looks like we got it. 203 00:13:55,310 --> 00:13:56,900 So we do have our categories. 204 00:13:56,900 --> 00:13:58,460 Some of these are below your screen. 205 00:13:58,460 --> 00:13:59,690 We'll fix that. 206 00:14:00,020 --> 00:14:02,630 And then we have our stations. 207 00:14:02,630 --> 00:14:03,980 We have our operators. 208 00:14:03,980 --> 00:14:12,650 Now what we need to do is when we pick a category then we need to show reasons based on that category 209 00:14:12,680 --> 00:14:13,310 here. 210 00:14:13,880 --> 00:14:20,270 So going back to Graphics Designer, looking at our category combo box and our recent combo box, what 211 00:14:20,270 --> 00:14:27,860 we want to happen is when a new category is selected, then we want to populate the reasons based on 212 00:14:27,860 --> 00:14:29,390 the selected category. 213 00:14:29,390 --> 00:14:32,390 And so how do we know when this changes? 214 00:14:32,390 --> 00:14:40,160 Well, if you recall the selected box property changes when the user in runtime makes a different selection 215 00:14:40,160 --> 00:14:40,730 here. 216 00:14:41,180 --> 00:14:48,620 And we can know which one was selected just by looking at the selected index for every property in runtime. 217 00:14:48,620 --> 00:14:51,950 When it changes, it generates a change event. 218 00:14:51,950 --> 00:14:54,560 So here's the selected box change event. 219 00:14:54,560 --> 00:14:56,870 And we can put a VBS action here. 220 00:14:56,870 --> 00:14:59,930 And we can just call our load combo box. 221 00:14:59,930 --> 00:15:01,820 And we'll load our reason. 222 00:15:01,820 --> 00:15:09,350 And we're going to select the ID and description from defect reason where the category ID is the item 223 00:15:09,350 --> 00:15:10,370 selected index. 224 00:15:10,370 --> 00:15:16,790 Well if you recall, the item is always a reference to the object that this script is on. 225 00:15:16,790 --> 00:15:21,380 So item represent category drop down box. 226 00:15:21,380 --> 00:15:23,690 So it's just like a really handy reference. 227 00:15:23,690 --> 00:15:29,420 And so basically once that script runs this is going to be selected index number one. 228 00:15:29,420 --> 00:15:34,310 And so it's going to return the category ID number one. 229 00:15:34,310 --> 00:15:36,680 And it's going to fill in our reason. 230 00:15:36,680 --> 00:15:44,570 And so if we save this and then go back into runtime and we're going to change the screen. 231 00:15:44,570 --> 00:15:49,220 And when we select a different category then it's going to populate this. 232 00:15:49,220 --> 00:15:53,060 Now for mechanical in the database we don't have any reasons. 233 00:15:53,060 --> 00:15:58,730 But for electrical we've got several here and for cosmetic we have a few here. 234 00:15:58,730 --> 00:16:00,410 So that's working. 235 00:16:00,410 --> 00:16:05,450 Now we want to make this also populate when the page loads. 236 00:16:05,450 --> 00:16:13,460 And since category number one is always selected here then we can just set this to category number one 237 00:16:13,460 --> 00:16:15,050 when the page loads. 238 00:16:15,050 --> 00:16:19,850 So if we go back and we'll go to our category dropdown. 239 00:16:21,330 --> 00:16:24,330 And we're just going to copy this line here. 240 00:16:26,310 --> 00:16:32,250 And then on our open picture we'll just paste that in here. 241 00:16:32,250 --> 00:16:36,270 However, we can't really do item dot selected index. 242 00:16:36,270 --> 00:16:45,690 We could just put one in here simply because we know that our category is always going to start with 243 00:16:45,690 --> 00:16:46,260 one. 244 00:16:46,260 --> 00:16:49,350 And so it's going to be fine to do that. 245 00:16:49,350 --> 00:16:52,290 And I'll just save this and we'll go back to runtime. 246 00:16:52,290 --> 00:16:54,300 And it automatically loads. 247 00:16:54,690 --> 00:17:01,410 Now that we have our dropdowns populating nicely, let's go back to the operator page and finish. 248 00:17:01,410 --> 00:17:08,880 We have to be able to capture the comments for the operator, and then put our code on our insert button. 249 00:17:08,880 --> 00:17:17,220 And also our defect ID needs a tag so that we can perform an update also using information in these 250 00:17:17,220 --> 00:17:17,940 controls. 251 00:17:17,940 --> 00:17:20,790 So we'll go back to Graphics Designer first. 252 00:17:20,790 --> 00:17:28,290 And this object here is just an I o field in tag management I've created a group called Operator Screen 253 00:17:28,290 --> 00:17:28,770 Tags. 254 00:17:28,770 --> 00:17:35,580 And I've created a defect ID tag which is unsigned 32 and an operator defect comments, which is a text 255 00:17:35,580 --> 00:17:36,150 tag. 256 00:17:36,150 --> 00:17:40,500 We're going to use these to capture the input for our insert and update. 257 00:17:40,500 --> 00:17:49,320 So for our I o field here, you can just drag these right out on the screen and it'll create an I o 258 00:17:49,320 --> 00:17:50,520 field and format it. 259 00:17:50,520 --> 00:17:57,360 And looking at this I o field, you can see that we have a tag assigned here operator defect comments. 260 00:17:57,360 --> 00:17:59,790 And it is formatted as a string. 261 00:17:59,790 --> 00:18:03,720 And I just made it big and set my font to something. 262 00:18:03,720 --> 00:18:05,430 And we'll see this in runtime. 263 00:18:05,880 --> 00:18:11,220 And then also on our defect ID I've attached it to the defect ID tag. 264 00:18:11,220 --> 00:18:17,400 And that's so that when we perform an update we know which record that we are going to update by the 265 00:18:17,400 --> 00:18:18,210 ID field. 266 00:18:18,210 --> 00:18:21,330 So looking at our insert button. 267 00:18:23,310 --> 00:18:29,790 We've created a script and basically we have to go get the actual category ID. 268 00:18:30,000 --> 00:18:34,800 Reason ID, operator ID and station ID we can't use the text. 269 00:18:34,800 --> 00:18:44,010 And so what we've done is taken advantage of how we've put our ID information into the user data property 270 00:18:44,010 --> 00:18:45,390 of our combo boxes. 271 00:18:45,390 --> 00:18:49,800 And again, the user data property is just a place to store information. 272 00:18:49,800 --> 00:18:53,130 So if you recall we can mouse over this. 273 00:18:53,130 --> 00:18:59,250 And I've also set up the information on the tooltip text just because it's easier to explain it. 274 00:18:59,250 --> 00:19:05,040 Then these IDs represent in order the IDs in the database table. 275 00:19:05,790 --> 00:19:07,230 Same thing here. 276 00:19:07,230 --> 00:19:14,400 And so what we're going to have to do is go into our user data property and find which of these IDs 277 00:19:14,400 --> 00:19:21,810 corresponds with our selected value, be it category, reason, operator or station. 278 00:19:21,810 --> 00:19:27,750 And so for our category, what we're doing is we're taking the user data and we're calling the split 279 00:19:27,750 --> 00:19:28,560 function. 280 00:19:28,560 --> 00:19:35,880 And basically split will take a string that is separated by some common character, like a comma or 281 00:19:35,880 --> 00:19:37,050 a tab or a space. 282 00:19:37,050 --> 00:19:39,630 And it puts it into an array. 283 00:19:39,630 --> 00:19:42,810 And an array is just a list that has elements. 284 00:19:42,810 --> 00:19:46,800 And so it breaks it out of the string, gets rid of the commas. 285 00:19:46,800 --> 00:19:54,990 And the way that we figure out which number in this list we need is we just take the selected index 286 00:19:54,990 --> 00:19:56,070 and subtract one. 287 00:19:56,070 --> 00:20:02,370 And the reason that we subtract one is because the arrays always start with zero. 288 00:20:02,400 --> 00:20:05,790 You have a zero index, a one index, a two index or three index. 289 00:20:05,790 --> 00:20:12,360 But looking at our combo box, the selected box there is no zero selected box. 290 00:20:12,360 --> 00:20:20,700 So basically for selected box one we are going to want to get the zero element of the array. 291 00:20:20,700 --> 00:20:28,800 So going back to our script on the insert button, we now have the correct ID value for the selected 292 00:20:28,800 --> 00:20:29,640 category. 293 00:20:29,640 --> 00:20:33,000 And we're going to store that in the category ID variable. 294 00:20:33,000 --> 00:20:35,190 I just printed this out for debugging purposes. 295 00:20:35,190 --> 00:20:39,450 And we're going to do the same thing for our category reason. 296 00:20:39,450 --> 00:20:46,080 We're just going to take the comma delimited string out of user data, split it put it into an array 297 00:20:46,080 --> 00:20:49,020 and then we'll get the selected index. 298 00:20:49,020 --> 00:20:53,700 For that reason combo that's going to give us our correct ID field. 299 00:20:53,700 --> 00:20:59,910 And then for our comments we're just going to read the operator defect comments tag and put it into 300 00:20:59,910 --> 00:21:01,050 operator comments. 301 00:21:01,050 --> 00:21:05,730 Next we're going to just create a string variable to hold our SQL commands. 302 00:21:05,730 --> 00:21:10,170 And we're going to create an Insert statement to our production defect tracking table. 303 00:21:10,170 --> 00:21:15,480 And here I'm just listing our column or field values in order. 304 00:21:15,480 --> 00:21:21,960 And then I'm creating a values list and just putting in our variables that are holding the information 305 00:21:21,960 --> 00:21:28,800 that we got from our combo boxes and from our tag now operator ID, station ID, category ID, reason 306 00:21:28,800 --> 00:21:34,470 id these are all integers, so I don't have to have a single quote surrounding them, although I could. 307 00:21:34,470 --> 00:21:37,440 And then for operator comments, it's a string. 308 00:21:37,440 --> 00:21:40,140 So notice that we have single quotes around that. 309 00:21:40,140 --> 00:21:42,450 That's a common mistake people make. 310 00:21:42,450 --> 00:21:47,880 And then we just call our exact SQL function, grab the result, print it out in case we have an error. 311 00:21:47,880 --> 00:21:51,390 And then at that point the new record should be inserted. 312 00:21:51,390 --> 00:21:57,930 And we need to refresh our defect view browser which is a web browser control. 313 00:21:57,930 --> 00:22:04,830 We're just going to recreate our SQL command from our view, and then use build HTML to take the results 314 00:22:04,830 --> 00:22:06,540 from this SQL command. 315 00:22:06,540 --> 00:22:08,730 And create an HTML document. 316 00:22:08,730 --> 00:22:11,790 It returns the path to that new file. 317 00:22:11,790 --> 00:22:19,260 And then because we're using the wink web browser, we have to first set the address of our web browser 318 00:22:19,260 --> 00:22:20,430 to about blank. 319 00:22:20,430 --> 00:22:24,810 Remember, it has to have a change in address in order for it to refresh. 320 00:22:24,810 --> 00:22:29,460 And then we just give it the updated file name, which should show us our new results. 321 00:22:29,460 --> 00:22:35,790 And then finally I'm just clearing out the comments tag just for a cleaner user interface. 322 00:22:35,790 --> 00:22:37,230 So I'll click okay. 323 00:22:37,230 --> 00:22:37,980 Here. 324 00:22:37,980 --> 00:22:39,930 We're just going to save. 325 00:22:39,930 --> 00:22:44,010 And we'll open our main screen and just go to runtime. 326 00:22:44,010 --> 00:22:46,260 So we'll put an entry here. 327 00:22:47,190 --> 00:22:49,110 I'll just select an operator. 328 00:22:49,110 --> 00:22:51,000 Select a category. 329 00:22:51,800 --> 00:22:55,190 And let's say that this is at the power up. 330 00:22:55,190 --> 00:22:55,670 No. 331 00:22:55,670 --> 00:22:58,760 Or low power and low power. 332 00:22:58,760 --> 00:23:03,050 And then I'll just put red two volts low and hit enter. 333 00:23:03,050 --> 00:23:04,040 So it takes it. 334 00:23:04,040 --> 00:23:06,830 And I'm just going to throw up our debug window here. 335 00:23:06,830 --> 00:23:09,080 And we'll just hit insert. 336 00:23:09,290 --> 00:23:12,020 And we see our new record here. 337 00:23:12,020 --> 00:23:13,970 And we've got some print out statements. 338 00:23:13,970 --> 00:23:16,760 So our insert button is working. 339 00:23:16,760 --> 00:23:20,390 Now let's go take a look at configuring the update button. 340 00:23:20,390 --> 00:23:22,490 We'll go back to Graphics Designer. 341 00:23:23,100 --> 00:23:25,170 Into our operator page. 342 00:23:25,620 --> 00:23:30,030 And so notice how the update button is disabled. 343 00:23:30,030 --> 00:23:34,710 And that's because I don't want the user hitting update unless there's some kind of valid number in 344 00:23:34,710 --> 00:23:34,950 here. 345 00:23:34,950 --> 00:23:39,240 So I have the operator control enable linked to defect ID. 346 00:23:39,240 --> 00:23:46,890 So as long as defect ID is zero then this operator control enable is going to be no or false. 347 00:23:46,890 --> 00:23:52,710 And as soon as we put something that's higher than zero in there then it will enable it. 348 00:23:52,710 --> 00:23:56,340 And looking at the script it's about what you would expect. 349 00:23:56,340 --> 00:24:01,950 We're going to go get our category IDs, just like we did on our insert button. 350 00:24:01,950 --> 00:24:05,880 Same method, same code, get our operator comments. 351 00:24:05,880 --> 00:24:09,030 And then in here we're just going to create a SQL command. 352 00:24:09,030 --> 00:24:14,580 And again I'm not using a command object I am just using just a straight string build. 353 00:24:14,580 --> 00:24:16,680 So we're creating an update statement. 354 00:24:16,680 --> 00:24:21,030 And we're just setting each field to whatever our variable is. 355 00:24:21,030 --> 00:24:23,670 And again operator comments is a string. 356 00:24:23,670 --> 00:24:26,370 So we have our single quotes. 357 00:24:26,490 --> 00:24:32,280 And then very importantly we have to do our Where clause to make sure we update the right record. 358 00:24:32,280 --> 00:24:35,910 And this is why we have to have the defect ID on the screen. 359 00:24:35,910 --> 00:24:43,020 And then we use exec SQL, which is turning out to be a very useful function to execute our SQL string. 360 00:24:43,020 --> 00:24:48,090 We catch our result printed out and then of course we update the web browser. 361 00:24:48,090 --> 00:24:54,480 And so we are doing a refresh down here of the wink web browser, clearing out our comment tags. 362 00:24:54,480 --> 00:25:00,180 And we're also clearing out the defect ID tag which disables our update button. 363 00:25:00,690 --> 00:25:05,040 So I'm just going to save this and we'll go back into runtime. 364 00:25:05,190 --> 00:25:09,330 And let's say that we want to update ID 16. 365 00:25:09,630 --> 00:25:13,410 So we're going to set it up for the same user. 366 00:25:13,410 --> 00:25:19,590 That's Richards and its category assembly missing damaged parts operator station one. 367 00:25:19,590 --> 00:25:25,170 And then for the comment let's just say missing shield. 368 00:25:25,170 --> 00:25:27,660 And then I'll put in 16 here to tell it. 369 00:25:27,690 --> 00:25:30,000 Which record gets this update. 370 00:25:30,000 --> 00:25:35,040 We will hit update and we see that it does update the operator. 371 00:25:35,040 --> 00:25:35,760 Comments. 372 00:25:35,760 --> 00:25:38,550 So the last thing is going to be our error log. 373 00:25:38,550 --> 00:25:41,550 So let's go back to Graphics Designer. 374 00:25:41,550 --> 00:25:44,220 And I have created a very simple page. 375 00:25:44,220 --> 00:25:47,820 It's got a header and it's just got a web browser. 376 00:25:47,820 --> 00:25:53,730 And this happens to also be the wink web browser on the open picture event. 377 00:25:53,730 --> 00:25:59,850 Basically, we're doing the same thing that we did in build HTML where build HTML. 378 00:25:59,850 --> 00:26:04,830 I can send it a record set and it will build an HTML file with the results. 379 00:26:04,830 --> 00:26:07,080 Well, in this case we're not doing a record set. 380 00:26:07,080 --> 00:26:08,970 We're doing a CSV file. 381 00:26:08,970 --> 00:26:11,280 And this is going to be our error file. 382 00:26:11,280 --> 00:26:20,010 And we're just going to store this HTML file in our script errors folder where our CSV log file resides. 383 00:26:20,010 --> 00:26:22,830 And I've set its font a little lower. 384 00:26:22,830 --> 00:26:26,100 But this is just a cut and paste from build HTML. 385 00:26:26,100 --> 00:26:28,080 And so we have two files. 386 00:26:28,080 --> 00:26:31,530 We are reading our CSV file. 387 00:26:31,530 --> 00:26:31,830 All right. 388 00:26:31,830 --> 00:26:32,790 So that's this guy. 389 00:26:32,790 --> 00:26:36,630 And we're writing to our error log HTML file. 390 00:26:36,630 --> 00:26:40,470 And we're opening it up in for write mode. 391 00:26:40,470 --> 00:26:46,410 And this blows away any existing contents instead of appending it which would be an eight. 392 00:26:46,410 --> 00:26:51,240 And so for each line of our CSV file we read in the line. 393 00:26:51,240 --> 00:26:54,540 And then we alternate our colors just like we do in build HTML. 394 00:26:54,540 --> 00:27:01,620 And then we are just constantly adding to this string variable, the HTML code for each row. 395 00:27:01,620 --> 00:27:03,930 So here we're creating a row. 396 00:27:03,930 --> 00:27:08,610 And then our CSV file obviously is common delimited. 397 00:27:08,610 --> 00:27:17,100 So again we're using the VBS split function to just split out the line and put it into a fields array. 398 00:27:17,100 --> 00:27:19,260 That way it makes it a lot easier to parse. 399 00:27:19,260 --> 00:27:22,020 And then we're using a for each statement. 400 00:27:22,020 --> 00:27:27,450 So basically FLDS that's short for fields is an array of error information. 401 00:27:27,450 --> 00:27:35,610 And so for each f means basically for each field in this collection of fields we are checking the first 402 00:27:35,610 --> 00:27:36,000 line. 403 00:27:36,000 --> 00:27:42,420 Because the first line is going to be our header which has our location and error description and source 404 00:27:42,420 --> 00:27:43,110 and things like that. 405 00:27:43,110 --> 00:27:45,330 So we want that to look like a header. 406 00:27:45,330 --> 00:27:52,080 And so if it is the very first line then we're going to start with a th which is table header. 407 00:27:52,080 --> 00:27:54,330 And we'll put in that first line value. 408 00:27:54,330 --> 00:27:56,070 Everything else is regular contents. 409 00:27:56,070 --> 00:28:00,120 We do TD which is basically just a cell content. 410 00:28:00,120 --> 00:28:06,180 And then at the end of the row we have to close out that row and we increment our row index. 411 00:28:06,180 --> 00:28:13,230 And then once we've done all the lines in the file then we just close out the table tag. 412 00:28:13,230 --> 00:28:17,280 And then we write to our write file which is our HTML file. 413 00:28:17,280 --> 00:28:19,200 We just write this string of the file. 414 00:28:19,200 --> 00:28:22,200 We end up with an HTML document. 415 00:28:22,200 --> 00:28:22,620 And then. 416 00:28:22,770 --> 00:28:26,520 We close out both of our files and set them to nothing. 417 00:28:26,520 --> 00:28:33,960 And then, of course, if we are going to refresh, since this is a wink web browser, we have to first 418 00:28:33,960 --> 00:28:40,230 set this to about blank so that it sees a file change when we do this next line. 419 00:28:40,230 --> 00:28:43,740 So I'm going to click okay and save this. 420 00:28:43,740 --> 00:28:45,810 And the name of this is Show Error Log. 421 00:28:45,810 --> 00:28:49,230 We'll go to our defect tracking main on our show error log. 422 00:28:49,230 --> 00:28:51,930 We'll do the same thing we did on our other buttons. 423 00:28:51,930 --> 00:28:53,880 Just right click Direct Connect. 424 00:28:53,880 --> 00:28:57,420 And we will do our constant show error log. 425 00:28:57,420 --> 00:29:02,280 And that's going to go to our Manage Defects picture name. 426 00:29:02,280 --> 00:29:04,710 Let's see how that looks I'll click okay. 427 00:29:04,980 --> 00:29:07,020 Save run. 428 00:29:07,020 --> 00:29:09,900 And in run time we'll hit Show Error Log. 429 00:29:09,900 --> 00:29:14,640 And you'll see we have a nice display of our errors here in our bottom window. 430 00:29:14,640 --> 00:29:20,940 Now there's one last thing that I would like to show you, which kind of ties together the whole concept 431 00:29:20,940 --> 00:29:22,650 of a relational database. 432 00:29:22,650 --> 00:29:26,460 Let's take a look here at ID 16. 433 00:29:26,460 --> 00:29:29,640 And this defect was put in by Richards. 434 00:29:29,790 --> 00:29:35,580 And if we go back to the manage staff we see that Keith Richards is ID 11. 435 00:29:35,580 --> 00:29:41,640 So what is going to happen if I try to delete Keith Richards out of the staff table? 436 00:29:41,640 --> 00:29:46,080 What happens to this defect record which he is attached to? 437 00:29:46,080 --> 00:29:53,130 Well, I can just go in and put 11 in here and hit delete and nothing happens. 438 00:29:53,130 --> 00:29:54,420 He's still there. 439 00:29:54,420 --> 00:30:00,720 But if we go to our error log and look, basically it's thrown an error. 440 00:30:00,720 --> 00:30:08,280 And it's because since that operator is referenced in another table, it will not allow you to delete 441 00:30:08,280 --> 00:30:12,960 the person from the staff table because it violates the constraint. 442 00:30:12,960 --> 00:30:20,070 And that's one of the big things about a relational database is it gives you data integrity, and it 443 00:30:20,070 --> 00:30:26,220 does not allow you to orphan data, just through the power of its relationships. 444 00:30:26,820 --> 00:30:32,610 In the next couple of assignments, you'll finish building our view for our defect tracking table to 445 00:30:32,610 --> 00:30:37,800 show the repair and supervisor staff and their stations and comments. 446 00:30:37,800 --> 00:30:43,530 Then you'll finish building the screens for our production defect tracking user interface. 447 00:30:43,650 --> 00:30:46,140 I will see you in the next assignment.