Alpha Software Newsletter: Reference Your Data via Table Pointers

To order Alpha Four or Alpha Five, click above to go directly to Alpha, or call 800.451.1018 or 781.229.4500



Friday, November 1, 2002		VOLUME 1 ISSUE 9

Contact the Editors:

Bill Warner: Compuniq@aol.com
Jim Chapman: jec@iowatelecom.net

HOME

TOPICS

News & Notes

Learn Alpha Five

Tips & Tricks

Tech Corner

Developer Spotlight

Xbasic CliniX

ARTICLES

Reference Your Data via Table Pointers

Reference Your Data via Table Pointers
by Jim Chapman

Reference Your Data via Table Pointers

Alpha Five is a database management program. It should come as no surprise that Xbasic, Alpha Five's built-in scripting language, is a powerful tool for referencing, accessing and manipulating your data. In the September issue I wrote about field objects on a form, and we took a quick look at how to reference field values by referencing a currently open form. This month I would like to share my favorite method for manipulating data, via a Table pointer variable.

In my opinion, this is the best and least confusing method for referencing and manipulating data via an Xbasic script for many situations. There are times when it makes sense to 'go through the form', but I prefer, if possible to go directly to the table. If we think about it, the form adds another layer between the Xbasic script and your data. Why not go direct!

A pointer variable is a special type of variable. It does not hold a value in the same sense as a character variable. Instead of 'holding' the value, it points to an object. In this article I am only going to talk about Table pointer variables, but virtually all objects within the context of Alpha Five, can have a pointer variable created to reference the object.

In essence, a pointer is an 'alias' you assign. Strictly speaking, this is not true. A pointer variable contains a reference (pointer) to the actual physical location of the object in the computers memory. It is NOT the object, it just points to the object. In use, and for all practical purposes, you can consider the pointer and the object it points to, as one and the same. And, as you determine the name of the pointer, it is in essence an alias assigned by you. Assume you have a table in your database called "Customers". The following Xbasic syntax creates a pointer to this table:

MyPointer = table.open("Customers")

Many times you will see a pointer named as simply 't' or 'tbl'. The name is up to you. I will pursue three different ways of accessing a table underlying a form using a table pointer variable:

Table.open() Table.get() and Table.current()

I will use the AlphaSports database that ships with Alpha Five version 5 to demonstrate these three commands, but any form with an underlying set with at least one one-to-many parent-child relationship will do.

If you open up the Invoice form your screen should resemble the following:

Except of course for the big ugly button I have placed on this form. My 'Just Playing' button has a single Xbasic command in its OnPush event:


Script_play("JustPlaying")

The reason I call a script from the button, rather then embed the Xbasic directly in the button's OnPush event, is because I can have the Xbasic script editor and the form opened together on my screen, allowing me to easily modify the script and test the results. If I had embedded the Xbasic commands in the button's OnPush event, I would have to put the form into design mode each time I wanted to modify the OnPush event code. In the following screen shot you will see that I have the form and the code editor up and running, which allows me to quickly change back and forth, testing the Xbasic syntax. Just remember that each time you modify the code you must save the script in order to make the new syntax available.

The Invoice form is based on the Invoice Set. Following is a screen shot showing the set structure. I am going to use the one-to-many relationship between the parent table, Invoice_Header, and the child table, Invoice_Items, to demonstrate these techniques.

If you want to follow along with me in Alpha Five version 3, 4, or 5, create a new Xbasic script and name it "JustPlaying" (no quote marks). Put the following Xbasic syntax in the script and press the 'Save' button.


t = table.current()

ui_msg_box("This is the Name of the Current Table:", t.name_get())

Leaving the code editor open, activate the Invoice form by clicking on it, and press the button you placed on the form. A message box comes up informing you that the current tables name is INVOICE_HEADER. It is pretty obvious that the table.current() method returns a pointer to the main or Parent table of a set. The table.current() method will also allow us to return a pointer to any of the tables underlying the form by using a 'slot number' argument. For instance, change the first line of our Xbasic script to read:


t = table.current(3)

Save the script, then press our button on the Invoice form and you will see that the name of the 3rd table in the set is INVOICE_ITEMS. As the Invoice set is composed of five tables, slot numbers 1 through 5 will return a table name from the set. In version 5, if you enter a slot number larger that the number of tables open in the current session, Alpha Five returns a table pointer to the built-in System table. In version 4, this will return the error message "invalid table handle". The table.current() method without a slot number, or with slot number 1 will return the parent table of a set, or in the case of a form built on a single table, it returns a pointer to the single table underlying the form.

The table.get() method is similar. Instead of using a slot number, you use the table's name. Try the following syntax to demonstrate this:


