Introducing Clarion FileManager (Part 1)

Clarion can generate the forms you need to add, change and delete data manually, but what do you do when you want to manipulate records using code? We have briefly looked at importing CSV data, but that isn't all we need to do. Introducing FileManager: look in the help file. It's under "ABC Library Reference" and it has numerous useful properties and methods.

First, the naming convention. Say we want to work with the table called "Customer". The we use "Access:Customer.Method()" to refer to the table and its records, and CUS:FieldName to refer to individual fields in the current record. Because Clarion documentation often refers to "files" when it means "tables", "FileManager" should really be named "TableManager", but this is an historical artefact from DOS days, where tables were stored in separate files.
Note: This article has been updated to leave out the .Init and .Kill methods, and to simplify the SET command. If you first read the article in 2022 then you should see the changes now. The .Init and .Kill methods are only useful in standalone handwrittten programs, and are handled automatically in a Wizard-generated program like the Learning Clarion application. Thanks to Carl Barnes at ClarionHub for pointing out my (newbie) mistakes.

Common Table Methods

• UseFile notifies other ABC objects that a file is getting ready to be used. Do this before you use Open.
• *Open tells the FileManager the calling procedure is using the file, then OPENs the file if it is not already open. The Open method handles any errors that occur while opening the file, including creating the file and rebuilding keys if necessary.
• ClearKey clears or (re)initializes the specified range of key component fields.
• SET initializes sequential processing of a FILE or VIEW.
• *Next gets the next record in sequence. The Next method handles any errors, except end of file, that occur while getting the record.
• *Fetch Gets a specific record by its key value and handles any errors.
• PrimeRecord prepares a record for adding to the managed table.
• *Insert adds a new record to the file, making sure the record is valid, and automatically incrementing key values as required.
• *Update changes (rewrites) the current record.
• DeleteRecord deletes a record from the table.
• Close tells the FileManager the calling procedure is done with the table, then closes the table if no other procedure is using it.

We will use some examples to make this a bit less overwhelming. Methods marked with an asterisk have a (better) version, by preceding the method name with "Try" to get TryOpen instead of Open, etc.

One thing I have noticed with .TPS files is they never seem to have a record number 1. The lowest record number is always 2. Clearly this is not the case with all table structures in all database types, but it's a useful conceptual point to bear in mind when we loop through records using FileManager's .Next method. It's why the loop always starts with .Next, even if we have already moved to the correct record.

Exercise 1: Some Test Code

First, let's return to the "LCLesson" application. If you don't have one, you can download it and extract the files.

Click on the "Application" menu, and choose "New Procedure".

Type in the Procedure Name, "MyTestCode".

From the "Defaults" tab, select "Source Procedure". This will create a standalone procedure in its own module.

Give the procedure a Description, and click on the "Declare Globally" box. Then click on the green check box ("Save and Close") and Save your work.

Notice how the procedure is not "connected" to anything yet. That means that no other code or form calls the procedure. To fix this, we are going to add the procedure to the application menu.

Click on the "Main (Frame)" procedure and then click on the "Window" button to go into the window designer.

Right-click on the menu control, and choose "Edit menu" from the drop-down menu.

Click on the "&Browse" menu item (MENU3) and change it to " &Data ", with a space before the ampersand and another after the word data. I think it looks nicer this way, but it's only a personal preference.

Click on the "BrowseOrders" line, and add a Separator. Then add a New Item. The text for this item is "&Test Code" and the message is "Run my test code". Then click on the "Actions ..." link.

Change the action to "Call a Procedure" and choose the "MyTestCode" procedure from the drop-down list. Check the "Initiate Thread" box, click "OK", click on the green "Save and Close" buttons (twice) to return to the main procedure screen. Notice how the "MyTestCode" is linked to the "Main (Frame)" window.

Click on "MyTestCode" and then on the "Embeditor" button. Now we can get the procedure to do something.

Scroll down a bit and type in "MESSAGE('My Test Code')" as shown. Click on the green "Save and Close" button and save your work.

Go to "Global Extensions" and make sure that the "UltimateDebug" extension is showing. Click on it and click on "Ultimate Debug Generation Options" and check that the two global information options are selected. Then click OK (twice) and save your work. Click on the shortcut on the taskbar to run DebugView++, and then on Clarion's "Start without Debugger (Builds Application)" button.

The DebugView++ capture window will probably show something like this. Now go to the "Data" menu item and choose "Test Code".

