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.