- **Static Queries: **The ability to create a new table which is a procedurally filtered subset of another table. I.e., creating a table which is a view of another table. Filtered views is all well and good, but not useful for creating filtered look-up tables for other linked tables.
Example: I have a table of Instructors. I have a table of Students. I want to add a field “mentor” to Students, so that each student can have a mentor. But I want only Instructors who have been certified for more than one year to be selectable as mentors. Right now, I can manually create a new table called Experienced Instructors (extends Instructors) and manually add a record linking one-to-one to each instructor whose time as an instructor is greater than one year, and then link a student to one of these Experienced Instructor records, but I have to manually update this derivative table, manually checking each day for instructors who have passed the one year mark. If I could create a table which is a filtered derivative of the Instructors table–i.e., a true Query, filtering by time since certification, then I could link student records to records in that Query. Then only valid options would be available, and valid options would always be up to date.
- **Procedural Queries: **Even better would be a procedurally generated query as a datatype.
Explanation: I create a field of type Link To A Query. I select a Table to which to link, just like the current link to another table. But then I get an additional option, WHERE {select: field} {select: comparison} {key value}.
Example: In table Classes, field Classes:“Instructor” is type Link to A Query of table Instructors WHERE {Instructors:“Certification”} {is equal to} {Classes:“Subject”}
- Additional query options for (1) and (2) above, such as cross-joins.
Refer to feature request multi-table-queries/1799 by [Jyrki_Keisala]