So in addition to the application displaying the message, the DebugView++ window is showing the fact that we entered ("[OpenClose]") the "MyTestCode" procedure. Click on the "OK" button and close the LCLesson application. Let's return to the "MyTestCode" procedure in the Embeditor.

Change the "MESSAGE" command to the two lines shown here:

    0{PROP:StatusText,1} = 'My Test Code'
    ud.Debug('---- My Test Code runs here ----')

Now, when you run the application and choose the "Test Code" menu item, "My Test Code" will display in the application's status bar, and you will get a message in DebugView++. Something like this:

Exercise 2: Reading The Customer Table Records

Open LCLesson and use the Embeditor to edit "MyTestCode".

Define a variable "i" of type "long" at the top of the procedure as shown. Then click on the "Data/Tables" tab, followed by the "Other Files" folder, and click on the "Add" button (shown in the blue circle above). Choose the "Customer" table and click "Select".
Copy and paste the following code to replace the original two lines of code:

    Access:Customer.UseFile()
    Access:Customer.Open()
    ud.Debug(' Access:Customer.Open()')
    i = 0  ! Record counter
    LOOP UNTIL Access:Customer.Next() <> Level:Benign ! CUS loop
        !// Process each customer record
        i += 1 ! Count the records
        ud.Debug(' Customer record ' & i)
        0{PROP:StatusText,1} = 'Customer record ' & i
    END ! CUS loop
    ud.Debug(' Loop ends. There are ' & i & ' records')
    0{PROP:StatusText,1} = 'There are ' & i & ' records'
    Access:Customer.Close()
    ud.Debug(' Access:Customer.Close()')

Once you have pasted the code, highlight it and press Ctrl-i to get the indenting to work properly. Warning! This code has no error checking. It is not production quality yet.

Will this code do anything? Let's run it to find out. Click on the green "Save and Close" button, and save your work. Click on the "Start without Debugger (Builds Application)" button to build the application and then try running the test code.

It didn't work as expected. Why? Because opening the table isn't enough. We have to navigate to the correct record before the loop begins. We need an index to do that, preferably the primary key.

Make a few blank lines above the "i = 0 ! Counter" line, and type in "Access:Customer.ClearKey()". Then drag the name of the primary key ("KEYCUSTNUMBER") from the Data/Tables pad on the left, and drop it between the two brackets after "ClearKey". Add in a comment "! Go to the top of the primary key" after the ClearKey statement. Put a "ud.Debug" line as shown below.

Type in SET() on a new line. Drag the primary key name to the brackets after SET(). Or you can copy/paste it in instead of dragging. The SET command says which index we will search/fetch on, and the sort order in which we will access the records. Usually, these are both the same. Follow this with a comment "! Sets the processing order" and put a "ud.Debug" statement on the next line. Your code should look like this:

Use the green "Save and Close" button, save your work, and start the application as usual.

This time it did what we expected it to: it looped through all 8 customer records. So let's read and show the data.

Change the code as shown. Use the green "Save and Close" button, save your work, and start the application again.

This time it is showing the customer number and name. Close the application and close Clarion.

Exercise 3: Adding New Records

Before you continue, go to the LCLesson folder and make a copy of the Customer.tps file because we are going to add in some rubbish data to the current one. Then open the LCLesson application, and use the Embeditor to open MyTestCode.

