Nested relation

Classic Servoy
1

Hello,

I have three tables like:

Order:

ID
ItemID
Order_Date

Item:

ID
Item_Name
ItemType_ID

ItemType:

ID
ItemType_Name

I would like to create a single form, based on rows in the Order table, to display the following fields:

OrderID, Date, Item_Name, ItemType_Name

I can easily insert the Item_Name field into the form by creating a relation order_to_item between Order and Item and using order_to_item.item_name as the dataProvider.

I am wondering if there is a way to insert itemtype.itemtype_name into the form using a nested relation? (i am imagining something along the lines of order_to_item.item_to_itemtype.name)

Is this possible?

2

Yes you can do exactly that. You can ‘stack’ relations like you described.
One way of doing this is the following.
Use a label and double click the text property. This will open a dialog where you can type %%order_to_item.item_to_itemtype.name%%.
Close this dialog and make sure to enable the displayTags property.

Another way would be by using a calculation.

Hope this helps.

3

Thanks

I have implemented this using a calculation/method since I want the value to appear in a field for consistancy with the other elements on the form. This worked great, but I ran into a problem:

I created a field and set the dataProvider to be a global variable. I created a new method that set the global variable to order_to_item.item_to_itemtype.name. I then attached that method to the onLoad event for the form.

That seems to work fine when the form is displayed by itself. However, it did not work when the form was used as part of a tabpanel. The method runs as expected, but the relationship order_to_item.item_to_itemtype.name returns ‘undefined’. Is it running in the wrong context?

4

I am having a similar problem- anyone out there have an answer, even if the answer is “it can’t be done”?

To be specific, here’s what I’m trying to do.

I’ve got a form that is based on table A, and that will display in Table View (locked). I want to include on each row of the Table View a value that is two relations away from table A.

I understand that I can get to that value by chaining relations, such as A_to_B.B_to_C.value, and indeed if I put that expression inside “%%” in a label, I get the desired result, but I would like to make this happen in a FIELD rather than a label, for two reasons:

1 - I’m hoping that that way I’ll get a column header for that column in my table view based on whatever I type in the “Text” property of the field, something I don’t get with a label, and

2 - It will match the look of the other fields in the table.

Using a field based on a global dataprovider obviously doesn’t work for my case, because it displays the same value for all rows.

Thanks

5

Guys, you can’t get it in a FIELD (without a calc) - only a label.

You CAN create a calc with this formula:

if(relation1)
{
return relation1.relation2.fieldname
}
else
{
return 'unknown'
}
6

So if I understand you correctly, you’re saying I can get it in a field with a calc. What I don’t get is what kind of field? If I try it in a field based on a global var, it won’t work in a table view because the global only contains one value for all rows.

7

CALC! CALC! :D

Not global, CALC.

Put the calc on the products table that points to product_type:

return products_to_product_type.fieldName

Then when you show this field on your form - all set. No problem. Will calc “on the fly” for each item.

8

Got it - thank you.

I had somehow gotten it into my head that calculations could only act on existing data coumns.

9

Hi Bob

Hope you are fine and deep into Servoy ,-)

Just to be sure what you are saying: If I want to access (display) any field further away than one relationship I need always an additional calc field? Except a label is sufficient. Is this going to be like this also in version 3, i. e. there are no plans to access a field via more than one relationship directly? To me this sounds like quite some overhead resulting in many calc fields just to access a field. It would be nice if one could access a field just the same as a label via more than one relationship (nested relationships).

Best regards, Robert

bcusick:
Guys, you can’t get it in a FIELD (without a calc) - only a label.

You CAN create a calc with this formula:

if(relation1)

{
return relation1.relation2.fieldname
}
else
{
return ‘unknown’
}

10

The thing about a label is - you’re not going to try to modify it.

Why doesn’t this work for you? I’m trying to understand why you want to place a FIELD that’s multiple files away.

Also - how do you propose we handle the UI for that? It can be very confusing - no?

11

Hi Bob

bcusick:
The thing about a label is - you’re not going to try to modify it.

As far as I understand you, a label can’t be modified, as the name label would suggest?

bcusick:
Why doesn’t this work for you? I’m trying to understand why you want to place a FIELD that’s multiple files away.

I did not mean muliple files, only multiple tables away, for example when displaying a field on a table via the join entity, i. e. when there is a relationship 1:m:1 (for example a resolved m:n relationship).[/quote]

bcusick:
Also - how do you propose we handle the UI for that? It can be very confusing - no?

No, no, even FileMaker can (since Version 7) display any field from any table (in a given file) on a form. One just selects the table and all fields appear. This is a standard need, isn’t it?

But what I am saying is I just would like to place a field from any table on a form without being forced to create a calc first, because if this is necessary (as it seems to be right now), one has many calc fields just to display a field from another table, which makes a solution very complicated. Don’t you agree?

Thank you and best regards, Robert

12

Hi Robert,

AH! OK, now I get it. - I think:

You just want a way to put any field from any table on a form - regardless of the relation between the underlying tables?

Part of the problem is that since we’re dealing with SQL - and Servoy does all the queries and databinding for your automatically - the form has to be tied to a particular table.

So, placing a related field is not that big of a deal (you can only see relations that are related to the base table). The problem comes - if you want data related in 1:M:1 - how do we let you navigate to show you the valid tables?

There are no “table occurances” in Servoy (thank God!)…

13

Hi Bob

bcusick:
Hi Robert,

AH! OK, now I get it. - I think:

You just want a way to put any field from any table on a form - regardless of the relation between the underlying tables?

More or less, yes.

bcusick:
Part of the problem is that since we’re dealing with SQL - and Servoy does all the queries and databinding for your automatically - the form has to be tied to a particular table.

So, placing a related field is not that big of a deal (you can only see relations that are related to the base table). The problem comes - if you want data related in 1:M:1 - how do we let you navigate to show you the valid tables?

In WebObjects, it’s simply done by navigating along the possible relationships. It’s displayed in the (Mac OS) column view. After selecting a relationship, the next column shows the corresponding attributes (of that table) as well as the next relationships (going from that table further). If one selects again a relationship, one gets the next tables attributes and relationships, and so on. That means, one can navigate to any useful attribute (field). This way, everything is well defined and easy to access and doesn’t need calc fields. BTW, the used notation is the same as Servoy uses, i. e. field = relation1.relation2.fieldname. Easy to read.

bcusick:
There are no “table occurances” in Servoy (thank God!)…

I completly agree with you, a 100%!

I hope i could have written it understandable. I really think we should have easy access to attributes more than one relationship away in Servoy and hope for an improvement in his (basic) aera :-)

Best regards, Robert

PS: If I would have another wish for free, I would like to have a form builder tool like Matisse in Netbeans - yes, yes, I know, everyone wants something, but dreaming is so nice ,-)