A HOW TO for creating a mobile Room Site Survey App- Part 1

Of all the Asset/Facility Management visualisation development tools I have been exploring, I felt the need to develop an app that can be used on mobile devices for site & room surveys.

This is the one item that a lot of commercial FM systems offer. Even OpenMaint you have to have a subscription for using their mobile app. The only one that I have found that offers a free mobile App for asset capture is Asset Tiger 1. free Online Asset Management Service with Free mobile app and its a pretty good one too. You can adapt it to your needs.

The other method is to use a survey tool Free Mobile & Web Survey Application for easy data collection using Epicollect5 or Free Mobile & Web Survey Application for easy data collection using Open Data Kit (ODK)

The intention of the app is to be able to go to site and look at the condition of a room and be able to update information held in the database on this room.

The tool completes the circle in the tools I have been developing so far.

The BIM models that can capture quantities of items in Rooms or Assets (a specific tool developed during my time with Wellington City Council (WCC) and the SAMP team there). These tools cannot give you the actual condition of those assets. You can have a stab at guessing the condition based on age, and if a new build, then you can set the condition of all elements as Condition 1.  But depending on the wear, if maintained and treated well, or neglected and treated badly, the condition over time can be variable.

This quantity information can be stored on the AMIS (Asset Information System), but what about actual condition of what is in place.

The reality is, you have to go and look and assess the actual condition on site. You then need to update the data in your AMIS.

App Inventor

The idea of an App that allows anyone servicing the building, to be able to do some surveying whilst carrying out maintenance items, seems to be an opportunity to explore. Also the tool could be used for a formal building survey process too.

I looked around for different methods of building an app for a tablet/smartphone device and found that App Inventor  was a tool that I could possibly fumble through to use. It is for Android systems only.

My intention is to get the App operational and then look to extend app for iOS and possibly Win10.

So, this is my preliminary design of the App.

Training Tools & Sources for Development of App

I looked online for some tutorials and found some nice apps in App Inventor that I could look to adjust to my needs.