Add in two new variable definitions in the data section as shown here. Next, replace the existing code with this new code (using copy and paste).

    Access:Customer.UseFile()
    Access:Customer.Open()
    Access:Customer.ClearKey(CUS:KEYCUSTNUMBER) ! Go to the top of PK
    SET(CUS:KEYCUSTNUMBER) ! sets the processing order
    i = 0 ! Record counter
    LOOP UNTIL Access:Customer.Next() <> Level:Benign ! CUS loop
        !// Process each customer record
        i += 1 ! Count the records
        ud.Debug(' Customer: ' & CUS:CustNumber |
            & ' Company: ' & clip(CUS:Company))
        0{PROP:StatusText,1} = 'Customer record ' & i
    END ! CUS loop
    ud.Debug(' Loop ends. There are ' & i & ' records')
    !// List of US state codes, including Guam and Puerto Rico
    strStates = '..AK.AL.AR.AZ.CA.CO.CT.DE.FL.GA.GM.HI.IA.ID.IL.' |
       & 'IN.KS.KY.LA.MA.MD.ME.MI.MN.MO.MS.MT.NC.ND.NE.NH.NJ.NM.' |
       & 'NV.NY.OH.OK.OR.PA.PR.RI.SC.SD.TN.TX.UT.VA.VT.WA.WI.WV.WY.'
    !// Add in some more records
    LOOP i = 9 TO RANDOM(150,250) ! i
        CUS:CustNumber = i
        IF Access:Customer.Fetch(CUS:KEYCUSTNUMBER) = Level:Benign THEN ! Fetch
            ud.Debug(' Fetched Customer: ' & CUS:CustNumber |
                & ' Company: ' & clip(CUS:Company))
            0{PROP:StatusText,1} = 'Found customer ' & CUS:CustNumber
        ELSE ! Fetch
            !// Record not found, so add it in
            Access:Customer.PrimeRecord() ! Customer no is autonumber
            CUS:Company = 'Company ' & i
            CUS:FirstName = 'First ' & i
            CUS:LastName = 'Last ' & i
            n = RANDOM(1,52) ! Get a random state number
            CUS:State = SUB(strStates,n*3,2) ! State Code
            CUS:City = SUB(strStates,n*3,2) & ' City'
            CUS:ZIPcode = n * 1000 + i ! Made up "Zip" code
            Access:Customer.Insert()   ! Add in the customer record
            ud.Debug(' Added Customer: ' & CUS:CustNumber |
                & ' Company: ' & clip(CUS:Company))
            0{PROP:StatusText,1} = 'Added customer ' & CUS:CustNumber
        END ! Fetch
    END ! i
    0{PROP:StatusText,1} = 'Test code completed.'
    Access:Customer.Close()

Once you have pasted it, highlight it and press Ctrl-i to get the indenting correct. It should look like this:

Use the green "Save and Close" button, save your work, and start the application.

Run the test code, and look at the debug messages. Run it again and note how the first loop shows the newly added records, before attempting to add more. Then look at the data in the Customer browse screen. Notice that the address is missing. Close the application and return to the MyTestCode procedure.

Let's add in a few extra lines of code as shown, so you can see how the Update method is used. Save your work, and run the test code to see what it does. Close the application when done.

Exercise 4: Performing Lookups

Now we are going to introduce a second table: State. Go back into the Embeditor and add in the State table, so you have "Customer" and "State" listed in "Other Files". Below the "strState = " assignment lines, add in the following code:

    !// State table lookups
    Access:State.UseFile()
    Access:State.Open()    
    Access:State.ClearKey(STA:KEYSTATE) ! Top of the primary key
    SET(STA:KEYSTATE) ! sets the search order    
    !// Add in and update some more records

At the bottom of the code, remember to add the method to close the state table again:

    Access:State.Close()

Now we need to add in the code to perform a lookup, based on the State code of each customer:

   IF Access:Customer.Fetch(CUS:KEYCUSTNUMBER) = Level:Benign THEN ! Fetch
       ud.Debug(' Fetched Customer: ' & CUS:CustNumber |
           & ' State ' & clip(CUS:State))
       STA:State = CUS:State ! This is the state code we want to fetch
       IF Access:State.Fetch(STA:KEYSTATE) = Level:Benign THEN ! STA
           CUS:Address = 'Here in ' & clip(STA:StateName)
       ELSE ! STA
           CUS:Address = 'Not Supplied'
       END ! STA 
       Access:Customer.Update()   ! Update changes to the customer record
       ud.Debug(' Updated Customer: ' & CUS:CustNumber)
   ELSE ! Fetch

The total code should now look something like this:

Save your work, start the application and try the test code. You should get a result similar to this:

Because there is no "Access:State.Update()" command, none of the records in the "State" table have been altered, even though we did an assignment "STA:State = CUS:State" to prime the key value for the Fetch.
Note: There is no reason why we can't do the same kind of lookup in the "Insert" part of the code, but we would need to assign the value of "CUS:State" (and also "STA:State") or the fetch will not work as intended. Remember: this code doesn't have any error checking yet!

Let's continue with Exercise 5 in the next post (below).

[ Introducing Clarion FileManager (Part 1) ]
[ Introducing Clarion FileManager (Part 2) ]
[ How to keep your useful CapeSoft accessories up to date ]
[ Adding Extensions to Clarion ]
[ Learning Clarion (Part 1) ]
[ Backing up your Clarion projects ]
[ Getting Started with Clarion (Part 1) ]
[ Introduction to Clarion 11 ]
[ Installing Clarion 11 on Windows 10 ]
[ Clarion Fixer (Part 1): Using the App ]
[ Clarion Fixer (Part 2): The Code ]
[ Change Mode vs View Mode ]
[ Clarion Build Events ]

[ Clarion Resources and Products ]

