Assuming you've created the four tables as show above, your 'Desktop Studio' main menu should look similar to this:

If we wanted to, we could simply view and edit our data by opening and closing the tables.  For example, let’s open the TBLLISTINGS table:

As you can see, I’ve added some sample data into the tblListings table.

Obviously, finding and viewing our data this way is less than ideal.  It’s very cumbersome and non-intuitive.  So let’s get cracking and we’ll start designing our abcDB REALTOR application.

First of all, let’s click on the FORMS tab, and we’ll create a NEW form.  When prompted for a table, choose the tblListings table and proceed to create a MANUAL form.

Let’s start this application by creating a search form.  Let’s add the following controls to the form:
(Note:  The screenshot shows the form after it's been saved and re-opened.  Your screen may look 'slightly' different as you're adding controls)

CONTROL TYPE                  Caption                                  TAG

LABEL                                  Address                                  Label1
LABEL                                  Street                                     Label2
LABEL                                  City                                       Label3
LABEL                                  Max. Size                               Label4
LABEL                                  Max. Price                             Label5
TEXTBOX                                                                          ADDRESS
TEXTBOX                                                                          STREET
TEXTBOX                                                                          SIZE
TEXTBOX                                                                          PRICE
DROPDOWN                                                                      CITY
COMMAND                          Search                                    butAddress
COMMAND                          Search                                    butStreet
COMMAND                          Search                                    butCity
COMMAND                          Search                                    butSize
COMMAND                          Search                                    butPrice
GRID                                     n/a                                         GRID1

The idea here is this:

We want to allow our user to enter either an Address, or a Street, or a City, or a Maximum Size, or a maximum Price.  By tapping the SEARCH button below the appropriate field, we’ll then fill the GRID control with houses that match that criteria.  For example, our user could enter MARSHALL in the City field, and then tap the CITY SEARCH button, and all houses that are located in MARSHALL would be displayed in the GRID.

Let’s select our city SEARCH button.  In the MACRO property we’ll add the following code: (NOTE:  The following command must be entered all ON ONE LINE.)

