In the Globals table, define the fields illustrated here:
Recapping, the fields in Globals provide services to the search, and hold values only temporarily. This table itself contains only one record.
- cLayout is a calculation which enables the display of the name of each table's layout based on the relationships between Globals and the other tables. See beneath in Execution: Setting up the Relationships.
- gC (an abbreviation of 'global constant') holds a constant 'global' value of 1 whose purpose is to create the relationship with the field 'C' (an abbreviation of constant)found in each of the other tables, in which is auto-entered the value of 1, as specified in the Define Database>Fields module, as illustrated above.
- gIDCatcher 'catches' the ID of the found record and helps steer the user to the record they wish to view.
- gLayoutCatcher 'catches' the layout name of that same record and helps steer the user to the table in which it will be found.
- gSearch contains, temporarily, the text string to be searched on.
- Search contains, as demonstrated beneath, the text which initially populates gSearch.
Through Define Database>Fields, define the following fields in Contacts. As the only difference between its structure and that for the Friends table is the presence of the Organisation field, after setting this up, just copy this table and call the copy 'Friends', and then delete its Organisation field, if you wish.
I will explain only the purpose of those fields which require it whose definition is not clear from the illustration beneath.
The purpose of the search is to find a match between the text string in gSearch and any relevant field in Contacts, but we can make this easier if we construct a simple 'composite' field of those fields most likely to contain the result. Thus:
- cCompContact is that field, a 'text calculation' which contains the Organisation, NameFirst, NameLast and location fields in a single string.
- cIfMatchComposite is a calculation which returns a '1' if the PatternCount - that is, the text within the gSearch field - is matched within the target cCompContact field, a straightforward Boolean result.
- cIfMatchgIDCatcher performs a similar function, which we will come to later.
- IDcontact is a simple, auto-entered, unique serial number for each record.
- TableName auto-enters 'T3.Contacts' into every record in its table.
Do you need help with Mac? 
1
Emile - 13/07/06
OOoooo, this is a great technique. Is there any way you can provide a sample file download to better illustrate how it works? Thanks
» Report offensive content
2
ianj - 03/10/06
One quick comment about the table names. Using a "." character in a table name or a field name has the potential to cause untold grief if you need to web-enable the system or connect via ODBC/JDBC. It's generally a really bad idea.
ian
» Report offensive content
3
Ender - 25/11/06
I'm afraid I don't see the need for techniques like this that search across multiple tables. Tables should really be about different things. If you have two tables with similar types of things, then the tables should probably be combined (especially if they may need to be shown together in a common listing or report).
In this example, "Friends" and "Contacts" should really reside in the same table, with a Contact Type field used to distinguish between them. This structure would make this search and listing quite simple, with no additional tables or import scripts required.
» Report offensive content
4
yusuf alam - 24/01/07
I want to search the record from the existing table from the oracle database using visualbasic programe and it will display the result in to the text box
select * from student where st_roll =" ' text1.text ' "
» Report offensive content