Introducing Clarion FileManager (Part 2)

This is a continuation from Part 1.

Exercise 5: Try Some Error Checking

So far we have simply assumed everything is working, but we haven't tested for any error conditions. You shouldn't do that in real programs. In particular, we should use TryOpen, TryNext, TryNext, TryInsert and TryUpdate.

Work with pairs

One way to make this easier is to work with method pairs while you are writing the code. The first is the TryOpen and Close pair:

if Access:Table.TryOpen() = Level:Benign then ! Open Table
    !// Table has been opened
    
else ! Open Table
    ud.Debug(' Open Table failed')
end ! Open Table
Access:Table.Close()

Next, add the ClearKey and SET pairs.

if Access:Table.TryOpen() = Level:Benign then ! Open Table
    !// Table has been opened
    Access:Table.ClearKey(TBL:KeyName) ! Top of the primary key
    SET(TBL:KeyName) ! Set the processing order
else ! Open Table
    ud.Debug(' Open Table failed')
end ! Open Table
Access:Table.Close()

After that, you will probably want a loop to read several records. So add in the LOOP UNTIL TryNext and END pair.

if Access:Table.TryOpen() = Level:Benign then ! Open Table
    !// Table has been opened
    Access:Table.ClearKey(TBL:KeyName) ! Top of the primary key
    SET(TBL:KeyName) ! Set the processing order
    LOOP UNTIL Access:Table.TryNext() <> Level:Benign ! Loop Table
    
    END ! Loop Table
else ! Open Table
    ud.Debug(' Open Table failed')
end ! Open Table
Access:Table.Close()

Notice how we comment the "if ... then", "else" and "end" statements to keep track of which "end" statement belongs to which "if" statement. Also, the same applies to the "LOOP" and "END" statements. Don't only rely on Ctrl-i to do the indenting for you. Use it to check your own comments and see if they line up properly. If not, check your code.
Also, you can avoid unforeseen bugs by ensuring that you put an "else" whenever you have an "if". Get into the habit of writing the "else" and the "end" whenever you write the "if", and put the comments at the end of each line, before you worry about what happens immediately after the "if".
With all of this in mind, open "MyTestCode" in the Embeditor and fix up the code. Note I have also copied the CUS:Address code into the insert block. It should end up something like this:

 Access:Customer.UseFile()
 if Access:Customer.TryOpen() = Level:Benign then ! Customer Open
     Access:Customer.Clearkey(CUS:KEYCUSTNUMBER) ! Top of the primary key
     SET(CUS:KEYCUSTNUMBER) ! sets the processing order
     i = 0 ! Record counter
     LOOP UNTIL Access:Customer.TryNext() <> Level:Benign ! CUS loop
         !// Process each customer record
         i += 1 ! Count the records
         ud.Debug(' Customer: ' & CUS:CustNumber |
             & ' Company: ' & clip(CUS:Company))
         0{PROP:StatusText,1} = 'Customer record ' & i
     END ! CUS loop
     ud.Debug(' Loop ends. There are ' & i & ' records')
     0{PROP:StatusText,1} = 'There are ' & i & ' records'
     !// List of US state codes, including Guam and Puerto Rico
     strStates = '..AK.AL.AR.AZ.CA.CO.CT.DE.FL.GA.GM.HI.IA.ID.IL.' |
       & 'IN.KS.KY.LA.MA.MD.ME.MI.MN.MO.MS.MT.NC.ND.NE.NH.NJ.NM.' |
       & 'NV.NY.OH.OK.OR.PA.PR.RI.SC.SD.TN.TX.UT.VA.VT.WA.WI.WV.WY.'
     !// State table lookups    
     Access:State.UseFile()
     if Access:State.TryOpen() = Level:Benign then ! State Open
         Access:State.Clearkey(STA:KEYSTATE) ! Top of the primary key
         SET(STA:KEYSTATE) ! sets the search order 
         !   
         !// Add in and update some more records
         LOOP i = 9 TO RANDOM(150,250) ! i
             CUS:CustNumber = i
             IF Access:Customer.TryFetch(CUS:KEYCUSTNUMBER) |
                = Level:Benign THEN ! Fetch
                 ud.Debug(' Fetched Customer: ' & CUS:CustNumber |
                     & ' State ' & clip(CUS:State))
                 STA:State = CUS:State ! This is the state code we want
                 IF Access:State.TryFetch(STA:KEYSTATE)|
                    = Level:Benign THEN ! STA
                     CUS:Address = 'Here in ' & clip(STA:StateName)
                 ELSE ! STA
                     CUS:Address = 'Not Supplied'
                 END ! STA 
                 if Access:Customer.TryUpdate() = Level:Benign then
                     ! Update changes to the customer record
                     ud.Debug(' Updated Customer: ' & CUS:CustNumber)
                     0{PROP:StatusText,1} = 'Updated customer ' |
                         & CUS:CustNumber
                 else ! Update
                     ud.Debug(' Failed to update Customer: ' |
                         & CUS:CustNumber)
                     0{PROP:StatusText,1} = 'Failed to update customer ' |
                         & CUS:CustNumber                         
                 end ! Update
             ELSE ! Fetch
                 !// Record not found, so add it in
                 Access:Customer.PrimeRecord() ! Cust number is autonumber
                 CUS:Company = 'Company ' & i
                 CUS:FirstName = 'First ' & i
                 CUS:LastName = 'Last ' & i
                 n = RANDOM(1,52) ! Get a random state number
                 CUS:State = SUB(strStates,n*3,2) ! State Code
                 CUS:City = SUB(strStates,n*3,2) & ' City'
                 CUS:ZIPcode = n * 1000 + i ! Made up "Zip" code
                 STA:State = CUS:State ! This is the state code we want
                 IF Access:State.TryFetch(STA:KEYSTATE)|
                    = Level:Benign THEN ! STA
                     CUS:Address = 'Here in ' & clip(STA:StateName)
                 ELSE ! STA
                     CUS:Address = 'Not Supplied'
                 END ! STA                  
                 if Access:Customer.TryInsert() = Level:Benign then
                     ! Add in the customer record
                     ud.Debug(' Added Customer: ' & CUS:CustNumber |
                         & ' Company: ' & clip(CUS:Company))
                     0{PROP:StatusText,1} = 'Added customer ' |
                         & CUS:CustNumber
                 else ! Insert
                     ud.Debug(' Failed to add Customer: ' |
                         & CUS:CustNumber & ' Company: ' |
                         & clip(CUS:Company))
                     0{PROP:StatusText,1} = 'Failed to add customer ' |
                         & CUS:CustNumber
                 end ! Insert
             END ! Fetch        
         END ! i
     else ! State Open
         ud.Debug(' Unable to open the State table')            
     end ! State Open
 else ! Customer Open
     ud.Debug(' Unable to open the Customer table')
 end ! Customer Open
 0{PROP:StatusText,1} = 'Test code completed.'
 Access:Customer.Close()
 Access:State.Close()

