Asset Tiger 2. Testing features Search, Delete, Export/Import & Data Structure

After writing the initial post which I wrote whilst setting up and populating the database I had a further play, using the Mobile App, or rather, I didn’t get very far at all.

The company who set up the free Asset Management Site make Bar Code tags, so the accessing of data would be by using a bar code scanner as the preferred method of querying the asset in the field.  Fair enough. The programme is slanted towards that method of asset ID capture.

Mobile Search

As you can see, there is not a lot of space for Asset Details on a mobile screen, so all you see are  Description and Asset Tag ID underneath. So not a lot of information there to guide you.
I initially did not place too much thought into what data I put into the “DESCRIPTION” field so I found it hard to search for a unique item using that field. To use the search function you have to know what you are looking for, then you can start to drill down to find it.

My preliminary “DESCRIPTION” was like – [Name]-[GroupCode]-[TypeCode]-[PropertyType] which does not tell you a lot. I need to re-do the description so that you can infer more from that field, like:

Description= ABEB001-02a- Ground- SE-ELE-Incandescent Lights    [PropertyCode] -[PropertyType] -[GroupCode]-[TypeCode]-[Name]

Asset Tag ID=   2073385 – ABEB001-02a   [ID (pk)] -[PropertyCode] (or maybe the other way around?)

This way you will be able to carry out your search and be able to see what you are looking for.

Using Barcodes would be useful too, but how would you organise multiple items to barcodes?

Linking Assets

There is a method using Linking. Linking links assets together (a sort of Grouping), so you could link all the finishes to the Room. If you look on Asset View you will see the linking tab.

If you watch videos 10 & 11 (bottom left corner) about Linking & the Mobile App use.

I’m not sure, if you link assets to rooms, whether you can then link rooms to units, units to blocks, and blocks to sites. May be worth a test. I’m also not sure whether you can do this externally in a spreadsheet and upload or whether you have to do it in the programme (in which case it could be quite a tedious exercise if you have thousands of rooms).

Desktop Search

The desktop search is a bit more versatile. You can click on the Search Criteria Button to get another page where you can specify other search criteria to find what you want. A little cumbersome but it works.

Deleting, export , alter & re-import data

After re-thinking format for Asset Tag ID and Description I decided to delete all the initial 5000 records I had uploaded initially.

When viewing Assets you can view 10, 25, 50 or 250 rows of data at one time. I selected 250 records, selected them all (tick box on left) and selected Delete in the More Actions button . So I had to repeat the process 20 times to clear the database.

It seems you can export all the Asset Data in one go. To date, I have not installed more than 500 records so cannot confirm this, another test for later. But you will only be able to upload it in lots of 5,000. So you’ll have to filter the export file to only 5000 entries for re-upload after modifications.

After exporting, I had the original Upload File from Knime. I altered the upload file to allow for restructuring the Description Field to the modified  method with codes in it (for easier searching).

I then tried to transfer the modified Description Field into the Export file to re-upload. To do this in Excel you can use the VLOOKUP() function. In both sheets I had the Asset Tag ID which is unique so:

I pasted the Knime Export sheet into the Export workbook.

I then used the VLOOKUP() function thus:

=VLOOKUP(B4,Sheet4!A:C,2,FALSE) .   (This was an example on Stack Overflow and worked on a small dataset (8) on 2 excel sheets)

=VLOOKUP(A2,Sheet1!$A$2:$B$5000,2,FALSE)   (This was an example on Stack Overflow and DID NOT WORK on a main dataset  on 2 excel sheets) Error #N/A.

=VLOOKUP(A3,Sheet6!A:AA,2,FALSE)  (This was an example on Stack Overflow and DID NOT WORK on a  dataset   which copied a sample from the Asset Tag ID column from main sheet on 2 excel sheets) Error #N/A.

So after spending hours trying to get this to work (I thought it would be a handy tool to use so tried to master it), I reverted to deleting all the existing asset entries and  loading  them anew. That worked fine.

I maybe need to explore Knime to see if it can do matching for Asset Tag ID. The Cell Replacer node seems to do the trick.

Mobile search with new configuration

I found, after loading the new descriptors for Asset Tag ID and Description I have found assets are easier to search for. It is a slow filtering process. When you type in Part of the Code and eg Abe- (enter) it does a search on this and displays results and the original Abe- stays in the search box, so you can then add the next part, eg  Abe-b-b00-02-02 (enter) and oit will further filter down to a more specific search.

I mention this as in the desktop, after you type in a search Abe- (enter) it will show a result then the text in the search box disappears so you have to type the original string plus what ever extra you need next time. So the Mobile search button is easier to use to find assets.

The one comment I will make with the coding structure I used in the Asset Tag ID and Description is I use “-” to split things apart. This is not a common key on a mobile text screen (to get to it you have to hold down “g” and slide your finger up to the “-” to select it) so I think I will refine the search by using a space ” “,  or “,” or “.” as these are all easily typed on a mobile keypad.

Export and Re-upload

I found the Knime process I set up for the first post for rebuilding the Import file really handy for automating the updating of the files.

I have now learned that when saving as a CSV file the formulae disappear, which is a bit of a pain, so I have now modified the process to read from a XLSX file instead (saves me having to re-settup the formulae when I want to change things)

I filtered a smaller set of about 500 rows so that I can test a few things like export and import. On the first trial of restructuring  Asset Tag ID and Description I was happy with the Asset Tag ID  coding but not with the Description. I wanted to add the room name into the description, and if there wasn’t a room name then I would put in whether it was a block or a Site grounds feature. UI re-imported the modifications quite easily.

I have now modified my Description filed based on the Mobile Search issues above and the string now looks like:

ABEDD014 SE MEC 09 Bath01.Extract Fan – Toilet

With minimal “-” in the line (the last “-” is in the name descriptor, I’ll leave that as I do not want to re-build all the names of asset items as they are infrequent, and usually by this point you have found the asset you are looking for).

Description filed modified for easier typing on Mobile App. Lots of the “-” removed and replaced with spaces.

End comment

Getting the data structure right for the Mobile App for easy searching is important. It is easy to update so its worth fiddling with to get right, just use a small dataset to begin with, just in case you need to delete it all.

The Export/Import process is important and the new Knime process as an alternative to Excel VLOOKUP works to a degree but could be refined further to make it more efficient.

I think the next things to explore in Asset Tiger are Alerts/Calendar Events, Linking and Uploading of Photos and Docs and connecting them with specific assets.