Now let's create our operator screen where our production defect tracking records are created and the
operators catching the defect will put in the category of the defect and the reason their name, their
station name and their notes, which we forgot to add to our review.
So we're going to head over to SQL Management Studio looking at our records, the production defect
tracking table stores relationships.
So it's got links to the primary keys for our operator station category and reason.
And this is what has to be put in the database.
Then of course we have information about our repair person, our supervisors and the root cause.
Now if we want to go add the operator notes to our view, we'll just right click on the view and choose
design.
And all we really have to do is go to our production defect tracking, check the operator comments,
hit our execute command, and then you can see for our record the operator comments are now there.
So we'll save this.
And then going back to graphics designer, if we refresh our main page then our operator comments are
going to be shown.
And this is a benefit of using a view.
We can go back and change that.
And the interface is automatically going to show that.
So as we said on the operator screen we're only going to be looking at key fields.
So we're going to use drop down lists to allow the user to select the operator, select the category
and the station.
And we've already done this on our Manage Staff page.
But we manually configured the drop down.
So let's go back and remember how we did this.
And then we'll get started on our operator screen.
So back on our managed staff page on the combo box, which is this object here under the standard palette
Smart Objects.
This is a combo box.
So basically it's a drop down list.
And there's three properties that we need to be concerned with.
Number one under geometry is number of rows.
And this defines how many items are in the list of the dropdown.
So when we go get our list of operators for our operator screen, this number is going to have to be
the number of operators in that list.
If we do right click what's this?
The HTML help will show us how we address this property in a script.
It's not always going to be the property name.
And so we're going to have to access the number lines property in order to manipulate the number of
rows.
Then under our Miscellaneous properties topics we have selected box and selected text.
And again we can look at our help and selected box.
We use cell index in script to manipulate this.
And then for our selected text of course we're going to use cell text.
And so the script first is going to write a query on all of the operators in the staff table on our
operator screen.
And then it's going to manipulate the number of rows to reflect the number of operators that we have.
And then it's going to numerically assign numbers to selected box one, two, three, four, five,
and then the name of the operator to each one.
Now this kind of poses a problem because we can't put the actual ID into selected box because as we've
said before, the IDs are not sequential.
So that's a problem that we're going to have to address is storing these IDs somewhere.
But also are we just going to use the last name or are we going to use the last name and first name.
Well, let's write a query here first that is going to give us one name field and one ID field.
So I will just do select ID comma.
And let's just start with last name from.
And this is going to be our staff table.
And then we only want operators.
And operators are ID of one in our roles table.
And so we will say where role ID equals one which is operator.
So if we just execute this that's going to give us the last name.
But we could have several people with the same last name.
So what we want to do is have say last name and first name.
We can actually go in and use the plus addition operator and then do a single quote and a comma, and
then we'll do another plus and we'll do first name and then we'll give this alias.
And then let's just call it OP name.
And basically this is going to give us one text field that has the last name comma first name.
And this is a much better interface to use.
So I'm just going to copy this SQL script.
And we'll go back to Graphics designer to our operator page.
And so on the operator page I'm going to get rid of our placeholder.
And I'm just going to paste in the visual components for our screen.
And so this is the screen you're going to be building in the next assignment.
These are just static text labels.
And for inserting a new record it's just like our table here where we have to have the operator ID field
station defect and reason and notes.
We're going to need a drop down list so that they can pick which operator they are.
They're going to have to select the category, and then they'll select the station they're at.
Now let's think about reason.
Reason is going to be a little bit different because our reason table going back here, if we go look
at our reasons each reason has a category ID.
We can't really fill out our reason until a category has been selected.
So I have to handle that.
And then we just have a tag for the comments and then we have an insert button.
We'll also give the operators the ability to update any given defect entry just by putting in the ID
field and filling out this information and hitting update.
So for now, let's just focus on the operator drop down.
And I have created a function to handle drop down lists that we're going to go over.
And then you guys are going to implement in the assignment.
So I'll open our VBS editor.
And in our SQL functions there is now a load combo box function.
And basically we send this a combo box on a screen and a SQL query.
And the SQL query needs to return an ID and a description.
So it could be an operator name, it could be a defect reason or a defect category.
And what it does is it executes that SQL query, and then it configures the combo box to show a drop
down list with those text fields.
It also is going to store our ID field so that we can use that on our insert.
So let's take a look at how this script works.
First we just set up our normal Ado objects and then we create a reference to the combo box that is
passed in.
And it is just looking at the base screen for wink and at a certain picture window.
And then through that picture window it is going to attach to the combo box.
So let's take a quick look at that in runtime.
This defect tracking screen is the base screen.
It's the start picture for the application.
And then this picture window here is the screen it's looking for.
If we go and take a look at this.
This picture window here is named.
PWA managed defects, and so any screen being shown in here can be accessed by that script.
So going back to our code it's going to our defect overview screen through the picture window.
And it's looking for that combo box name.
Then of course we're going to disable automatic error reporting.
So we can handle it.
We're going to connect and open our connection object.
Then we're going to execute our SQL query.
And then as long as there's no errors and as long as we have records, we're going to set the number
lines property to how many records we have.
So if we've got five operators then we need to set the number lines property of combo box to five so
we can show the five records.
And then we're using a record index counter because that is going to be the selected index value for
each record 12345.
And so we're going to loop through our records until we find end of file.
We're going to set our index to the current value of index.
So the first time it's going to be one for our first text, it is just going to take the first result
from our query.
And then we are going to create a list of the IDs.
And that is going to be the IDs of our operators here.
And so right now we only have one.
We're going to add a couple of more.
And then for each record it's just going to add to this list.
And it's going to be comma delimited.
And then we're going to increment our index.
We're going to go to the next record.
And we're going to do that for all the records.
And then this list of IDs which is just a comma delimited string we're going to store in the user data
property of this combo box.
And that is just a place I can just stick some values.
And I also put it in the tooltip text so that you can have a kind of a visual aid.
When I explain how this script works, and it's just going to be a list of the IDs that go along with
our text records.
And then of course, if there were no records, then we only have one drop down value.
And that's going to say no reason given for value number one.
And then we'll handle any errors and we will close out.
Now at this point the combo box is completely configured.
And so let's go back and configure this and test it.
Go to our operator screen.
And we're going to do this on the open picture event.
So that way when the picture opens it's going to configure these dropdown boxes.
And I'll go to VBS action.
Then I'm just going to paste in the code.
This is our SQL string that is going to give us an ID.
And then one string value is our operator name.
This is going to be only operators because we're looking for role ID one.
And we're going to send our load combo box CMB operator, which is the name of our combo box and our
SQL query.
And so looking back at this, you can see that the name of this combo box is CMB.
Operator.
So I'm just going to save this and we'll go to runtime.
And first notice how I mouse over and there's an 11 there and a comma.
That's because we right now only have one operator.
So let's go add a couple of operators.
I'll go to manage staff.
I will just put in a couple more operators.
So we have Karen Manning and we'll do.
Insert and then we will have George Carlin.
And he is also an operator.
And we'll do one more a mole chase and we'll hit insert.
So now if we just leave that repair screen and come back now our list has all four of our operators.
And if I mouse over you'll see that we have.
1147 4849 and so those numbers correlate to the IDs for each of these, just like they do here when
we come in the table.
So when we do the insert, it's this number that we have to put into our production defect tracking
table.
So we'll go back to graphics Designer and we have cab category cab station and reason.
So if we go back to our open picture we're going to need to do our SQL commands for each of these.
So let's do category three and we'll go back.
I'll just do a new query window and we will do select star from and we'll go to defect categories.
These are always going to go in order, but we may be changing these.
So I would rather do ID description from defect category.
And then we'll just go ahead and do order by ID just so that they're always in order.
And then we're going to need one for our stations.
And so I could probably just copy this probably going to be the same thing.
But I'm going to replace this with stations.
And then let's see what we get.
Yep.
That's going to be about the same thing.
So I am just going to copy both of these.
Go back to our operator table.
We'll just reuse this and we'll say, okay, our SQL string is going to be that that's going to be our
stations.
And then I'll just copy this here and it's going to be.
Station.
And then I'm just going to copy this right here.
And it's going to be.
And CMB category.
All right.
So we have our station SCL, CMB station.
We have our defect category, CMB category.
Let's go check and see if this works.
There's CMB category CMB station.
We got our names right.
We're going to save this.
Looks like we got it.
So we do have our categories.
Some of these are below your screen.
We'll fix that.
And then we have our stations.
We have our operators.
Now what we need to do is when we pick a category then we need to show reasons based on that category
here.
So going back to Graphics Designer, looking at our category combo box and our recent combo box, what
we want to happen is when a new category is selected, then we want to populate the reasons based on
the selected category.
And so how do we know when this changes?
Well, if you recall the selected box property changes when the user in runtime makes a different selection
here.
And we can know which one was selected just by looking at the selected index for every property in runtime.
When it changes, it generates a change event.
So here's the selected box change event.
And we can put a VBS action here.
And we can just call our load combo box.
And we'll load our reason.
And we're going to select the ID and description from defect reason where the category ID is the item
selected index.
Well if you recall, the item is always a reference to the object that this script is on.
So item represent category drop down box.
So it's just like a really handy reference.
And so basically once that script runs this is going to be selected index number one.
And so it's going to return the category ID number one.
And it's going to fill in our reason.
And so if we save this and then go back into runtime and we're going to change the screen.
And when we select a different category then it's going to populate this.
Now for mechanical in the database we don't have any reasons.
But for electrical we've got several here and for cosmetic we have a few here.
So that's working.
Now we want to make this also populate when the page loads.
And since category number one is always selected here then we can just set this to category number one
when the page loads.
So if we go back and we'll go to our category dropdown.
And we're just going to copy this line here.
And then on our open picture we'll just paste that in here.
However, we can't really do item dot selected index.
We could just put one in here simply because we know that our category is always going to start with
one.
And so it's going to be fine to do that.
And I'll just save this and we'll go back to runtime.
And it automatically loads.
Now that we have our dropdowns populating nicely, let's go back to the operator page and finish.
We have to be able to capture the comments for the operator, and then put our code on our insert button.
And also our defect ID needs a tag so that we can perform an update also using information in these
controls.
So we'll go back to Graphics Designer first.
And this object here is just an I o field in tag management I've created a group called Operator Screen
Tags.
And I've created a defect ID tag which is unsigned 32 and an operator defect comments, which is a text
tag.
We're going to use these to capture the input for our insert and update.
So for our I o field here, you can just drag these right out on the screen and it'll create an I o
field and format it.
And looking at this I o field, you can see that we have a tag assigned here operator defect comments.
And it is formatted as a string.
And I just made it big and set my font to something.
And we'll see this in runtime.
And then also on our defect ID I've attached it to the defect ID tag.
And that's so that when we perform an update we know which record that we are going to update by the
ID field.
So looking at our insert button.
We've created a script and basically we have to go get the actual category ID.
Reason ID, operator ID and station ID we can't use the text.
And so what we've done is taken advantage of how we've put our ID information into the user data property
of our combo boxes.
And again, the user data property is just a place to store information.
So if you recall we can mouse over this.
And I've also set up the information on the tooltip text just because it's easier to explain it.
Then these IDs represent in order the IDs in the database table.
Same thing here.
And so what we're going to have to do is go into our user data property and find which of these IDs
corresponds with our selected value, be it category, reason, operator or station.
And so for our category, what we're doing is we're taking the user data and we're calling the split
function.
And basically split will take a string that is separated by some common character, like a comma or
a tab or a space.
And it puts it into an array.
And an array is just a list that has elements.
And so it breaks it out of the string, gets rid of the commas.
And the way that we figure out which number in this list we need is we just take the selected index
and subtract one.
And the reason that we subtract one is because the arrays always start with zero.
You have a zero index, a one index, a two index or three index.
But looking at our combo box, the selected box there is no zero selected box.
So basically for selected box one we are going to want to get the zero element of the array.
So going back to our script on the insert button, we now have the correct ID value for the selected
category.
And we're going to store that in the category ID variable.
I just printed this out for debugging purposes.
And we're going to do the same thing for our category reason.
We're just going to take the comma delimited string out of user data, split it put it into an array
and then we'll get the selected index.
For that reason combo that's going to give us our correct ID field.
And then for our comments we're just going to read the operator defect comments tag and put it into
operator comments.
Next we're going to just create a string variable to hold our SQL commands.
And we're going to create an Insert statement to our production defect tracking table.
And here I'm just listing our column or field values in order.
And then I'm creating a values list and just putting in our variables that are holding the information
that we got from our combo boxes and from our tag now operator ID, station ID, category ID, reason
id these are all integers, so I don't have to have a single quote surrounding them, although I could.
And then for operator comments, it's a string.
So notice that we have single quotes around that.
That's a common mistake people make.
And then we just call our exact SQL function, grab the result, print it out in case we have an error.
And then at that point the new record should be inserted.
And we need to refresh our defect view browser which is a web browser control.
We're just going to recreate our SQL command from our view, and then use build HTML to take the results
from this SQL command.
And create an HTML document.
It returns the path to that new file.
And then because we're using the wink web browser, we have to first set the address of our web browser
to about blank.
Remember, it has to have a change in address in order for it to refresh.
And then we just give it the updated file name, which should show us our new results.
And then finally I'm just clearing out the comments tag just for a cleaner user interface.
So I'll click okay.
Here.
We're just going to save.
And we'll open our main screen and just go to runtime.
So we'll put an entry here.
I'll just select an operator.
Select a category.
And let's say that this is at the power up.
No.
Or low power and low power.
And then I'll just put red two volts low and hit enter.
So it takes it.
And I'm just going to throw up our debug window here.
And we'll just hit insert.
And we see our new record here.
And we've got some print out statements.
So our insert button is working.
Now let's go take a look at configuring the update button.
We'll go back to Graphics Designer.
Into our operator page.
And so notice how the update button is disabled.
And that's because I don't want the user hitting update unless there's some kind of valid number in
here.
So I have the operator control enable linked to defect ID.
So as long as defect ID is zero then this operator control enable is going to be no or false.
And as soon as we put something that's higher than zero in there then it will enable it.
And looking at the script it's about what you would expect.
We're going to go get our category IDs, just like we did on our insert button.
Same method, same code, get our operator comments.
And then in here we're just going to create a SQL command.
And again I'm not using a command object I am just using just a straight string build.
So we're creating an update statement.
And we're just setting each field to whatever our variable is.
And again operator comments is a string.
So we have our single quotes.
And then very importantly we have to do our Where clause to make sure we update the right record.
And this is why we have to have the defect ID on the screen.
And then we use exec SQL, which is turning out to be a very useful function to execute our SQL string.
We catch our result printed out and then of course we update the web browser.
And so we are doing a refresh down here of the wink web browser, clearing out our comment tags.
And we're also clearing out the defect ID tag which disables our update button.
So I'm just going to save this and we'll go back into runtime.
And let's say that we want to update ID 16.
So we're going to set it up for the same user.
That's Richards and its category assembly missing damaged parts operator station one.
And then for the comment let's just say missing shield.
And then I'll put in 16 here to tell it.
Which record gets this update.
We will hit update and we see that it does update the operator.
Comments.
So the last thing is going to be our error log.
So let's go back to Graphics Designer.
And I have created a very simple page.
It's got a header and it's just got a web browser.
And this happens to also be the wink web browser on the open picture event.
Basically, we're doing the same thing that we did in build HTML where build HTML.
I can send it a record set and it will build an HTML file with the results.
Well, in this case we're not doing a record set.
We're doing a CSV file.
And this is going to be our error file.
And we're just going to store this HTML file in our script errors folder where our CSV log file resides.
And I've set its font a little lower.
But this is just a cut and paste from build HTML.
And so we have two files.
We are reading our CSV file.
All right.
So that's this guy.
And we're writing to our error log HTML file.
And we're opening it up in for write mode.
And this blows away any existing contents instead of appending it which would be an eight.
And so for each line of our CSV file we read in the line.
And then we alternate our colors just like we do in build HTML.
And then we are just constantly adding to this string variable, the HTML code for each row.
So here we're creating a row.
And then our CSV file obviously is common delimited.
So again we're using the VBS split function to just split out the line and put it into a fields array.
That way it makes it a lot easier to parse.
And then we're using a for each statement.
So basically FLDS that's short for fields is an array of error information.
And so for each f means basically for each field in this collection of fields we are checking the first
line.
Because the first line is going to be our header which has our location and error description and source
and things like that.
So we want that to look like a header.
And so if it is the very first line then we're going to start with a th which is table header.
And we'll put in that first line value.
Everything else is regular contents.
We do TD which is basically just a cell content.
And then at the end of the row we have to close out that row and we increment our row index.
And then once we've done all the lines in the file then we just close out the table tag.
And then we write to our write file which is our HTML file.
We just write this string of the file.
We end up with an HTML document.
And then.
We close out both of our files and set them to nothing.
And then, of course, if we are going to refresh, since this is a wink web browser, we have to first
set this to about blank so that it sees a file change when we do this next line.
So I'm going to click okay and save this.
And the name of this is Show Error Log.
We'll go to our defect tracking main on our show error log.
We'll do the same thing we did on our other buttons.
Just right click Direct Connect.
And we will do our constant show error log.
And that's going to go to our Manage Defects picture name.
Let's see how that looks I'll click okay.
Save run.
And in run time we'll hit Show Error Log.
And you'll see we have a nice display of our errors here in our bottom window.
Now there's one last thing that I would like to show you, which kind of ties together the whole concept
of a relational database.
Let's take a look here at ID 16.
And this defect was put in by Richards.
And if we go back to the manage staff we see that Keith Richards is ID 11.
So what is going to happen if I try to delete Keith Richards out of the staff table?
What happens to this defect record which he is attached to?
Well, I can just go in and put 11 in here and hit delete and nothing happens.
He's still there.
But if we go to our error log and look, basically it's thrown an error.
And it's because since that operator is referenced in another table, it will not allow you to delete
the person from the staff table because it violates the constraint.
And that's one of the big things about a relational database is it gives you data integrity, and it
does not allow you to orphan data, just through the power of its relationships.
In the next couple of assignments, you'll finish building our view for our defect tracking table to
show the repair and supervisor staff and their stations and comments.
Then you'll finish building the screens for our production defect tracking user interface.
I will see you in the next assignment.