Deep Down Relationship in Portal Row

Classic Servoy
1

Hi Guys,

I have the following tables & relationships :

Table Fields

  1. OrderHeader - DocNo / .DocDate / . Customer
  2. OrderLine - DocNo / ItemNo. / StockCode / Qty / UnitPrice
  3. Stock - StockCode / Desc / Supplier / ColorCode
  4. Color - Color Code / Desc
  5. Supplier - Code / Name

Relationships:

  1. OrderHeader_to_OrderLine
  • OrderHeader.DocNo = OrdrLine.DocNo
  1. OrderLine_to_Stock
  • OrderLine.StockCode = Stock.StockCode
  1. Stock_to_Supplier
  • Stock.SupplierCode = Supplier.SupplierCode
  1. Stock_to_Color
    Stock.ColorCode = Color.ColorCod

I have a portal using OrderHeader_to_OrderLine, :

Question 1)

When user key in the stock code at the portal line -
OrderLine.StockCode, HOW can I display the Stock.Desc which was
link by OrderLine_to_Stock relationship and Color.Desc which was link
thru Stock_to_Color relationship on the poratl row.

Example: Portal: OrderHeader_to_OrderLine:

ItemNo StockCode Stock.Desc Color.Desc Qty Stock.UnitPrice
x 1 1001 Running Shoe Red 1 159.90
x 2 2002 Tennis Shoe White 1 249.90
x 3 3003 Joggin Shoe Blue 2 179.90

Question 2)

When the user click on th “x” button, the onAction method will fire to show
the selected row in FID form , How can I display the follwing data in FID.

Item No. : 2
Stock Code : 2002
Stock Desc : Tennis Shoe ← From Stock.Desc

Color Code : 101 ← From Stock.ColorCode
Color Desc : White ← From Color.Desc

Supplier Code: S001 ← Form Stock.SupplierCode
Supplier Name : Hitech Shoe Ltd. ← From Supplier.Name

Thanks in advance.

Kelvin

a) OrderHeader.Docno → OrderLine.DocNo

  • OrderLine with fileds:
  • StockCode + Qty + UnitPrice
    b)
2

Hi Kelvin,

I understand the relational schema that runs this and the need to have the related data to hand but my first question concerns the methodology behind where the data resides here.

In all set ups of this type (e.g. orders, quotes, invoices etc) I think more of making the data used in line item tables static and not totally relational.

That means I will lookup (or copy) data into fields within the line item table.

This is simply due to the fact that if you do not do this then you MUST ensure that the data will never change within those reference tables where the data is being used by simple reference to the related data.

For example, at the point which the product table changes and you maybe delete an item or modify a description then ALL existing orders etc will change because the data is relational - the worst case being deletion of a product item which means that there is then NO description in existing orders !

Are you in control of this and can you ensure that this sort of thing will never happen ?

If you cannot then you need to copy the related data into the line itmes table at point of creation and then you will have the necessary data in place to do what you require with regard to display in portal and FID without further methodology involved !

Cheers
Harry

3

Hi Harry,

Thanks for the reply. If I have to store all the related data fields inside
the line items, it will create a lot of redanduncy data & also I needs to
have a method to change all the line item description if someone
amend the stock or color description because of typing mistake. I will
have before delete method to control that no master data can be
deleted if there are line item exist. User also expect to see the changes
in master file are reflected in all the reports and forms printe out.

I just hope that there are ways of doing the relationship way in Servoy
for the portal without involving many globals variable to store as
temporary data as in other programming language.

Merry Christmas & Happy New Year to all of you.

Thanks.

Kelvin

4

Hi Kelvin,

Well, as long as I have got my opinion across then I am happy :lol:

Portals are not going to allow you the flexibility to go more than one relational hop deep !

You need to investigate using related tab panels.

Using a tab panel to show a form based on the line items table means that you can bring related fields onto that form thus showing data from two relational hops away.

If you want more than that at present then you need to use lookups or calculations to bring in that more remote data ! :cry:

Yuletide Felicitations to you !

Cheers
Harry