t = table.get("Invoice_header")

ui_msg_box("The Current Tables Name is:", t.name_get())

This of course returns a pointer to the INVOICE_HEADER table. Whether you use the table.current() or table.get() method is largely a matter of preference. In general, if I want to return a pointer to a table other than the parent table, I will use the table.get() method using the argument (the value placed between the ellipses of a method or function) "Invoice_Items" as in table.get("Invoice_Items"). This has more meaning to me than a slot number. Another consideration might be the potential for the set to be changed. Then a slot number may return a pointer to a table other than you intended. Also, you do not have to worry about how many instances of a table are open. Both the table.get() and table.current() methods act only on the table or tables open in the current session. If there is more than one instance of the same table opened in the same session, such as a table linked to itself, then when using the table.get() method, you would need to supply the alias, that Alpha Five supplies, for the second instance of the table. This is a numerical value following the table's name, such as INVOICE_HEADER1.

It is appropriate to stop here and contemplate what it means to have a table pointer to a table in the current session. Go to the Invoice form and advance several records. I will demonstrate this while on the 4th Invoice record. Delete the Xbasic in the script and replace it with the following:


t = table.get("Invoice_header")

ui_msg_box("The Current Record Number is:", str(t.recno()))

Press the 'Save' button and then press our button on the form. You will see the following on your screen.

It shows that the current INVOICE_HEADER table, underlying the form is on record number 4 (or what ever record you paged to). Now, while the Invoice form remains on the same record, change the script to use the table.open() method. All you have to do is change the 'get' to 'open' as the following syntax shows, and add a third line of Xbasic to the script:


t = table.open("Invoice_header")

ui_msg_box("The Current Record Number is:", str(t.recno()))

t.close()

(Although it is not strictly necessary to explicitly close a table, opened with the table.open() method, it is good form to do so. Alpha Five will automatically close any open table pointers when the script completes, ie: it cleans up after itself. Even though this is the case, if for some reason your script failed to complete, it could be leaving open instances of tables in memory. This is commonly called a memory leak. You do NOT want to try to close a table pointer returned with the table.get() or table.current() methods. These are instances of tables already opened BEFORE your script returns a pointer to them. They are the instances of tables underlying the current session, in our case, the opened 'Invoice' form.)

Save this script and press our button on the form. You are now informed that the record number is 1. The table.open() method opened a new entirely separate instance of the INVOICE_HEADER table. Lets explore this a little further. We are now going to take a look at the child table, INVOICE_ITEMS. Delete the syntax in the script and replace it with the following:


T = table.get("Invoice_items")

t.fetch_first()

ui_msg_box("The First Invoice Item is record number:", str(t.recno()))

t.fetch_last()

ui_msg_box("The Last Invoice Item is record number:", str(t.recno()))

Record number four of the Invoice form contains, on my system, three Invoice_Items records. The first being record number 10, the last being record number 12. Of course this may vary on your system.

Now modify this scripts' syntax by changing table.get("Invoice_Items") to table.open("Invoice_Items"), and add a table close statement. Your script should look like the following:


T = table.open("Invoice_items")

t.fetch_first()

ui_msg_box("The First Invoice Item is record number:", str(t.recno()))

t.fetch_last()

ui_msg_box("The Last Invoice Item is record number:", str(t.recno()))

t.close()

Save the script and run it by pushing our button. On my system I am shown that the first record in this instance of the INVOICE_ITEMS table is 1, and the last record is 106. You can see that the instance of a table, represented by the table pointer 't', returned by the table.get() and table.current() methods is constrained by the current set link. In other words, it will only contain valid records for the current parent record of the set.

Lets try something constructive. The INVOICE_ITEMS table contains five fields:

Invoice_number, a character field
Product_Id, a character field
Price, a numerical field
Quantity, a numerical field
Extension, a numerical field

Replace the syntax in our script with the following syntax:


t = table.get("invoice_items")

t.enter_begin()

	t.product_id = "Jims Stuff"

	t.price = 10.05

	t.quantity = 2

t.enter_end()

Now if you save this script and then run it by pressing our button, you will at first be disappointed as it will appear that nothing happened. Something did happen. But first I want to examine the script and explain a few things. You will notice that, although there are five fields in the table, I only populate three of them in this script. The Invoice_Id is the linking field between the parent record and the child records. As we are dealing with the current set, because we gained our table pointer via the table.get() method, Alpha Five will automatically populate the linking field of any new record, with the correct invoice number. This is important to note, do NOT try to populate a linking field of a child table when your pointer is gained via the table.current() or table.get() method. Alpha will take care of this for us. You notice that I also did not try to write a value to the Extension field. This is because the Extension field is defined in the table's field rules as a calculated field. As such Alpha Five will automatically calculate and populate this field based on the Price and Quantity fields.

