|  Login
 Building a Web-based Database Application Using IndooGrid Minimize

One thing newbies don't always have is a 'lay of the land' when it comes to doing a project.  When you buy a specific tool (module, other software program), there are instructions for using that tool.  But, what is missing is how you might combine tools, how to approach a project in general.   I recently built my first web-based database application using IndooGrid and learned a lot of things that may be useful to some of you.  I used IndooGrid extensively for this project, but I also used a number of other tools which seemed appropriate at the time.

Here is one way to approach building a web-based database application in DNN using IndooGrid.  Here's my disclaimer!!!!   I have no official computer certifications and this is an unauthorized tutorial for how to use IndooGrid, so you can consider the source!  It's just one newbie to another.

  1. What do you want to do?  

    It's a good idea to think about it a lot before you start designing database.

    I wanted to build a hospital census application for an equine veterinary hospital.  I had stalls that would be filled with patients.   The stalls would be coded as to readiness to accept patients.  Each patient (or sometimes two patients in the case of a  mare and foal) would be assigned to a particular stall.  I wanted a list of all the stalls each day (occupied or not)  indicating the occupancy or readiness status so the scheduler could see what was available when patient appointments were made.  I wanted the board type (adult, mare/foal, isolation) and the intensive care (ICU) code to be recorded for each patient to be entered so the cashier could use the census for billing.  I wanted to know which doctor and which house officer were managing the case. 

    Each patient had an associated name, case number, age, breed, sex, and color as well as an owner, a trainer,  referring veterinarian (RDVM), admitting veterinarian, attending veterinarian and discharging veterinarian, plus a few others.   The owners, trainers, RDVMs, admitting, attending and discharging clinicians might change from time to time.

    The 'admission' or visit was the smallest kernel (record) of information for the patient.  This table was made of the patient ID (linking to the patient name, case number,etc), clientID (linking to c;oemt name, address, phone number, etc.), trainerID, RDVMID, admittingclinicianID, dischargingclinicianID, and  admitdate, and dischargedate.  Details of each item were kept in separate fields in look-up tables (clients, rdvms, trainers, etc.)

    The smallest kernel (record) in the 'census' table was composed of censusdate, stall, admissionID (which provided a link to all the patient, client, rdvm, etc information), boardtypeID, ICUcodeID, attendingclinicianID, and a few others.

    I needed to account for where each admitted patient was in the hospital, what board type they were, what level of ICU care they were receiving.  I needed to know the status of unoccupied stalls.....whether they had been cleaned and disinfected and were ready for occupancy or whether they were empty but not ready.

    I wanted all this to be web-based.  I wanted to use DNN because all the security and login, interfaces, etc. would be easily handled.   
    .
  2. Design your database structure.

    You can actually create new tables using IndooGrid.  I didn't do it that way.  I used MS Access 2007 on my local machine to design and relate all the tables.  It's a great tool for setting up tables and queries and gives you a visual layout of the application relationships.  You can test the results of your queries to see if you're getting back the data you expected.  In fact, I pretty much built the whole application as a stand-alone Access program in a pretty short time (few hours).   The goal, however, was to deploy it on the web in DNN.

    The devil is in the details.....Hint:  Prefix all your tables and views with a set of letters to keep them grouped when you do upsize them from Access to SQL.  There are a lot of tables in the DNN database, and if you don't do that you will be hunting all over to find your tables in the list.  All my tables started with vth_ (vth_xxxxx) to keep them grouped.  All my views were named vw_vth_xxxx.

    The devil is in the details.....Hint: Give every Access table a primary key that auto-increments.  e.g. ClientID, autonumber.  These keys will be used extensively to link tables for drop-down lists and creating views.

    The devil is in the details.....Hint:  Order the fields in your database to correspond to the column and field order you plan to use in your grid and forms.  While you can re-arrange the order later in IndooGrid, starting the the approximate order in the database will save you a lot of time.

    The devil is in the details.....Hint:  Don't use blank spaces in table or field names (even though Access doesn't seem to mind).  At some point trying to deal with blank characters will probably be a problem.

    The devil is in the details.....Hint:  Enter a few dummy records in each of the tables in the database so that you can test the output of queries.  This also gives you some content later to view formatting.  

    Here's the database structure I finally settled on.  

    It took more than one attempt to get the database structure 'right' and it's much easier to test in Access than SQL.  

    There were some features that worked fine in Access that I have not been able to resolve using IndooGrid on the web, so I had to go back and modify the database more than once to make 'work arounds'.  This includes the use of sub-forms ...easy in Access...impossible(?) in IndooGrid.  There may be a way to do a true one-to-many form in IndooGrid, but I haven't figured it out yet.   
    I also have not been able to use horizontally arranged radio buttons in the grid although these can be set up in forms. 
    .
     
  3. Upload the db structure to SQL server.

    Access2007 has a very useful upsizing tool that allows you to very easily transport the entire table structure to SQL.  You need to have access to the SQL server (permissions, login, password, connection string, etc.).  But, if you can connect, you can in one fell swoop upload and create all the tables incredibly easily.

    I have  SQL Server Management Console, so I could have created the tables manually, but the upsize was much easier.  I did use Management Console to view the results of the upsize to make sure everything was okay.

    I put all the new tables in the DNN database.  You can put them in a separate database.  But putting them in the DNN db  makes connection easier when using IndooGrid because the connection string is already established when you install the module on a page and you don't have to agonize with getting it right.

    The upsizing didn't upload the queries I had created in Access.  I had to create them in SQL manually using Management Console.   

    A 'select query' in Access is equivalent to a 'view' in SQL.  If you can get an Access query that returns the data you want, you can view the SQL statement in Access that's behind the query and cut and paste it into a 'Create View' dialog box in SQL and create the view.  (If you are using Management Console, expand the directory tree for the database and right-click on Views (right below Tables).  That will bring up a query box, paste the SQL statement from Access, and 'Run' the query.)

    The devil is in the details.....Hint:  There are some differences in the SQL syntax generated in Access and that used in MS SQL.  You may need to manually edit some of the syntax for the 'views' you need to create in SQL.  One issue in views that use concatanated fields is that Access uses the & (ampersand) to join fields whereas SQL uses the + (plus) sign.  You will need to replace the & with + but the rest of the syntax was similar.  

    For example:  The Access generated SQL will read something like:
    [firstname] & '  ' & [lastname] wherease SQL will read [firstname] + '  ' + [lastname]

    So, at some point I had all the tables and views created that I needed for my web-based application.   

      
  4. Install IndooGrid as a DNN module.

    IndooGrid installs like a regular DNN module.  Log on as host, go to Module Definitions under the Host tab, click on 'add new module' at the bottom of the page, browse to the IndooGrid zip file and click 'install'.   The manual that comes with IndooGrid is pretty good for the installation.  After installation IndooGrid appears on the module dropdown list in the DNN control panel.   Add the module to a page by selecting it from the list, editing the name or pane locations as desired and clicking 'add'.
    .
  5. Set up IndooGrid modules for the functions you need.

    One thing I needed to do was set up basic add(insert)/edit/delete functions to maintain my look-up tables.   These are pretty much the simplest application of an IndooGrid module and a good  place to start.  You specify a table, set a few gridView  (tabular) settings, set a few gridEdit (form) settings and you can display a list of records and insert new records or select individual records to edit or delete.  You have a functional add/edit/delete setup for the table in minutes.   You can customize a lot of things (column headers, widths, validation, etc.) on the grids and forms, but you don't have to do it a this point.  You can always go back and edit the module later.

    Of course it wasn't that easy the first few times through!!!!!!!   The learning curve with IndooGrid early on is that there are dozens of setting and choices on the first page and you have no idea which ones are necessary to set or how.

    My advice is start simple.  PIck one of your look-up tables that you want to have add/edit/delete functionality and start there.

    IndooGrid has proven to be a great tool with incredible capabilities and I think it's been worth the investment to learn how to use it.  I've been at it for about 2 weeks and counting!!!!   

    I'm working on an image-based tutorial to help you slog through the elemenary settings when you get started.  So, check back.

    Jill