UDB Query Expressions (Simple)
Objects (or records) within Ultimate Database are linked via relationships and these relationships are either Direct or Indirect.
Direct relationships take the form of an ID on one record pointing to another.
So lets use an invoice as an example. An Invoice has an Object ID, an Invoice Number and three item lines on it. So these Invoice Line Objects would then hold the Object ID of the Invoice, not the Invoice number. This would probably be stored in "Parent ID A" as the Invoice is a parent of the item lines.
These Invoice Line Objects might also hold the ID of the Stock Item being sold. This might be held in "Child ID X" as the Stock Item is a child of the Invoice Line.
Direct relationships are fast and easy to use but they have their limitations. They can only link to one other object and there are a limited number of them for each list (A, X, Y and Z). This is fine for an Invoice line as an Invoice line can only belong to one Invoice and it is only responsible for one Stock Item. If you wanted to link an object to multiple objects such as linking a company to a list of product types that it sold, you would need an indirect relationship.
There are also a number of automatic system direct relationships. These are maintained by the system but can be accessed in the same way as the A, X, Y and Z relationships. See the list below.
Indirect relationships Link multiple Object to one another via relationship links. These relationship links are defined on the database and given an ID or Relationship Type ID. So the ID of a Relationship between a Company and it's Products might be 1234.
Queries are simply a way to get from one object to another via a relationship.
UDB uses the Current Object ID as the default starting point in a simple relationship. So if the current object is an Invoice Line and you want to access its Invoice Header that is referred by the Parent ID A, then the query is simply "A". This could be used in a UDB command to access the Invoice Number {OBJ_DATA(A, C1)}.
If you wanted to access the Stock Item that is referred to by the Child ID X, then the query is "X" or {OBJ_DATA(X, C1)}.
Indirect relationships are used in a very similar manner e.g.{OBJ_DATA(1234, C1)}. The problem is that they may often need to be used in a loop as there may be multiple linked objects to consider. As in the example above, a company might be linked to multiple products. For this, you could use the {FOR_EACH()} command. {FOR_EACH(1234)}{OBJ_DATA(0, C1)}<br />{END_FOR_EACH}. Within the FOR_EACH Loop, the current object (COID) becomes the object you have linked to, so the query is "0", indicating that the data is to be pulled from the current object rather than a related one. This code would display a list of products for the company separated by the HTML line break "<br />".
So the simplest query is effectively just "0", indicating the current Object.
You can then have "A", "X", "Y", "Z", indicating an object linked to the current object via direct relationships A, X, Y or Z.
There is a full list of these relationship codes below.
Lets say you had Invoice lines linked to the invoice header via direct relationship A, and your current object was the Invoice header and you wanted to access all the Invoice lines that were linked to it. You couldn't simply use "A" as that would refer to the object linked to the Invoice Header via Parent Relationship A.
What you are really asking is "Can I have all Objects in the Invoice Line List (9876) that point to this Invoice Header (the current object) via their Parent A relationship". While that is a bit of a mouth full in English, the simple query equivalent is "A.9876" (Give me everything in list 9876 that points to the current object via relationship A. The dot between the A and the List ID tells the system that this is effectively a reverse lookup.
You can also add [n] to the end of an expression to limit the number of rows returned to n.
List of direct simple relationship expressions:
| C | Children |
|---|---|
| P | Parent Object |
| A.list_id | Objects in the specified list linked to the current object via Parent A |
| A | Parent A Object |
| X.list_id | Objects in the specified list linked to the current object via Child X |
| X | Child X Object |
| Y.list_id | Objects in the specified list linked to the current object via Child Y |
| Y | Child Y Object |
| Z.list_id | Objects in the specified list linked to the current object via Child Z |
| Z | Child Z Object |
| S | Created User Child Object |
| U | Amended User Child Object |
| T | Object Child Comments |
| V.list_id | Objects in the specified list linked to the current object via Value 1 |
| V | Child Object referenced by Value 1 |
| W.list_id | Objects in the specified list linked to the current object via Value 2 |
| W | Child Object referenced by Value 2 |
| rel_id | Child Objects linked to via relationship |
| -rel_id | Parent Objects linked to via relationship |