We have recently had a couple of requests for a way of copying equipment serial numbers from a spreadsheet into an e-Quip filter. Surprisingly, this is very easy to do, it just needs a little bit of knowledge about Excel formulae.
Let’s suppose that you have a spreadsheet with a list of devices:
Now you need to try to find these equipment records in e-Quip, maybe so that you can add them all to a Medical Device Alert or perhaps to a contract. How can we use the information from Excel to create an e-Quip filter? Of course, you could copy and paste each individual cell, but that would be annoying if there were 100+ cells.
Let’s have a look at what a filter on several Serial No’s would look like in the Advanced Query Builder of the Filter Wizard. I’ve manually created a filter for 2 different Serial No’s and just changed the AND for an OR.
One way to approach this would be to keep on adding “[Serial No = ‘xxx’]” blocks until we have the full list. Again, difficult if we have several hundred, but let’s have a go. What happens if we just copy and paste from Excel into the Filter Wizard? In the example below I have just selected all of the Serial No’s in the spreadsheet and pasted them into the Filter Wizard:
Hmm, not a lot of use; this leaves us with lots of typing to do to put “[Serial No =” etc. around each value and to add lots of “OR’s”. Still, there is the germ of an idea here. Excel is very good at doing this kind of thing. This time, I’m going to add an extra column to the spreadsheet and put a formula in it, like so:
Look at the formula that I have put into cell B2:
=”[Serial No = ‘” & A2 & “‘] OR “
The “=” just tells Excel that what follows is a formula.
Then we have some text enclosed in double quotes: “[Serial No = ‘“. This means to literally insert the text [Serial No = ‘ (that’s why it’s called a string literal).
Then we have & A2. In Excel “&” means “join 2 bits of text together“, so this will stick whatever text is in cell A2 and add it onto [Serial No = ‘. This will give [Serial No = ‘1234.
The last part of the formula, “& ‘]”. This tells us to simply stick ‘] OR onto what we have so far, giving [Serial No = ‘1234’] OR.
So far so good. Now we just have to copy that to every row. This is easy in Excel. You could use Copy & Paste (Ctrl+C and Ctrl+V) but Excel make sit even easier. Just put the mouse over the bottom corner of cell B2 (in the red box shown below).
The cursor will change to a small “+” sign. Just double-click on that and the formula will be copied to every row.
Now all we need to do is to select the 2nd column and paste it into the Filter Wizard, as shown:
We just need to delete the last “OR” which is hanging off the end off our filter:
Hey presto! We have a filter that could be several hundred or even thousands of lines, all as a result of a few clicks. You haven’t needed to know anything about SQL or about the structure of the database.
Can we improve on this? Yes & No. You can save fractionally on the typing and marginally on execution speed, but you need a bit more knowledge about SQL and the database structure. Is it worth it? You decide.
First, you need to know that the Serial No of a device is held in a database field named SerialNo, and secondly you need to know about the SQL IN clause.
This time, in the formula in B2 put:
=”‘” & A2 & “‘,”
As you can see, this involves marginally less typing than before. Then copy & paste the formula to all rows as before. Now your spreadsheet should look like:
If you now open the Filter Wizard and switch to the SQL tab, type in:
SerialNo IN ()
like so:
Now just copy & paste the Excel column into the space between the two brackets and delete the trailing comma:
Is this an improvement? You typed slightly less in Excel and slightly more in e-Quip. Offset against that you had to know a database field name and you had to be familiar with the SQL IN clause.
Use whichever way you are most comfortable with. Either way, if you have to search on a list of several hundred Serial No’s then a tiny bit of familiarity with Excel could save you a lot of time.
This same technique can be extended to search on a wide range of fields for different data types.