Now, if you ran this script, it appears that nothing took place. If you scroll up or down in the INVOICE_ITEMS browse, you will see that a record was indeed added. The reason it didn't immediately appear is that we went 'behind the form' so to speak and wrote the record directly into the table, rather than going 'through' the form. To make this record instantly appear, it is a simply a matter of adding one more line to your Xbasic script. Add the following line to the end of the script if you are working with Alpha Five version 5:


parentform:browse1.refresh_browse()

If you are working with Alpha Five version 4.x, I have found the following syntax to be most effective, which will also work fine in version 5. Add it to the end of the script.


Parentform.resynch()

Now if you push our button, of course after saving the script, the form should refresh immediately and show the record that we added.

Before we go on, I want to address an important consideration. In its current form the script does not address the state of the table we are dealing with, in this case the Invoice_Items table. In Alpha Five, a table will be in one of three states or modes, view mode (no data entry is taking place), change mode (a change operation is taking place), or enter mode (a new record entry is taking place). The above code should work reliably. But whenever you are writing code in any language, the words 'should', 'probably', and 'almost always', should make you shudder. Never, never depend on 'almost'!

The reason I say the above code should work reliably is because you must press the button to run this script. This button is on the parentform, so the action of pressing the button takes focus away from the Invoice_items browse. This should almost guarantee that the INVOICE_ITEMS table is not in enter or change mode. If the table was in either enter or change mode when the above script is run, it would generate an error message. The error message is generated because we would be trying to put the table into enter mode, via the t.enter_begin() command, when it is already in a data entry mode.

You should always check what mode the table is in when you are using a table pointer gained via the table.get() or table.current() methods. I use a simple 'Case' statement to check and put the table into the correct mode. If, as in the above example, I wanted to put the table into a new record Enter mode, I would write a short routine like the following:


T = table.get("Invoice_Items")

Vmode = t.mode_get()

Select

	Case vMode = 1

		t.change_end()

		t.enter_begin()

	Case vMode = 2

		t.enter_end()

		t.enter_begin()

	Case else

		t.enter_begin()

End select



t.product_id = "Jims Stuff"

	t.price = 10.05

	t.quantity = 2

t.enter_end()



parentform.resynch()

The t.mode_get() command returns a numerical value that represents the mode of the table. '0' means no data entry is taking place, '1' means a change operation is already underway, and '2' means that a new record entry operation is under way. The above Case statement (Select…….Case……end Select) makes sure that any current data entry operation is closed out and a new data entry operation is begun for the purposes of this script.

Let me end this article by contrasting the above method, using the table.get() or table.current() method, with using the table.open() method. The script is very similar except for two points. As we are opening up a new instance of a table, rather than accessing the currently open instance of the table, we will have to supply the Invoice_Number to the new record. We will do this by first getting the Invoice_Number from the parent table, then explicitly put it into the new record. Here is the syntax to use to try out this method:


tparent = table.current()

vInvoice_Number = tparent.invoice_number

t = table.open("invoice_items")

t.enter_begin()

	t.invoice_number = vInvoice_Number

	t.product_id = "Jims Stuff 3"

	t.price = 10.05

	t.quantity = 2

t.enter_end()

t.close()



parentform.resynch()

This script differs in the following respects. As we are opening a new 'stand alone' instance of the table we have to explicitly enter in the linking value in the Invoice_number field, which means we have to retrieve this value, which I did in the first two lines. We also do not have to check for the table's mode. This script has opened up this instance of the table so we know that no other process has put the table into an entry mode. The last difference is that it is good form to explicitly close the table, hence the 't.close()' command.

A quick word about refreshing the browse to show the added record when using a table.open() method. Using the table.open() method means that we have gone to an entirely different instance of the table. It is not the same instance of the table that is underlying the Invoice form. Because of this I have seen situations where it has been slightly more difficult to refresh the browse. Specifically I have never seen this to be a problem except in situations where the browse has a specified sort order or filter. If you think about this, it makes sense that Alpha might not immediately recognize a new record, as it has already ran its sort and filter, then we go behind its back and sneak in a new record. If you run into a this situation, I have found that issuing a parentform:browse1.fetch_first() and or parentform:browse1.fetch_last() command will always force Alpha to include the record in the browse. For this reason, I will usually use the table.get() and table.current() methods if I want the records to immediately appear for the user.

I hope that this short tour of the table.open(), table.get(), and table.current() method has given you some insight into the power and ease of using Xbasic to retrieve, manipulate and write data. Xbasic is indeed an extremely powerful and extensive language. Not only that, it is fun!
[PRINTER FRIENDLY VERSION]