Add Initials Witness with airSlate SignNow

Witness text field

good afternoon my name is Roger England I've been a consultant with Landon for about 13 years now and I've been asked to present a webinar today on connecting witness to databases and the aim of this exercise will be to try and get more people to use this rather underused area of witness I should apologize for the misc weight on our website and the invitation to this session and we are not trying to link Excel to databases but link witness to databases and if any of you choose to leave now because this wasn't what you were expecting and I completely understand but in the hope that I'm going to interview for using databases more we witnessing future then Marshall for John and first of all a few administrative features as I say this this is the first of actually two webinars today I'm going to focus on and bringing data into witness from databases and how we basically connect to external databases and work in that way there will be another session and next wednesday on outputting the results for witness mortals into a database we expect this to the about a 30-minute session we you can submit questions by the chat area on the WebEx screen that you should have in front of you or where if you want to raise question a territory you can always email us at support at langa calm and just in case anybody's unsure you should see a something at the top of your screen to allow you to set up the chat so that you can actually see it there and you need to send messages to the host of the recession so you select host type your message in here click send and then a message will appear there that way we can we can just steal all of your questions obviously you should all be muted so where we can see your questions and hopefully I'll have time to address some loans at the end so why use a database or possibly why not use a database um witness has typically been interfaced to excel and we have very rich functionality in that area but there are some areas of managing data which are rather rather difficult in in XL for one thing the data and the user interface the management of the data the everything from the colors to the layout is all in one place you can't separate the two I think some people probably find this a big advantage and certainly we all use and those sorts of facilities in the you know in XL but the ability to actually separate the data and use different user interfaces and apply different business logic is one of the real strengths of database work where there are formula relating one thing to another they're not at cell level so you're not repeating the same formula over and over again in a big column of data and the it's also not possible to overwrite us and one cell with actual of the formula any single cell and it's not impossible to type over that accidentally in in databases also the way that the data has to be and I think the word is fully typed in a particular way it must be another integer or text or whatever to actually put that kind of validation into Excel takes a great deal of work and to make it really robust and check the logic it's a lot simpler in databases databases tend also to be more multi-user than XL and know that that's not always the case and it's not always a problem in a lot of cases I think it's in user XL is probably just fine but databases do tend to be scalable they can start small grow big they can be really almost as big as you like whereas XL does have some limitations and particularly you need to precise Excel spreadsheets to take the data that you require whereas a database you can just pull what you need the hardware and the applications around database servers if you're using remote servers corporates or observers and things of that sort they tend to be very robust hardware that's optimized for doing exactly that rather than a djembe it was laptop that it's fun to do games and XL and other things there's are worth reporting tools that you can sit on top of databases both web-based tools or indeed use you can use XL as a as a front end reporting tool on top of the database but you have a great deal of choice rather than trying to do everything in XL size I've just mentioned and also the fact that as I say cell level formula are repeated over and over again throughout a spreadsheet so the performance does tend to deteriorate as you want your spreadsheet gets bigger whereas with a database that's much less of a problem i would say that operating witness with a database is not mutually exclusive with excel there's no reason why you shouldn't use the two I would advocate christ Rhonda using the strengths of Excel and it's in the integrated user interface and so on for putting data into a witness spreadsheet a witness model but maybe using it for the data coming out and one other thing that I should mention and it's not always necessary to install the database software itself you don't actually have to have Microsoft Access installed on your laptop to be able to get data out of an Access database into business and so you can you can be pretty flexible some examples of databases Microsoft Access I've mentioned and that's probably the one that most people have fallen over or that have access to Microsoft sequel server is used quite extensively in a lot of businesses we do ourselves here modulus as you are sequel this cloud-based database it's basically a Microsoft sequel server app my server farm somewhere in California and maybe show you the little better than anyone Oracle the Big Daddy of databases perhaps my sequel a free database that is used to support many wikis and things of that sort and these are the sorts of different range of data based providers that you might see on a typical laptop that's a screenshot taken from my laptop yesterday one thing that I hope is to try and dispel a lot of peoples and almost fear of using databases with with witness and I think there's a lot of jargon and a lot of technological stuff that gets in the way and one of the things that we come across is Ola drivers and odbc e open database connectivity in terms of this sort and we could spend a lot of time talking about them but I think in reality you don't need to worry about them except when you actually have to connect your witness and model to the database once it's set up these things mostly run themselves and as I've said they mostly very stable they're designed to be reliable and work in the background and so as I say are hopefully and jump through that and and and dispel some of those those myths and fears within witness we have a number of functions for connecting to database but opening a database and also closing the connection to it and for moving around a recordset so after you've pulled in a set of data and it's held in the memory in witness you can move around that move up and down with the table detect the end with things like database eof is end of file there are functions for moving data from the record set into integer reels and string variables excuse me within witness and there's also one or two other functions which are just touch on today for around how you can explore the data so first of those d be open and DB openx these are two functions in witness they both fulfill the same function DV open X tends to be used for LA connections and DV open for odbc connections I tend to find that there are almost one in the same and I frequently find that I've actually used the wrong one so it's probably not necessarily sweat this too much open as I've mentioned means opening a recordset it's not just opening a connection to the database its opening records head and pulling a set of data in so you need to provide a data source which is that's the connection where you would like to get the data from but you also need to provide a sequel statement come back to that in the second which is a request to pull some specific data from the tables in the database you may have to provide a user ID in the password with an asterisk against DB open that's where those while you are often required but if there's no user ID or password set of the database you don't need those the other two parameters which are required for the DB open function in witness our cursor and a lock and in reality I think we need your we set this to three for the static cursor and one for a pessimist o'clock which means that when you grab the data nobody else can have it until you close the connection yes in reality we use those almost all the time there are other options there but I shall talk about those anymore today because these are generally pretty reliable and robust solutions the other thing that we need about DB open is that you did provides a value it returns a value to a variable which we call handle and and basically if the handle equals one you've had a successful connection or a successful operation on the data record set if you get zero then he tells you there's been problem so I'll just dive quickly into a witness model and show you some of this in action and in these models i'm using the user actions and for for this for the demonstration just by the way all of these models and and the access database that will be using today we're going to be made available to this presentation and so we would typically have a DB open x function is used in this way so handle equals d be open x we have a provider data so what technology we use in what's the name of the database perhaps some security information and then critically we have the sequel statement that says what the data is that we require the cursor and the lot at the end and it's handy to put in a little check that if the handle has failed then we put some kind of a message up or if it succeeded then we get success just to demonstrate how what they look that looks like when you run it we get a little message that says success so we know that we've connected and we know that in the background we've actually pulled data from the database into the memory but we can't see is the data itself and we need to then move on to try and removing that into some variables I'll click back to my presentation and talk about that those data source strings in little more more detail I think I'll probably do a blog about this and try and try and dispel some of the complexity of this but a data source string typically has four components we have a connection technology we have the server and database as I saw in the example there was an access database this is actually this distant drum accessing much soft as your database on the cloud because it's a cloud-based technology there's more password information that's required and occasionally there are one or two other parameters of this sort these section don't need to be in a particular sequence but it does help to be methodical and there are various ways that the string can be generated one way is through the DA do link which microsoft provided which is something that's brought up when you're creating a database module in witness come back to that again in a moment you may actually get the be able to get the string from your host or wherever the database is being held if i flip around for to normal as you something i looked at my as your control panel so this is my database on the internet there's little thing here saying chokin connection strings and that just gives me the odbc string that i can then paste straight into we're into my witness model i just have to put in the password and make sure that all the security settings are okay but that's that's not terribly complicated i go back to my presentation and i've just put some examples there of different types of connection string as i say i think i'll i'll skip this and that in law isn't if i go back to witness now move on to a slightly more complicated model and as I saying I've defined the code to pull the information from that particular connection and pull it into my own witness model but it's often easier to work rather than working with a long DB openx statement with a great long string it's often easier to break this down into two sections one is the connection provider so you can then edit this maybe change these manually if you like and also the sequel string you can then build those together there's the connection provider there's the sequence training than the lock parameters at the end and the chapters that perceived it's opened so this model will get that record set then we then having got the record set into memory we can then use this function new first goes to the start of the table that's in memory and then while we are not at the end of the table we can then cycle through incrementing 12 and using this move next function so we go to the go to the start and then record real value so we're moving data from a wrecked our data set a record setting memory into a parameter which of what to sort of two variables VI part and VI time in and at the end of that it can tell us how many have been imported my run that you can see that's reading you meant the data from the Access database it's resizing the dynamic variable as it goes and you can see 2166 records have been imported if you are used to pulling in data from Excel I'm sure you find that extremely slow going so a smarter thing to do if I move to this model is to actually size that actually estimate how many records you need to pull in first so same procedure we have a connection provider string and we have sequels select statement here this is pulling in a simple sequel statement select count as count rows from arrival so and that's really saying count all the records that are in the file and give me a number which is called count rows we can then use that down here to set the dynamic variable size we've got a print statement than there to say how many rows it's going to import and then it can do the same procedure as we had before DB move first move next link round but this time we're not having to resize the variable as we go and think we'll find that that is considerably quickly so if we just reiterate if we check how many records we're going to put in first with one sequel statement we can then redefine the sequel statement further down here that's exactly the same statement as we had before in previous model but then then resize automatically perform in just a few dates on structured query language sequel it isn't as hard as it seems there are whole thick books that you can read about sequel but really for working with witness there are a limited number of things that you actually need to learn it's it's rather like not having to learn the whole of the witness control language in order to makes and models there are any number of cheat sheets on the internet if you just put in sequel cheat sheet you'll find several of them and will probably make one available at the end of this session somehow but it's only required also within within witness to actually get the data into the memory and we can use as you can see here select star from my table simply means getting everything from table call my table or I might just want to have two particular parts in the from the table or I might want to filter it I might want to count things this is possibly as complicated as you're going to get this is just grouping parts by part number and coming up with the average of time in and pulling that in most of the most of what we would call the database work after that is actually not working with the database it's working with record set that seam men drink I'll skip round to the access database that i'm using this just briefly just so that you can see this is a table arrivals and so this is an arrival profile it was actually generated by another witness model so we have a part and we have a time that the part actually come can comes into the model so I'm using the analogy that we might be trying put in some kind of an arrival profile gives have an experiment number and a dummy common follow mend as in most databases which have been made equal on this rather so as we're pulling that data in to witness externally you can see that that data has been building we don't need ride if I go back to a statement I've only actually been pulling in part and time from that database moving on to another so I did one more thing I should mention here at the bottom I didn't introduce this earlier but it's very good practice to include a cloud statement you should really close any table once you've got the data out for various reasons but most particularly because somebody else might want to get get to the data and if you're only working in your moon database then from and skip it but it's a good habit to have in the first place and I'll just very quickly go through a couple of other things that you can do with witness and database functions this modeled if you if you care to download it later and look at it I'm thinking here that there is a field in the table and the database but we don't know which one it is all I know is that the time is in a field that starts with a tee so this simply pulls in and it uses field camp to check how many fields there are in the table and then it walks through the fields one at a time using the DB field name function to detect which one is the one that starts with the T so there if I run that it says our we've got the field is full time in so I'm going to put that in my time in field and it's called in the two 2166 records there's another function that relates to that which is actually detecting what type of field it is before so if you've had a set of tables you can actually use the there is a database field type so you can actually detect whether each field text or were real or an integer and you can actually use that I went on that for now because we need to run out of time in my database get that back up apart from having my arrivals in that way i also have another arrivals table where if I salt my part you can see that i actually have so part 99 there are actually two records part 99 in there from different experiments so supposing we want to actually bring in the average or we want to bring in one or other of those into our witness for model we need to be able to filter them and this again is simply just down to how we configure the sequel statement so here I'm using an input dialog to control this but if I want to pull in just the the items from one particular experiment then I can filter things in that way I'm going to hear i'm looking at the count that i have to do at the start if i scroll down then they're on pulling in my arrivals profile as an average or I'm pulling it in from a specific experiment so here for instance if i run this it asked me which experiment to use that's pulled in all the records from one experiment if I rewind that and run it again pick experiment to you can see there's actually a different number of records in 2066 2161 that again you run it and input mean then the other sequel statement kicks in and it pulls in the average so you can actually say that those could see if it wasn't zoomed out you could see that those are the averages of the two experiments apart from actually doing the filtering in witness so defining that sequel state in witness itself it's also sometimes more efficient to actually do the averaging at the at the database end so I can for instance define a query here which those of you who are used to looking at an Access database as you can see i'm taking I'm grouping by part and I'm taking the average of the arrivals or if you're used to looking at sequel and that's a handy hidden by the way if you want to generate sequel statements you can switch from Dave access mode to sequel mode and then cut and paste that to you sense me that gives you that particular result so this model if you use it instead of defining the sequel statement itself here we simply say from the query and for the user the witness user a query or a table are effectively one in the same thing so all the hard work of doing the filtering all the summarizing averaging or whatever is actually done back on server going back to my statement about the servers you usually optimized for doing this kind of thing so that can make things a lot easier plus if there's a particular summary that you want you can have to get somebody else to write the query in and find item there so that works sorry remaining that user actions number of rows to report and read successfully those of you who have been using witness and databases for a long time are probably used to using the witness a database wizard if I go back to my presentation the witness database module and is defined using the wizard you simply define it build the connection string which you do through this build button add a sequel statement visual design and that will then save in the model it will create a series of functions that summarize and those all the the witness database function that you need to use just show that on model that create something that looks like this so I've created a database module will access in import and let's read the table and you can see it's putting my dummy column experiment part time in and it's created a series of its own functions such as open records which are using the dvo fdd open access and it's wrapping all of those up that was created by that build function those of you who have ever done this will be familiar with that the thing that you would typically do then is you would build those having a building with your the activities into a function as i say i've been using the user actions just as a way of making this a little easier but here I built it built it into this particular function here so that's working through again moving the data from your records but we're using here load current which is there that's one of the functions created by the witness wizard rather than using the DB open statements and so on so that that works that works as well if I run that and need to actually run that so we are good for instance call that function from the initialize actions of nonsense of running this model and hold that through and read successfully the downside of using that that module is that you can only actually have one sequel statement for module without actually having to hack it around a great deal yourself it does add some additional error checking but it does create a loader functions that may not be required so for instance in the model you've got functions for find first when maybe you don't need to do fine first as well as last record or first record and it clatters things up aren't you the wit functions are listed down here you can see DB open DB open exit we've discussed these are the functions that are created by the wizard and you can see that which functions are used in which places and in in several of these cases such as open records open records action references load current so it is a keyboard with these in the asterisk implies then I'm looking at the clock and I'm actually running out of time so the next section that I was going to talk about error handling I think I will have to roll into the next session next week glad to see the knowledgeable he is left in smart of the back middle back in internet time to databases and I will I just skip forward over over these I will try and put that in in a blog or some some message I was just going to go through how and how you can debug your connections it's the connection to the database that usually fail so some resources that will offer for you and the example models and the databases are say will be posted online somehow and we've got your email addresses I believe we'll be in touch in the next few days obviously looking witness help for some more information there is Lana blog and think I have that open and if you go to the Lana website and click on the blog and there you can see various things and this good-looking guy here and you can see I've I've recently been producers and what blog items up on the scenery we're doing quite a lot of work in this name in there the support portal as well has a number of and things those of you who are used to using and the support portal you'll find solutions in there as well and interfaces you can see various things use of a BBC lips and witnessing my Lana youtube channel i think this will become will be posted on YouTube shall soon as already mentioned you can contact us at support going back to the connection strings there are websites that just tell you all you need to know about what connection strings and probably more besides and by all means look at Microsoft for their definitions are no lady bc and over thee and simple cheat sheets and they really are that there is only probably one page worth of commands that you really really need to know and one of my particular pet hates and wear XL gets used for things that really should be done by databases I've got some horror stories that we've named on the internet and so next week and connecting witness to databases for purposes of outputting data so where you're generating large amounts of results putting them in the database but so that you can then do some family reporting the only other thing I should say is that if there's more information that you'd like about this area then by all means come along to our user conference if you go to the website you can find details there the conference is in soil it's on the twenty-fifth of March so when book early thank you for listening thank you for sticking with it and I hope that you were that you developed the same enthusiasm for witness and databases as I did any any other questions as I say if we've not outside anything there we'll get back to you and a copy you on those as well thank you all and good night