Now run the application and see if the debug messages match what you expected to see.

You may decide that it is important to notify the user of some error conditions. I have only used debug messages in this example for consistency, not for any other reason.
Warning: If you write

if Access:Customer.TryUpdate() then

and omit the

= level:Benign

part you will tear your hair out for days trying to figure out why the update "isn't working" when in fact it is working correctly but the IF statement is the wrong way around. I speak from personal experience.

Exercise 6: Making Changes Across Multiple Records

In SQL it takes a single UPDATE command with some WHERE clauses to update a bunch of records. With FileManager we don't always have that luxury. Say, for example, we want to delete all customers in the state of California. One way to do it is simply to loop through all the records and every time we find a California state code, delete the record and move on. It's not particularly efficient, but it will get the job done.

Open LCLesson, select the "BrowseCustomers" form and open it with the Window designer.

Drag a button control from the Toolbox and place it on the form as shown.

Change the Text to "Delete CA", the Use property to "?btnDeleteCA", the TextColour to Red, and the Message property to "Delete CA Customers". Then select the button, and double-click it.

This should open the Embed points for the button. Click on "Accepted", press the "Insert" button, choose "Source" and "Select".

Type in "ud.Debug(' Delete CA')" and press the green "Save and Close" button three times. Save your work. Now click on the "Embeditor" button.

Click on the "Next Filled Embed" button until you find the "ud.Debug" statement. Now we can add some suitable code:

   ud.Debug(' >Delete CA')
   Access:Customer.UseFile()
   if Access:Customer.TryOpen() = Level:Benign then ! Customer Open
       Access:Customer.ClearKey(CUS:KEYCUSTNUMBER)
       SET(CUS:KEYCUSTNUMBER)
       LOOP UNTIL Access:Customer.TryNext() <> Level:Benign ! Cust Loop
           ?btnDeleteCA{PROP:Text} = clip(CUS:CustNumber) ! Display Cust No
           DISPLAY() ! Update the screen display
           !// Delete stuff goes here
       END ! Customer Loop
   end ! Customer Open
   Access:Customer.Close()
   ud.Debug(' <Delete CA')

