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 ]