Skip to the content Back to Top

I've been using this technique a lot lately, so why not share it?

When linking a textbase to another, it's best to link on a unique ID: something that won't change over time. The plus side is that the link from primary to secondary textbase will not be easily broken. This is just good sense and a long-time database design golden rule. (What if you linked to a Name field and someone changed their name?)

The down side is that an ID is not people-friendly. When the time comes to link out to the secondary textbase, a number like "123" means a lot less to human eyeballs than "Smith, John". Inmagic gives you the ability to browse the available items to link to, but a list of numbers is next to useless. How do you know that "123" is the Record ID for "Smith, John"? You don't.

To retain the strength of the link-on-ID and the user-friendliness of browse-by-name, we call upon JAVASCRIPT. Hurrah. Here's what I do in edit forms. I'll use an example based on a library sign-out page: there is a Catalog, a Borrowers textbase and a Loans textbase. The Loans textbase is primary, but links to Catalog and Borrowers textbases as secondary textbases. I want to create a loan record that links to the Borrower via Borrower ID, but I don't want the user to have to know the Borrower's ID. I want them to search by borrower name to make the link.

Fig. 1. Link is made on Borrower ID, but user can search by Borrower name or barcode and the script will make the link.

In this case I give the user the opportunity to search either by name or barcode. If the script finds a single match, it automatically populates the Borrower ID field. If it finds multiple matches, it displays the top 5 so the user can either re-search with a more specific search or copy-and-paste the ID themselves.

Fig. 2. User has searched for "doe" and found 2 matches.

Here is the script which performs the search on borrower name. I have defined an object of type "Borrrower" to encapsulate all the information I need about a borrower and this function returns an array of these Borrower objects.

 

function GetBorrowersByName(name) //returns array of Borrower

{

    var names = name.replace("\r", " ").split(" ");

    var rs;

    var fldBorrowerID, fldBarcode, fldLastName, fldFirstName, fldMiddleName, fldPatronType, fldPatronStatus;

    var strNames = names.join(" / ");

    var output = [];

 

    rs = Application.newRecordset(userTextbase, Application.activeTextbase.path, userPassword);

    if (rs != null)

    {

        rs.Open("Find (LastName ct " + strNames + ") or (FirstName ct " + strNames + ") or (MiddleName ct " + strNames + ")");

        DebugLog("Find (LastName ct " + strNames + ") or (FirstName ct " + strNames + ") or (MiddleName ct " + strNames + ")");

        if (rs.Errors.Count > 0)

        {

            DebugLog("GetBorrowersByName: " + rs.Errors(0).Description);

        }

        else if (rs.RecordCount > 0)

        {

            rs.MoveFirst();

            while (!rs.EOF)

            {

                fldBorrowerID = rs.Fields("RecordID");

                fldBarcode = rs.Fields("Barcode");

                fldLastName = rs.Fields("LastName");

                fldFirstName = rs.Fields("FirstName");

                fldMiddleName = rs.Fields("MiddleName");

                fldPatronType = rs.Fields("PatronType");

                fldPatronStatus = rs.Fields("PatronStatus");

                output[output.length] = new Borrower(fldBorrowerID.Value, fldBarcode.Value, fldFirstName.Value, fldMiddleName.Value, fldLastName.Value, fldPatronType.Value, fldPatronStatus.Value);

 

                rs.MoveNext();

            }

        }

        rs.Close();

    }

    return output;       

}

 

 Fig. 3. GetBorrowersByName function, searches Borrower textbase and returns an array of Borrower objects

Here is the script which contains the logic to decide what to do with the results. Again, if single match is found, the Borrower ID is populated automatically, and if multiple matches are found, the top 5 are shown to the user in the gray background box (called boxBorrowerInfo in the script).

 

function ShowBorrowerResults(results)

{   

    if (results.length < 1)

    {

        selectedBorrower = null;

        Form.boxes("boxBorrowerID").content = "";

        Form.boxes("boxBorrowerInfo").content = "No match found";

    }

    else if (results.length == 1)

    {

        selectedBorrower = results[0];

        Form.boxes("boxBorrowerID").content = results[0].BorrowerID;

        Command.refreshWindow();

    }

    else if (results.length > 1)

    {

        selectedBorrower = null;

        Form.boxes("boxBorrowerID").content = "";

        Form.boxes("boxBorrowerInfo").content = "Matches:";

        for (i = 0; i < results.length;i++)

        {

            if (Form.boxes("boxBorrowerInfo").content != "")

            {

                Form.boxes("boxBorrowerInfo").content += "\n";   

            }

            Form.boxes("boxBorrowerInfo").content += results[i].BorrowerID + "  " + results[i].Barcode + "  " + results[i].LastName + ", " + results[i].FirstName + " " + results[i].MiddleName;

        }

    }       

}

 

In the single-match-found scenario, the Borrower ID is populated with the found Borrower ID, and the window is refreshed so that any secondary link information shows up.

Fig. 4. A single match has been found for "notdoe". The Borrower ID has been populated, and the secondary fields (name and borrower type) based on the link are showing.

And there you have it. Get to know the Inmagic recordset object and you can query any textbase from any other textbase.

Brilliant quick reference site that finds programming documentation as you type (HTML, CSS, Javascript, more). Just needs C# and VB.NET. C'mon somebody. Anybody. Not me though. I'm a busy man. http://www.quickref.org/

Ohhhhhhhahaaaawwwww. Last week a hard drive crashed on me. I do nightly backups, so I lost only a day's work, but all the projects I had in the source control library now point to an extinct directory. No problem, I'll just set the working directory to the new restored location. Oh no. No no no. You won't get away that easy. You must PAY. So now, every time I want to fetch me a project, I have to fight a source control guardian with the physical and argumentative capacity of a beefy ape and the intellectual capacity of a squashed plum. Fwah!

To run a canned query when a web page is loaded, instead of a user having to manually begin the query by clicking a link:

<head> <script type="text/javascript"> window.location.replace("http://localhost/dbtw-wpd/exec/dbtwpub.dll ?AC=QBE_QUERY&TN=cars&RF=Full+information&QY=Findall"); </script> </head>

Obviously the query itself will change depending on your needs, but the key is to set the window.location to the canned query url.

We bought Peter Blum's validators and more package just recently. Everyone says it's better than sex for ASP.NET edit pages. Then again, that's the kind of thing your average .NET developer WOULD say. Unremediated social misfits, the lot. But I got more than I bargained for when I started working with them today. The validation controls, not the misfit developers. First clue was opening the user's guide, and seeing it was 235 pages. And, oh wait, that was only one of the user guides. There were a couple more, plus a tutorials PDF. I think it all added up to about 500 pages. Of instructions. Gawd. I slumped into instant depression. It's an in-depth package. It's going to do everything, and I'm going to be able to retire early with Peter Blum's Validation and More running the whole show for me. And now it's the end of the day, and I can say I have implemented exactly one (1) validation control that makes Title a mandatory field. I don't know whether to shoot myself or have a party.

Categories

Let Us Help You!

We're Librarians - We Love to Help People