Another email I received asked that I demonstrate "How to locate and change a record in a table not connected to the current form."

I will demonstrate this using the Product and Vendor tables in the AlphaSports database. The Product table stores data about the individual products that AlphaSports sells. The Vendor table contains data about the vendors who supply the products to AlphaSports. Using the Product form, I will place a button that will play a new script I am going to name "JustPlaying2".

The following graphic shows the Product form with the button added.



The button, "Big Ugly Button", has as its onPush event code:

Script_play("JustPlaying2")

Create a new script named "JustPlaying2". Place the following syntax in the new Xbasic script:

vVendor = table.current().Vendor



vContact = ui_get_text("Contact Name","Enter your contact's name")



t = table.open("vendor")

t.index_primary_put("Vendor_Id")

vRecordNumber = t.fetch_find(vVendor)



t.change_begin()

t.Contact_name = vcontact

t.change_end()

t.close()

vVendor = table.current().Vendor

This script is very similar to the ones we built last month. The difference of course, is that this script will change a record in a table that is not associated with the current form. We will find the correct record by getting the Vendor ID value from the Vendor field in the Product table. The Vendor ID is a unique value that identifies a particular vendor. The first line of the script does just that, retrieving the Vendor ID value from the vendor field and places it in the variable, vVendor, to be used later in the script.

vContact = ui_get_text("Contact Name","Enter your contact's name")

I am going to write a value into the Contact_Name field of the Vendor table. Rather than just assign a value, I will use the ui_get_text() function to ask the user for a name. The name that the user types in is placed in the vContact variable.

t = table.open("vendor")

t.index_primary_put("Vendor_Id")

vRecordNumber = t.fetch_find(vVendor)

We use the t.fetch_find() command to go to the correct vendor record. The fetch_find() command uses the vendor Id we retrieved from the Products table along with the Vendor_Id index. You will notice that I store the returned record number in the vRecordNumber variable. If a matching record is not found, then a negative value is returned. You should use this variable to check and see if a valid record was found if this was a real, in production, script.

t.change_begin()

t.Contact_name = vcontact

This line assigns the value returned via the ui_get_text() command to the contact_name field in a memory buffer. It is important to note that at this time, nothing has been changed in the record. The values are held in memory until the t.change_end() command is received, telling Alpha Five that it is ok to save the values to the actual record.

t.change_end()

This line ends the change operation and saves any changes to the record. The t.change_end() command will accept a true or false flag as an argument. For instance, t.change_end(.f.) will abort the change. If no argument is supplied to the command, the default true, .t., logical value is assumed.

t.close()

The table is closed.

A few ending words about this script. It is purposely kept simple to make clear the procedures and flow of the logic. However, there are a number of assumptions that are made. They are:

• There will always be a Vender ID in the current Product record's vendor field.
• The user will always supply a name in the ui_get_text() dialog.
• The vendor table will always exist.
• The vendor table will always have an index named 'Vendor_Id' that contains key values from the vendor_Id field.
• The vendor_id will always be unique.
• There will always be a matching Vendor record for every vendor ID found in the products table.

frm = form.load("Vendor Information") 

t = frm:tables:vendor.this

t.fetch_goto(vRecordNumber)

frm.resynch()

frm.show()

frm.activate()

Below is the screen prompting for a contact name.



And here we see the results, the Vendor Information form is brought up showing the change the script made.



These lines of Xbasic first load the Vendor Information form into memory, but does not display it. Then after getting a table pointer to the underlying Vendor table, we use a t.fetch_goto() command to jump to the appropriate record. Remember we had the record number stored in the vRecordNumber variable earlier in the script. Now we resynch the form with the underlying table, then display the form on the screen, and finally, make sure that the form appears on top by giving it focus with the frm.activate() command.