Multi-level relationship technique

Classic Servoy
1

Hi, I have three tables that are connected through the relationship described in the first attached image. I’d like to display a list of records in a nested form on the right based on the currently selected record on the left.

This is easy enough to do with relationships that are one level apart, but I’m not sure how to do it with a table that’s more distant. Any ideas?

Thanks,

Andrew

2

This can easily be done by making creative use of calcs and relations.
If you had a foreign key in VendorBids pointing to its grandparent Itemid, you’d know how to do this. Problem is you don’t have such a foreign key. But using a calc and a relation, you can “create one”. Here’s how.

  1. Create a relation called Vendorbids_to_Bids, with primary key = VendorBids.bidid and foreigh key = Bids.bidid.

  2. Create a non-stored calc in VendorBids - I’ll call it “itemid” but you can call it whatever you like. Set its calculation script to return Vendorbids_to_Bids.itemid. You now have a Vendorbids.itemid column which you can treat like a foreign key. Pretty cool huh!

  3. Now create a relation called Items_to_VendorBids with primary key = items.itemid and foreign key = Vendorbids.itemid.

  4. Create a form containing the VendorBid columns you want in your right panel.

  5. Create a tabpanel based on the Items_to_VendorBids relation and displaying that form.

3

I got to step three, but it doesn’t let me add the calculation VendorBids.itemid as a foreign key… what am I missing?

Thanks,

Abndrew

4

…Anyone? Should this technique work?

5

Sorry, I made a mistake in step 2 of my instructions. The calc must be a stored calc, which will require a db column in your VendorBids table.

If having a db column for this purpose isn’t an option then I think you’ll have to write SQL and use getDataSetByQuery to retrieve the “grandchildren” of your current item and then perhaps use getAsHTML() to display them.