Use the green "Save and Close" button to exit the Embeditor. Save your work. Start the application and test the button.

We haven't actually deleted anything, but we have made sure that the looping works. Use the "Fast Forward" button to go to the last record and check that the record number matches the number on the button text.
So let's return to the code and make it a bit faster, and to count how many records were deleted. The variable i# is an "implicit LONG" variable used here.

   ud.Debug(' >Delete CA')
   Access:Customer.UseFile()
   if Access:Customer.TryOpen() = Level:Benign then ! Customer Open
       Access:Customer.ClearKey(CUS:KEYCUSTNUMBER)
       SET(CUS:KEYCUSTNUMBER)
       i# = 0
       LOOP UNTIL Access:Customer.TryNext() <> Level:Benign ! Cust Loop
           if clip(CUS:State) <> 'CA' then CYCLE . ! Try next record
           ?btnDeleteCA{PROP:Text} = clip(CUS:CustNumber) ! Display Cust #
           DISPLAY() ! Update the screen display
           !// Delete without asking
           if Access:Customer.DeleteRecord(False) |
               = Level:Benign then ! Delete
               ud.Debug(' Record deleted ' & CUS:CustNumber)
               i# += 1 ! Count the deleted record
           else ! Delete
               ud.Debug(' Record NOT deleted: ' & CUS:CustNumber)
           end ! Delete
       END ! Cust Loop
   end ! Customer Open
   Access:Customer.Close()
   ?btnDeleteCA{PROP:Text} = 'Deleted ' & i# ! No of records deleted      
   ThisWindow.Reset(1) ! Update display to remove the deleted records
   ud.Debug(' <Delete CA')

Some lines need further explanation: the purple line is a one-line if statement because it ends with a dot. The CYCLE command forces execution to go to the top of the LOOP, so everything below the purple line is ignored, down to the END of the loop. It prevents non-'CA' records from being deleted.

This is another way to write the test: look for the qualifying record and process it inside the "if" block. It's a matter of preference and legibility. You choose.
Click on the green "Save and Close" button, save your work and start the application. Let's see if the new code actually works as intended.

How to check? Click on the "by ZIP Code" tab and see if you can see any California addresses.

Did you notice these lines?

   if Access:Customer.DeleteRecord(False) = Level:Benign then ! Delete
       ud.Debug(' Record deleted ' & CUS:CustNumber)
       i# += 1 ! Count the deleted record
   else ! Delete
       ud.Debug(' Record NOT deleted: ' & CUS:CustNumber)
   end ! Delete

How could we display the Customer number from a deleted record? (Line 2) Because the record buffer did not disappear when the physical record was deleted. It only changes once we move to the next record.

Exercise 7: Indexes Make Record Access Easier

Close the LCLesson application and open the LCLesson dictionary instead. Consider the Customer table.

Notice that the "State" code is not part of an index. So let's add an index for it. This will enable us to jump to any record based on its state code.

Right-click on the "Keys" folder and choose "Add Key"

Give the key the label "KEYSTATECODE" and a description, and then add the field "State" as the only field in the key. Click "OK"

Move the new key to the bottom of the list, and then click on the "Save All" button to save these changes. Now we need to get the data file itself to change.

Right-click on the Customer table and select "Browse Table".

Choose the "Convert and backup" option, and click "OK".

Click the top dropdown box to check that the new index/key is present in the data file. Close the window, and close the dictionary.
From the Start Menu, open the LCLesson Application. We need to introduce some changes to the Customer browse form. Select it, and click on the "Window" button to open the Window Designer.

Use the "List Box Format" command to change the width property of "First Name" and "Last Name" to "40" instead of "80". Then adjust the width of the screen to 640, and adjust the SHEET and the LIST controls as shown.

Now click on the SHEET to select it, and then right-click to add a new Tab.

In the properties on the right is a "Tabs" property, with the word "(Collection) ..." after it. Click on the ellipsis button and choose "tab4" in the Control Collection Editor". Change its text to "by State Code" and click "OK".

Change to the new "by State Code" tab, and then drag and drop the "State" field from the Data/Tables window and place it below the State column as shown. You can delete the label. Then mark the State control as "Flat" and "Read Only" as shown. This will display the state code for whatever record is selected in the browse form. Click on the green "Save and Close" button and save your work.

Keep the "BrowseCustomers" form selected, and click on the "Extensions" button. This will display the extensions that apply to the form.

