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

Where we left off at end of Part 1.

Part 2. How to get the search tool working.

I tried to code myself, adding to what I had already achieved, but spent a day going around in circles.

Then I came across this :

Weblesson Datatables Jquery Plugin with Php MySql and Bootstrap.

This was a one file search using JQuery & Datatables.  Its search engine works fine, but it needs to pull up all the records (6285 rows) before it formats itself to only show 10 records and has the Search text box. So a little slow. Unfortunately, in the tutorial there are no edit or delete buttons, so records can be searched but not edited.

So, I have either Add, Edit and Delete or Search, one or the other.

I ended up going back to one of my earlier tutorials that I had worked on, based on a book database and re-edited it for my Database, in the end it did not take too long, but I do recall it looked quite daunting 3 days ago when I first got that particular tutorial operating. So now we have all of the aspects operating, and it filters, using the search text box, down to a specific room for editing

Weblesson Datatables Jquery Plugin with Php MySql and Bootstrap.

 This one got me to where I wanted to go, it still takes about 30 econds to load, so anty editing, adding or deleting refreshes the whole system. So not necessarily what you would need for a survey tool where Wifi/signal could be weak.

So, yes, the process reads from the database, allows you to filter by room and then lets you edit, delete or add data.

The database needs to be developed to add condition information, as we can already get quantities from the BIM model. But the Proof of Concept is working so far.

The next part is to link this to a mobile app, the App Inventor tool I am exploring.

After that, speeding the process up and extending the tool.

Operational CodeIgniter Database tool CRUD ( Create,Read, Update & Delete)

The Tool can be viewed and explored HERE

1. It is SLOW !!!!!
2. It is reading all the records in the database. So the bigger the dataset, the slower it is going to be.
3. One method of speeding it up is to break the dataset up into groups of buildings, each reading a smaller dataset.
4. Another method may be to only partially upload part of dataset to say an XML or JSON file and work offline with that dataset, then update the original dataset later. This was the  reason I explored the 2nd version below, but alas not what I expected.

SitePoint Example with only 5 files

So I looked  at other examples and found a reference to SCRUD  (Search, Create,Read, Update & Delete).

I found this tutorial online from SitePoint Pty Ltd, based on article HERE.

The other aspect I like is that the row settings (5,10,25 etc) are set at the beginning unlike the codeigniter(CI) example, so all the features are on display at the beginning, (rather than loading all the lines in CI and then showing the search button)

The Search function is  fast on both methods, you can just use” -01″ and it looks for all the data based on that criteria and sorts them pretty fast.

I used a small dataset for testing and it did very well with it, but when I came to hook it in to the 6800 lines of code it still took 30 seconds to load and each Add/Edit/Delete command would require a refresh of the dataset, so , another 30 seconds each time. This is because it pulls up the complete dataset.

This pulls the data from the MySQL database via a JSON file. I had thoughts of being able to use the loaded JSON dataset, in a file on the device, so updates could be made to the JSON file without refresh,( maybe this can be done, but not by me at this time with my current skill level).

Database Retrieval Access & Speed

So, after getting 2 methods of accessing database information and displaying on a device working, there are a number of paths I can explore, the 2 simplest as follows:

1. At current state, copy database to say 20 rooms, so that it pulls up complete dataset, but as it is small, speed is adequate.
2. Modify the code so that it only calls to the database in the first instance to upload only the room number required.This would be the logical method  and also fits in with the idea of the app, that scans room number and goes and asks for that info.

So for 1 above, at the moment I can get the app to operate by copying MySQL database, writing a query to trim the data down to a manageable size and then point the programme to that database. I would then need to look at how to merge the updated data into the original database (or an updated copy of it). I suppose there is a merge or update function that could be used. I may need to explore that process.

For 2 above, there are a few challenges.

1. I need the App Inventor App to pass the Room Code across to The SCRUD programme
2. Write a function in the SCRUD programme that will receive this Room Code and query the database and retrieve the information.
3. The SCRUD programme to GET only the data from that room only and when adding/Editing or updating, only Refresh that Room Code information only.

In both methods, the question is, do you use the SCRUD programme to edit the data or try and develop the editing tools in the App inventor App? The App inventor also does the coordinates and the Photos too.

At this point, in the proof of concept, I would go with the SCRUD programme doing the data input and the App doing the photos and coordinates.

Validation & Pick Boxes

Currently, I have been dumbing down the current validation in both the SCRUD programmes, just to get the fundamentals working. In fact, these need to be quite robust.

A method that Simon De Favero of RTV Tools  when developing the Revit Parameter Extraction Tool RTV Reporter Pro for Wellington City Council was to have a DB Table with acceptable naming conventions for inputs ()eg Type CEILING FINISHES (OK) but CEILING FINISH (singular, not acceptable) . He then allowed for pull down boxes to ensure that only acceptable naming conventions were used for inputs, speeding up the data collection process.

Paused state of App development-part 2

1. App Inventer on hold during the SCRUD
2. SCUD programmes explored and operational as a programme, but not versatile enough as it takes too long to load and edit RELEVANT DATA!
3. Next interim step is to update App Inventor App to put a button on Start Screen to call SCUD programmes. I am choosing this direction as I am sick of looking at the SCRUD code files at the moment. A time of reflection and distance.
4. Then figure out how I can get the Room Code to be passed from App Inventor App to SCUD programme
5. Revisit App Inventor App to explore GIS and Camera issues I have. GIS slow & Camera files to have room codes in file name.

Overall not that successful but it gives me an understanding of the issues.

Some key takeaways:

1/ Being able to use the app to capture data off-line. Means that you have to have a database (storage)  on the mobile to store this information in the interim.

2/ Upload part of the information (if doing a re-survey of an existing building) into database (storage) on the mobile prior to doing site visit.

3/ If can work off-line then faster. You only have to download when back in office and update main asset database (once data validated).

I didn’t take this any further. I explored pre-built free alternatives that can be tailored to your data capture requirements like:

Asset Tiger 1. free Online Asset Management Service with Free mobile app 

Free Mobile & Web Survey Application for easy data collection using Epicollect5 

Free Mobile & Web Survey Application for easy data collection using Open Data Kit (ODK)