I maintain a db with multiple tables. Currently for aviation data I need to link tables to see the entire data picture. Each table has an ID which corresponds to each subject, example airport id in one table and runway info associated in a separate table.
Thanks
I would need some more details. Please take a look at the “Calculated fields, links between tables” help topic. In general, you should use the “vLookUp2” function in a calculated field.
It’s much like the standard spreadsheet vLookUp function - it simply searches one field in all records of another table for a specified value. After finding a record with that field value, it returns the value of any other field from that record.
For example, if you have the “airport” table describing airports (one airport per one record), you can refer and display the “airport” record fields in any other table as calculated field values.
Typically, both tables must have one linking field. As you mentioned, it can the “airport id”. It could be the very airport name as well but it’d make modifying names a bit troublesome.
When entering the ids in another tables, please use a drop-down list to simplify this.
I’ll work with the Vlookup2 in a calculated field. The mention of a list box is interesting, however with over 10,307 indivual airports that list box would be rather large. Thanks for the quick reply I do greatly appreciate it.
Perhaps all the more reason to use a drop-down list with the “AutoComple” option On and some longer (descriptive) ids to avoid typing errors. Anyway, if you have further questions, feel free to ask.
Is there an update for doing this in v. 11.7.6? I looked for the specified help file and did not see anything about a vlookup2. I’m trying to link the record numbers field for two tables, and then run a query (which appears to be another issue) so I can copy & paste results into a spreadsheet.
Thank you.
The function is now called: vLookUp_ex(). There is a group of functions with the “_ex” suffix in the “cross-table(…)” category that are used to link/process data between two or more different tables.
For example, the “orders” table in the sample.gsb file uses this function in calculated fields.
If I need to establish a “relationship” between two tables, I use the Drop Down List to create one. While GS-BASE is not a relational database product, it is easy to establish logical relationships that are reliable and easy to maintain via the use of the Drop Down list. The simplest and most common relationship between two tables is the one-to-many with one table, such as a Client Information table, containing unique records based upon (usually) an identifier field, and a Client Transaction table that contains one or more records that point back to the Client Information table. I have such a database that contains a Client table and a Sessions table (that relates to one or more meetings/sessions with a Client. The Client table contains, among other things, administrative information about the client. A Client ID is created with a formula that creates a text field of the first three characters of the clients last name – LN -concatenated with the customer’s five character ZIP (Postal) code (=left(LN,3) & ZIP). The ID could be a concatenation of anything, however. Objectively, the goal is to create something unique for each Client record. The first field of the Sessions table would also be called Client ID with a Drop Down created for it that uses the Client ID filed in the Client table. The Drop Down, then, establishes the link between the two tables. From the reporting standpoint, the Client table will contain the actual client list and the Sessions table contains those actions – monetary or actionable – related to the client. This system works very well, since it can be easily controlled. If a client’s information changes that impacts the ID, a simple Copy/Paste can update Sessions. In a large Client base a periodic dupe check on the ID field can identify the possibility of duplicates. However, if the ID formula is well thought out, the odds of duplication is very limited.