Tag ArchiveFilters

ByGraham

Using Excel in Filters

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.

ByGraham

Base Filters – A Great New Feature!

What is a Base Filter?

A base filter is a normal filter (it has to be saved as shared) that is applied automatically by e-Quip in addition to any filter that the user sets. Users can’t see base filters, and they cannot override them. Base filters are created and edited in exactly the same way as any other filter, although they can only be assigned by administrators. You can assign up to three base filters for each screen at the following levels:

a. User: applies to an individual user only

b. Group: applies to all members of a group

c. Role: applies to all members of a role

Suppose that a user is a member of a role called ‘Engineers‘ which has an equipment list base filter of [Equipment Status Class = ‘Active’]. The user  is a member of the group ‘East Cambs Engineers‘ which has an equipment list base filter of [Site = ‘East Cambs Hospital’]. A particular user within that group might be responsible only for servicing defibrillators and the administrator may assign him a  user-level base filter of [Category = ‘Defibrillator’].  When using the equipment screen, every search that the user makes will always be prefixed by:

[Equipment Status Class = ‘Active’] AND [Site = ‘East Cambs Hospital’] AND [Category = ‘Defibrillator’]

There is nothing that the user can do to prevent this. The user himself may have an individual default filter of [Team = ‘EBME’](note that a user’s default filter is not the same thing as a base filter). On opening the equipment summary screen he will see all active defibrillators at the East Cambs Hospital assigned to the EBME team.

What would happen if the user tried to circumvent this by searching for all infusion pumps? Although he might set a filter like [Category = ‘Infusion Pump’], the filter that would actually be applied would be:

[Equipment Status Class = ‘Active’] AND [Site = ‘East Cambs Hospital’] AND [Category = ‘Defibrillator’] AND [Category = ‘Infusion Pump’]

This would never return any data, as it is not possible for a device to be both an infusion pump and a defibrillator.>

 How is this Different from a User’s Default Filter?

A base filter is not the same as a default filter. Base filters are always applied in addition to any user filters. While users may create their own default filters, base filters are imposed by administrators. Users cannot see base filters and in general would be unaware of their existence.

As such, they are used to restrict users’ view of the database, in much the same way as the Footprint Manager.

Why not use the Footprint Manager Instead?

The Footprint Manager is an extremely powerful way of restricting the dataset that a user can see. However, despite its power it has some limitations.

a. It is entity-based, rather than screen-based

In the Footprint Manager you might for example, define an inclusion on [Site = ‘East Cambs Hospital’]. This inclusion works on every screen, so that the user will only see equipment at East Cambs Hospital, will only see locations at East Cambs Hospital, will only see jobs at East Cambs Hospital, spare parts at East Cambs Hospital, and so on. You might want the user to only see equipment at East Cambs Hospital, but still allow him to view spare parts at other sites. This cannot be done with the Footprint Manager.

b. The problem of missing data

Suppose that an engineer works only on Philips equipment and you set an inclusion on [Supplier = ‘Philips Medical Systems’].  Note that there is more than 1 supplier field on the equipment screen; there are at least 6 separate suppliers: the original purchase supplier, new purchase supplier, spare part supplier, training supplier etc. If a user can only see data where the supplier is Philips Medical Systems, then every one of these supplier fields would have to be set to Philips Medical Systems. This is not going to happen in practice. For a large number of records many of these suppliers fields will be empty.

So if there is a device which was purchased from Philips but for which no training supplier has been set, this means that if users with this inclusions are to be able to see the device then they must be able to see devices where a supplier field is either Philips Medical Systems or is empty. This is problematic, because now these users can not only see Philips equipment, but any equipment where any supplier field is empty. This is a considerable problem,

c. Hierarchical Data

Suppose that a group of users work from  a workshop associated with Operating Theatres, and are only interested in equipment located within the theatre block. If the Theatre Block is comprised of multiple locations (Theatre A, Recovery, Theatre B, Recovery, etc), then using the Footprint Manager, each individual location must be added as an inclusion. If a new location is added, then it must also be added to the inclusion.

Ideally, it would be better to have a filter of [Location Path starts with ‘/Theatres/’], which will pick up all sub-locations of Theatres, but this is not possible with the Footprint Manager.

How do you Assign Base Filters?

The User, Group & Role Managers have been modified to allow you to assign base filters at the appropriate level.

Conclusion

Base filters can be a much simpler way of restricting a users’  dataset. They can be used on their own or in conjunction with the Footprint Manager to give you very fine control over what data users can see.