Circular Join

Classic Servoy
1

I may be attempting the impossible…

I have two tables:

Items

ItemID
ItemName

BillOfMaterials (BOM)

ParentItemID
ChildItemID

I want to create a form with Items as the main table. On this form I want to have two portals:

Portal #1 (“BOM” for ItemID) - where ItemID=ParentItemID and shows all the ChildItemID as well as the Items.ItemName where ChildItemID=ItemID.

Portal #2 (“Where Used” for ItemID) - where ItemID=ChildItemID and shows all the ParentItemID as well as the Items.ItemName where ParentItemID=ItemID.

I am unable to get the Items.ItemName to display in the portals. I am a novice, but I believe that this type of circular join may be impossible. Any pointers on how to proceed are appreciated.

Jay

2

Hi,

I’m not sure I completel understand. You mention two tables:

Items

ItemID
ItemName

BillOfMaterials (BOM)

ParentItemID
ChildItemID

And you want to create a portal on a form based on the ‘Items’ table with a relationship for the portal of ItemID:ParentItemID. So for example if we have three rows in the Items table:
ItemID ItemName
1 John
2 Bob
3 Sue

And three rows in the BOM table:
ParentItemID ChildItemID
1 1
1 2
1 3

You would like the portal to look like this (for the first record/row in the Items table):
ChildItemID ItemName
1 John
2 Bob
3 Sue

Is this what you want? If so I think you can do this by putting the ChildItemID twice in the portal. The first one you leave as is. For the second ChildItemID you attach a value list to it. That valuelist will be based on the Items table. You ‘return’ the ItemID and ‘show’ the ItemName. Does this help?

John

3

jblarsen:
I am a novice, but I believe that this type of circular join may be impossible. Any pointers on how to proceed are appreciated.

Impossible??? Come on, this is Servoy!!! I have yet to come across anything that is impossible…

Observations;

  1. I would use forms in invisible tab panels instead of portals. They are much more flexible. Your BOM form can show info from Items using a simple relationship.
  2. Note that each form may only be used once on screen at any time.
  3. As a last resort, If you can’t get what you want to do working using simple relationships,
    try using a on_record_change method n items to update your tab panels using SQL queries via getDataSetByQuery(). You may need to set up the forms in the tab panels to use indpendent found sets.

Hope this helps.