GRIDREFRESH(“SELECT * FROM tblListings WHERE City = ‘”~CITY~”’”// // //”---“// // //)

It’s easy to understand what this command will do.  When the user enters a city name and taps SEARCH, abcDB will refresh the GRID’s contents with all records where the CITY equals the city entered by the user. 

Let’s try our form out.  SAVE it using the name ‘frmSearch’ and then open it.

After entering the city of MARSHALL and tapping SEARCH, here are the results.  Pretty neat eh?

Now things are starting to get exciting!  We can start imagining all the possibilities!  But let’s complete building this form before we put the ‘cart before the horse!’

Let’s edit our form again, and we’ll finish the code behind the other 4 SEARCH buttons.

Add the following code behind the MACRO properties of the following command buttons: (Make sure every GRIDREFRESH command is on ONE LINE!  Do not add carriage returns!)

butAddress:
GRIDREFRESH(“SELECT ID,Price,Style, address,StreetAve,StreetType,City FROM tblListings WHERE Address LIKE ‘%”~ADDRESS~”%’”// // //”---“// // //)

Notice that we’ve used the LIKE operator in this SQL.  This allows our user to enter a ‘partial’ address.  Basically this gives us a ‘wildcard’ search.

butStreet
GRIDREFRESH(“SELECT ID,Price,Style, address,StreetAve,StreetType,City FROM tblListings WHERE StreetAve LIKE ‘%”~STREET~”%’”// // //”---“// // //)

butSize
GRIDREFRESH(“SELECT ID,Price,Style, address,StreetAve,StreetType,City FROM tblListings WHERE houseSize <= ”~SIZE~// // //”---“// // //)

butPrice
GRIDREFRESH(“SELECT ID,Price,Style, address,StreetAve,StreetType,City FROM tblListings WHERE Price <= ”~PRICE~// // //”---“// // //)

When you’re done, save your form and test it.  You should find that you can enter all the various search criteria and find results.  AWESOME!

If you get any errors, review all the steps to this point to ensure you didn’t make any typographical errors.

You notice however, that we really don’t need the NAVIGATION MENU at the top of the form.  We also really don’t need the horizontal scrollbar OR the vertical scrollbar.  So let’s get rid of these by editing the form again and changing the following properties for the FORM by tapping the FORM PROPERTIES button .

Caption:   SEARCH
NavigationMenu:  NO
Horizontal_Scrollbar:   NO
Vertical_Scrollbar:     NO

After saving these changes, our form should look like this.  Much better!

If you want, you can change the background and foreground colors of the form and of all the controls.  Since everyone has their own opinions of what ‘looks good’, I’ll leave the colors up to you.

Now, after we’ve searched for our houses, wouldn’t it be nice to be able to choose one from the list and then view it in more detail?

No problemo!  Let’s edit our form again, and I’ll meet you at the next step….

Ok, so what we want to do is this:

1.      Choose a house from the list.

2.      Once chosen, we want to display a BUTTON that displays a caption like “Show House#2”

3.      When we click this button, we’d like to open a new form which will show the details of this home.

How are we going to do this?  Well, let’s start by adding a button as shown here.  Set it’s VISIBLE property to FALSE so that we won’t normally be able to see it.  Give it a TAG name of butZOOM

Now, the key to being able to view the details of a particular home is the ID field.  Remember, every home will have a unique ID.  And in our GRID control, the ID field is the first field displayed, so we can use the GRIDCOL() function to find out what it is!

In the ON CURRENT event of the GRID control, enter the following code:

SETMEM(HouseID//GRIDCOL(0))
CAPTION(butZOOM//”Show House#”<!HOUSEID!>)
VISIBLE(butZOOM//TRUE)

Whenever a user taps into a new row (or cell) in the GRID, the ON CURRENT event fires.  As you can see, a memory variable named HouseID is then given the value that is contained in the first column of the currently selected grid row. 

Then, we change the caption of the butZOOM button so that it explains which house number you’re going to view.

Finally, we make the butZOOM button VISIBLE, so that the user can see it.

After you save your form, you can see it works pretty slick!  But our SHOW HOUSE button still doesn’t do anything.  

Before we do anything further, let’s create a NEW form based on the tblListings table.  When prompted, please call this form:    frmListings

This form is going to allow us to view the details of each home, as well as view the photograph of the property.  In addition to this, we’re going to allow the user to ‘sketch’ diagrams, take notes, etc.. by incorporating a SCRIBBLE control. 

We’re going to build it so that initially the photograph is displayed, but if the user wants to display the ‘sketch’, he/she only has to tap the SKETCH button, which will then ‘hide’ the photo, and ‘show’ the ‘sketch.  To view the photo again, the user will just tap the button again.

Add textboxes and labels so that it resembles our screenshot here.  Make sure that your textboxes are all bound to the appropriate fields, by selecting the proper field in the BOUND_FIELD property of each control.

You can also see that we’ve added a SCRIBBLE control and an IMAGE control.  Overlap them slightly so that you can still select each one if needed.  Set the VISIBLE property of the SCRIBBLE control to NO, since we want it to initially be invisible.

Set the VISIBLE property for the CLEAR and SAVE buttons to NO. 

Set the BOUND_FIELD property of the Scribble control to the SKETCH field.

Set the FORM’s properties to this:

Background Color:  Green
Caption:   HOME DETAIL
NavigationMenu:  YES
Horizontal_Scrollbar:   YES
Vertical_Scrollbar:     YES

Let’s just test our frmListings form to make sure it works.  As long as your table contains data, you should see something similar to this screenshot. 

Don’t proceed until you’ve gotten this form to work.

Now, let’s make our SKETCH button work (NOTE:  The scribble and image controls do not function in abcDB 'Desktop Studio', however they do function on your actual PocketPC device). 

Here’s how:

In the MACRO event of the SKETCH button, enter this code:

TEST(<!IMAGE!>//=//1)
CAPTION(butSketch//Picture)
VISIBLE(imgListings//false)
VISIBLE(scrListings//true)
VISIBLE(butClear//true)
VISIBLE(butSave//true)

SETMEM(IMAGE//0)
EXIT()
ENDTEST()

CAPTION(butSketch//Sketch)
VISIBLE(ImgListings//true)
VISIBLE(scrListings//false)
SETMEM(IMAGE//1)
VISIBLE(butclear//false)
VISIBLE(butsave//false)

In the ON OPEN event of the FORM, enter this code:

SETMEM(IMAGE//1)

In the CLEAR button’s MACRO event, enter this:

SCRIBCLEAR()

And in the SAVE button’s MACRO event, enter this:

SCRIBSAVE()

In the FORM’s ON CURRENT event, enter this code which will automatically attempt to load the corresponding image for the currently select house ID (ie. 1.bmp, 2.bmp, etc…):

IMAGE(LOAD//"/My Documents/” {ID} “.BMP")

Now let’s save our FORM and test it out…

And here we are!

NOTE:  If you wish to test the 'sketch' and 'image' controls, you will need to 'import' the database to your PocketPC.  Assuming you do, you'll see something similiar to this screenshot.

As long as you have a BMP file in the My Documents folder that is named the same as the currently selected ID field value, then you should see a picture!

Let’s draw a quick sketch by tapping the SKETCH button.

If we’ve done everything correctly, we should be able to SAVE our sketch by tapping the SAVE button.  The next time we view this record, our sketch will still be here for us to view.

Of course, if we make a mistake, we can always tap the CLEAR button, to clear the scribble control so we can start over!

Cool isn’t it?

Ok, now that our frmListings form is working fine, let’s go back and edit our frmSearch form.  Here we are again.

Remember, our next task is to make the butZOOM button work. 

We’ve already programmed our GRID’s ON CURRENT event to capture the ID of the house we’ve tapped on, and store it in a memory variable named HouseID.

Now we want our butZOOM button to open our new frmListings form and have it display the record that matches our HouseID value.  Here’s how:

In the MACRO event of our butZOOM button, enter the following:

FORMOPEN("frmListings"//"select * from tblListings where ID =” <!HouseID!>//1//"---"////)

For details on the FORMOPEN syntax, refer to the Appendix.

Now let’s save our form and test it again.

Let’s search for all homes that are under $100,000.

After selecting a home in the list, let’s tap the SHOW HOUSE button, and see if our program is working.

Move to next step.

Yahoo!!!!  It’s working!

I’ll admit that we could certainly spend some time to make our application ‘look’ more aesthetically pleasing, but the fact remains that it’s working fine.

There are many things that we could do.  For example, on this screen, we need to make the SEARCH button take us back to our frmSEARCH form.  This is easily accomplished.  Can you think of how?

All we need to do is add this code in the buttons MACRO event:

FORMOPEN("frmSEARCH"////1//"---"////)

We could also add the ability to display photographs of our houses.  How can we do that?

Move to the next step.

Well, let’s start by editing our frmSEARCH form and adding an IMAGE control.  We’ll resize and move our butZOOM button so that there’s room.  Set the IMAGE controls STRETCH property to YES.

Now, let’s add the following line of code to the bottom of our GRID’s ON CURRENT event:

IMAGE(LOAD//"/My Documents/” <!HOUSEID!> “.BMP")

Now, whenever we tap in the grid, we’ll set our memory variable to the value of the selected House ID, we’ll make our butZOOM button visible, AND now we’ll load the image of the house into the image control.

Note:  In order for this to work, it is assumed that you have copied your pictures (*.bmp files) to the /My Documents folder on your device, and that they are named according to the ID# of the house. (ie. 1.bmp, 2.bmp, 3.bmp, etc..)

Let’s save our form, copy a valid *.BMP file to our /My Documents folder and test it out.

*NOTE:  This screenshot is based on the application running on your device.  Images and Scribbles will not function in the Desktop Studio.

WOW!  Now we’re accomplishing some pretty cool stuff.

Obviously, we would probably want to tidy up our forms and make them more user-friendly, but this should give us a pretty good idea of what’s possible.  And granted, I’m not the best photographer in the world, but I guess that’s why I’m not being published in ‘National Geographic’!

But we’re not finished yet.

Why don’t we modify our frmListings form so that we can easily add new listings into our database?

Let’s EDIT our frmListings form and go to the next step.

Let’s give our CITY textbox a TAG name of CITY.

In the CITY Label’s ON CLICK event, add this code:
(Notice how we’re taking advantage of the abcDB QUERY BUILDER to easily create the necessary SQL statement?)

POWERPICK(CITY//"SELECT tblCities.city FROM tblCities order by tblCities.city ASC“//”---“//)

Also note that our SQL statement in the PowerPick command will return our city list in alphabetical order.  This will make it much easier for our users to find the city they want, especially when there may be dozens to choose from.

It would also be a good idea to change the CITY label’s caption to “City …”  You’ll see why in a moment.

Save your form, and let’s test it out.

Let’s tap the ADD NEW menu button, and we should be presented with a brand new ‘blank’ record. 

Now, let’s try tapping the ‘City …’ label.  See what happens?  We’re presented with a POWERPICK, a new feature of abcDB.  PowerPicks allow your users to quickly and easily choose values from a list.

I’ll choose MARWAYNE from the list and tap OK.

By the way, see why we changed the label’s caption to “City …”?  The three dots help our users to recognize that this is no ordinary label control.  They’ll begin to realize that whenever they see a label with 3 dots in our applications that it means there’s a PowerPick waiting to be used!

Move to next step …

Notice how the PowerPick has now filled in our CITY textbox with the value I chose. 

We could EASILY add a PowerPick to every other field on our form!  For example, our STYLE, STATE, STREETYPE, BEDROOMS, and BATHROOMS fields would be prime candidates for using a PowerPick since they are all fields that obtain their values from a predefined list of items. 

PowerPick’s save your user time, and they make data entry more accurate with fewer spelling errors.

Hmmm … what else can we add to our application?  How about a main menu?  This should be EASY for professional programmers like you and me!  Let’s create a NEW form (not based on a table), and proceed to the next step.   When prompted, call this new form ...  frmMain

Ok, I couldn’t resist.  I threw in some color on this screen too.  Of course, we could get real fancy if we wanted, and display a picture, or even add some sound using the PLAYSOUND() command.  But I’ll leave that up to you.

Let’s add a LABEL with the caption “abcDB REALTOR”, and also add 3 COMMAND buttons, just as is shown here.

In the SEARCH button’s MACRO event, enter this:

FORMOPEN("frmSEARCH"// //1//"---"////)

In the BROWSE button’s MACRO event, enter this:

FORMOPEN("frmListings"// //1//"---"////)

In the EXIT button’s MACRO event, enter this:

EXITAPP()

Also, since we would like this MAIN MENU to automatically appear whenever this database is opened, let’s set the AUTOFORM property for this form to YES.

(NOTE:  AutoForm does not function in Desktop Studio.  It only function on the PocketPC device)

Well, that brings us to the end of our abcDB REALTOR application.  There’s still LOTS that we could add.  Just a few suggestions might be:

1.      Forms to add new Cities, States, House Styles, etc…

2.      We could add forms which display more details relating to the REALTOR who listed each particular home.

3.      If we had abcDB ‘Wireless’, we could easily convert this application so that it retrieved the data from a remote computer! 

The SKY’S THE LIMIT!

Hopefully, if nothing else, you’ve learned some more techniques that can be used to develop your own abcDB applications.