First, we are going to delete the top extension, the one that allows the form to be resized. This will prevent the buttons moving around on the form. Then double-click on the line that reads "Browse on Customer ()".

Change to the "Conditional Behavior" tab, and click on the "Properties" button. This shows how the sort behaviour based on which tab is selected, works. Highlight and copy the "Condition" expression and click "Cancel".

Now click "Insert", and paste the clipboard to the "Condition" field. Change it to end with a "4" instead of a "2". Click on the ellipsis on the "Key to use" field and select the "KEYSTATECODE" key. Click "OK".

Make sure the choices are in numerical order, for sanity's sake. Click "OK". Click on the green "Save and Close" button and save your work. Start the application and try out the changes in the Customer Browse form.

Adjust the screen to fit the data. Notice how the scroll bar is using up some of the data space, so let's make a note to reduce the size of the "City" column just a bit (change the width from "80" to "70"). Notice how the data is sorted by the field on which the key is based. Close the app and return to Clarion.

Open the Window Designer and return to the "by State Code" tab. Add a button labeled "?btnDeleteThis" with text "Delete this State:" and Red TextColor. Change the adjacent "State" control to have red text too, and line up their middles for neatness.

Double-click on the button and click on the "Accepted" event. Thenc click on the "Insert" button and choose "Source". Type in "ud.Debug(' btnDeleteThis ' & CUS:State)" and click on the green "Save and Close" buttons (three times) and save your work.
Note: always use the "Insert" button shown. Do not be tempted to use the "Source" button to get into the code and add in stuff. It will cause the Clarion IDE to crash and you will lose some of your work. Never enter the Embeditor directly from the Window Designer.

Now that we have left the Window Designer, we can go to the code using the Embeditor. Click on the "Next Filled Embed" button a few times until you get to the "btnDeleteThis" section. The code is not going to be the same as the previous button, but we will borrow pieces from it.

First, move up to the top of the "ThisWindow.TakeAccepted" procedure, and define two variables as shown. Then move down to the "btnDeleteCA" code block and change the three "i#" entries to "i" because we have explicitly defined the i variable as LONG. Now let's write the new code:

  strStateCode = clip(CUS:State) ! Remember which state to delete
  ud.Debug(' ?btnDeleteThis ' & strStateCode)
  Access:Customer.UseFile()
  if Access:Customer.TryOpen() = Level:Benign then ! Customer Open
      Access:Customer.ClearKey(CUS:KEYSTATECODE) ! Top of the index
      CUS:State = strStateCode ! Jump to this state
      ! SET using this index, starting at this index value
      SET(CUS:KEYSTATECODE,CUS:KEYSTATECODE)            
      i = 0
      LOOP UNTIL Access:Customer.TryNext() <> Level:Benign ! Cust Loop
          if clip(CUS:State) = strStateCode then ! State
              ! Display Customer Number
              ?btnDeleteThis{PROP:Text} = clip(CUS:CustNumber) 
              DISPLAY() ! Update the screen display
              !// Delete without asking
              if Access:Customer.DeleteRecord(False) |
                  = Level:Benign then ! Delete
                  ud.Debug(' Record deleted ' & CUS:CustNumber)
                  i += 1 ! Count the deleted record
              else ! Delete
                  ud.Debug(' Record NOT deleted: ' & CUS:CustNumber)
                  MESSAGE('Unable to delete ' & CUS:Company)
              end ! Delete
          else ! State
              !// We have moved on to the next state code
              ud.Debug(' Break at ' & CUS:State)
              BREAK ! All done
          end ! State
      END ! Cust Loop
  end ! Customer Open
  Access:Customer.Close()
  ?btnDeleteThis{PROP:Text} = 'Deleted ' & i ! No of records deleted       
  ThisWindow.Reset(1) ! Update display to remove the deleted records
  ud.Debug(' <btnDeleteThis ' & strStateCode)  

Note the SET command has the same index listed twice, to specify the search and sort order, and to allow you to specify the starting point of the loop. From the help file: "SET (key,key) specifies keyed sequence processing and positions to the first ... record which contains values matching the values in the component fields of the key. Both key parameters must be the same."
I always think of this technique as the "hop, skip and jump" method. We start with the index being reset. Then we use the assignment statement to tell the index which record to "hop" to. While we loop through the records, we are skipping from one valid record to the next. When we run out of valid records, we use the BREAK command to "jump" out of the loop because there are no more valid records. The index is doing most of the work, instead of having to rummage through all the records in the entire table.