1. The video tutorials by Derek Banas I think are great. Fast and good coverage of topic, with good support material. Follow Link.
2. Item 5 above. The particular tutorial by Derek Banas that I thought was brilliant is the connection to MySQL database, that led me to consider connecting to the Dataset for updating. Follow Link. I followed his tutorial and built his app. It  allows you to put contacts in a database and update. Thus began my trials and tribulations with PHP, AJAX and all other calling programmes to databases.
3. Item 6 above. I also used the MIT MapIt tutorial to build the app that shows a location on a map. Follow Link. This could incorporate , in future using the Room coordinates to show location on map for service personnel. I would like to develop that strand at a later date. From that I developed the Location page of the App, although it is a bit slow and needs work, but it does get coordinates at this time (I haven’t doe anything with them as yet.
4. Item 7 above.For Camera app and using TinyDB I used the Derek Banas tutorials above. To date I can take photos and store them on my device but have not put room code info into file name yet. To be developed.
5. Item 4 above. Scanner. Tutorials for this were from Derek Banas and others. The one issue I had was that I had to upload ZXing scanner (on Google Play). Here is Github Zebra Crossing Scanner page.
6. Items 1 & 2 above.The Derek Banas videos showed how to link button to an external website.

So, after templating the App it got down to the fundamentals of, after getting a Room Code, going and fetching data on that item.  This was not as simple as I initially thought.

I had to step back from the MIT App Inventor tool to focus on simple web retrieval  and updating of data from a database.

Database Access Challenge

Building & Accessing Database on Web using CodeIgniter & CRUDigniter

The initial Derek Banas tutorial on data retrieval from MySQL works for a single line of data. The dataset that I am working with has several lines of data. The exercise I have set myself included fabric data for floor, wall & ceiling and each having a line for fabric and one for finish, so minimum a total of 6 lines of data to be retrieved and updated. So I had to look at a number of different tutorials that would retrieve more than one line of data.

I could have simplified the dataset, but I set myself the task of trying to be realistic in using an existing dataset that was based on one table (a further exercise would be to extend to multiple tables, especially for the data drop from RTV Reporter Pro that takes data out by category to separate tables).

So, the simple App Inventor didn’t meet my needs. I started to look for web based solutions and came across the PHP & Ajax CRUD processes (Create, Read, Update, Delete) and there were quite a few examples and tutorials on the web & YouTube on the subject. I built a few of them, some using  straight PHP & some with PHP (Mysql & PDO) and I had some of them running. I then had to look at modifying the code to suit all the names of my dataset (aaaarg!). Then I found CodeIgniter & CRUDigniter.

I was resigning myself to learn PHP to a reasonable depth when I noticed that the CRUD process seems to be pretty common (not that, as an architect I would have been aware of it) so when I came across the CodeIgniter process I was impressed by its structure, the MVC process ( Model-View-Controller approach).

The next part of the puzzle was the automated coding . CRUDigniter allows you to automatically create complete CodeIgniter CRUD for your database tables, with form validation rules, column associations and form controls. This was really exciting as I new that I would be taking several takes building up the Database tables to meet my needs (adding Long/Lat coordinates, Username or updater & Date & also trying to rationalise the Condition structure in the tables (I would be looking to use KNIME for the heavy lifting for that!)).

Security & Database Size questions

In exploring the access to the AMIS, there is an issue of access and data corruption. You need to be able to only let people who have approval to alter the dataset have access, so creating a password login, and also identify whom & when data was altered.

The question that is then asked is what happens to the database? If you update it, what happens to the original record? Do you create a new record and mark the original record as OUTDATED (and possibly bloat the database), or do you write over the original record (and have a lean Database, but need to refer to backup database for earlier records?).

I chose to work with  the latter option. The thinking behind this was using mobile devices for survey you were at the mercy of the devices Wifi or Mobile connection speed, so leaner and faster more preferable.

If poor data was put into database, then a desktop exercise would be the most appropriate way of resetting the data.

Current State of App Development

1. Android App template structured. Compiled and some buttons tested. You can download partial performing app here, RoomSurveyV0-1.apk. Also the RoomSurveyV0-1.aia file (source code).
2. The parts that work are camera, location (partially) scanner (although not tested on other devices and you may need to download ZXing (see above).
3. The parts that DO NOT work are the room retrieval on the App.
4. The Web Application for CRUD is working, but has not got:

• Search interface for searching by specific room number, currently reads all 6000 odd lines of data and presents in descending order by ID.
• Limit to the number of rows being displayed (important for speed and data connectivity speed and download requirements.

(The above 2 items need to be managed by a jQuery link and be developed)

• Login interface developed to a robust state. Login process and table for users up and running but not interfacing with the Web pages yet.

So, for the demonstration of the web form allowing for adding, editing (updating) and Deleting follow this LINK.

(note, this is a copy of the simplified dataset without condition so can be messed around with with gay abandon)

Some further thoughts, part way through the development process.

App Inventor. A few comments on App Inventor programme and testing.

The programme and tutorials are great in my opinion. Well done App Inventor Team. I found the interface between linking the blocks together and then testing on the phone via the 6 digit code brilliant. It is a quick way to see if you have something operational.

I found that putting comments in at specific intervals and then running the app allowed you to get an idea where the code was breaking. The comment with buttons worked to identify how far through the process your code was working before it glitched (as mine did regularly as I am not that great a coder).

I also found that, using the Android platform meant that my phone (Sony Xpiria) had a mini USB port, so it was easy to compile the App on the desktop browser programme, download it to an .apk file and copy it across to phone using a Flash drive with a normal & Mini USB connectors. That way, the app can be tested directly. Note that since it is not a verified app from Google Store you need to allow your device to accept apps from an unauthorized source. See LINK.

A further exercise once I get the app up and running is to test it on a number of different devices, such as tablets with different size screens and in portrait and Landscape modes.

Database & CRUD

The dataset used here is based on the SPM  data structure that was used by WCC, which later was converted to a Technology 1 asset system that incorporated WIP tools. I wanted to look at a process that was flexible and would work with more than a simple one line dataset, but also something that was not too cumbersome as a prototype.

Currently the dataset is only looking at room fabric,finishes, quantities, units and baselife data. I have not included condition as the dataset has a condition spectrum that allows a specific element to be  part condition 1, part condition 2,3,4 & 5. So a bit complex, I would like to simplify it to a weighted system as per the HeatMap blog (HERE) and demonstration (HERE).

Also, another area to explore is pull-down menus for Types & Names Columns to ensure only correct descriptors can be used for updating information to maintain data integrity.

In the CRUDigniter code there is a validation process, the one I initiated is quite simple and may need to be explored for better refinement for data integrity.

Be Aware. One of the issues I had in Debugging the CRUDigniter code was that the URL of the Add/Edit & Delete buttons did not go to the appropriate pages. In the end I found that on the index page (in views) I had to add the index.php/ to the scripts for the buttons in the  index.php file in the view directory to allow the buttons to find the appropriate files. Also, I didn’t realise that the views/layout/main.php file was the primary route to the Add/Edit/Delete.php files, it took me over 4 hours to figure that out, but the code is elegant.