Close the Embeditor, save your work, and Start the application. Go to a state near the end of the alphabet (I chose Vermont) and click the "Delete this State:" button. Watch in the debugger how the program skips through the records.

Exercise 8: Be Careful Not To Delete Parent Records

I assumed that since there was a relationship between Customers and Orders (and it has a "restrict" setting for deleting records) that "Customer" records with existing child records in the "Orders" table, would not be deleted. I was wrong. In any case, it is always better to check for these things explicity than to expect them to happen implicitly.

Open the Embeditor for the "BrowseCustomers" form, and go to the "Data/Tables" panel. Click on the "Other Files" folder, and the "Add" button, and select the "Order" table. This makes it easy to see the Index and Field names, and to drag and drop them into the code. So let's modify the code for the "delete this" button:

  strStateCode = clip(CUS:State) ! Remember which state to delete
  ud.Debug(' ?btnDeleteThis ' & strStateCode)
  Access:Customer.UseFile()
  if Access:Order.TryOpen() = Level:Benign then ! Order Open
    if Access:Customer.TryOpen() = Level:Benign then ! Customer Open
      Access:Order.ClearKey(ORD:KEYCUSTNUMBER) ! Top of the index
      Access:Customer.ClearKey(CUS:KEYSTATECODE) ! Top of the index
      CUS:State = strStateCode ! Jump to this state
      SET(CUS:KEYSTATECODE,CUS:KEYSTATECODE)  
      SET(ORD:KEYCUSTNUMBER) ! Use this index to find stuff 
      i = 0
      LOOP UNTIL Access:Customer.TryNext() <> Level:Benign ! Cust Loop
        if clip(CUS:State) = strStateCode then ! State
            ?btnDeleteThis{PROP:Text} = clip(CUS:CustNumber) ! Display Cust #
            DISPLAY() ! Update the screen display
            !// Delete without asking
            ! Look for this Customer in the Orders Table
            ORD:CustNumber = CUS:CustNumber 
            IF Access:Order.TryFetch(ORD:KEYCUSTNUMBER) |
              <> Level:Benign THEN ! Orders
                !// No orders found. Safe to delete empty customer
                if Access:Customer.DeleteRecord(False) |
                  = Level:Benign then ! Delete
                    ud.Debug(' Record deleted ' & CUS:CustNumber)
                    i += 1 ! Count the deleted record
                else ! Delete
                    ud.Debug(' Record NOT deleted: ' & CUS:CustNumber)
                    MESSAGE('Unable to delete ' & CUS:Company)
                end ! Delete
            ELSE ! Orders
                ud.Debug(' Orders exist. Record NOT deleted: ' & CUS:CustNumber)
                MESSAGE('Unable to delete ' & clip(CUS:Company) | 
                    & ' because of existing orders')    
            END ! Orders
        else ! State
            !// We have moved on to the next state code
            ud.Debug(' Break at ' & CUS:State)
            BREAK ! All done
        end ! State
      END ! Cust Loop
    end ! Customer Open
  end ! Order Open
  Access:Customer.Close()
  Access:Order.Close()
  ?btnDeleteThis{PROP:Text} = 'Deleted ' & i ! No of records deleted       
  ThisWindow.Reset(1) ! Update display to remove the deleted records
  ud.Debug(' <btnDeleteThis ' & strStateCode)        

Now that we have the required checks in place, "Save and Close" the Embeditor, save your work, and start the application.

Change to the "by State Code" tab, and choose South Carolina. Click on the "Delete this State" button. The first record in the Customer table has an invoice, so we get the correct notification. Same with the third customer.

In the end, we deleted the empty customers, and two SC customers remain. We would have to delete their orders before we should delete these customers.

Essential Reading

I learnt most of what is covered in these articles from "Section 2: Accessing Data Tables" from Bruce Johnson's book, "Programming in Clarion's ABC". You really need to invest $49 in this book. I was also assisted by some very helpful people at ClarionHub, who kindly guided me through my mistakes and misunderstandings.

[ Introducing Clarion FileManager (Part 2) ]
[ Introducing Clarion FileManager (Part 1) ]
[ How to keep your useful CapeSoft accessories up to date ]
[ Adding Extensions to Clarion ]
[ Learning Clarion (Part 1) ]
[ Backing up your Clarion projects ]
[ Getting Started with Clarion (Part 2) ]
[ Getting Started with Clarion (Part 1) ]
[ Introduction to Clarion 11 ]
[ Installing Clarion 11 on Windows 10 ]

[ Clarion Resources and Products ]