One To Many (and many and many...) update scenario

Hi Guys.

I'm after opinions on the best way to handle the following (example) scenario

Header Table
Must have at least 1 Item record and can have many

Item Table, linked to Header by foreign key
Each Item must link to a Header record, and must have at least 1 SubItem record


SubItem Table, linked to Item by foreign key
Each SubItem must link to an Item record


All data updates are handled in Transactions, so I need to buffer an indeterminate numbers of Items and SubItems per Header record.

Typically the Items/SubItems will be managed on screen in small editable memory tables

I am leaning towards holding the data in a data class containing collections of items,
with each item holding a collection of subitems.

I am still deciding how to code this, some considerations are:

Base collections on arrays (objects, structures, or just 2 dimensional arrays?)
Methods of populating UI from these collections
Methods of updating data from these collections
Somehow try to keep the above as generic as possible for re-use

Obviously, I am so new to the product that I might miss some very useful commands and functions, so tips greatly appreciated.


Regards

Malc

Hi Marc,

Welcome. I only use hyperfile, I'm assuming you will too.

First define the files in the analysis:

Header:
Unique key item (automatic or manual, I prefer manual)
one or more description items

Item:
Unique key (automatic or manual, I prefer manual)
Key with duplicates, same name and description as Header key
one or more description items

Subitem:
Unique key (automatic or manual, I prefer manual)
Key with duplicates, same name and description as Unique item key
one or more description items

Then you define the links using the wizard.

UI:
Create a window with three tables, each table based on a query.
In the row selection section of the Header table:
Tabledisplay(Table_Item,ta init)
Tabledisplay(Table_SubItem,ta init)
In the row selection section of the Item table:
Tabledisplay(Table_SubItem,ta init)
In the queries you select records based on the parameters Header_key and Item Key.

In the Table initialization code you feed the paramaters to the query. Use hexecutequery for this.

All editing can be handled by Windev.

HTH regards,
Piet

Hi Piet

Thanks for the reply.

> Welcome. I only use hyperfile, I'm assuming you will too.

Actually, I will probably be using MS SQL via OLEDB, but I should be able to use
most of the Windev commands with that.


> All editing can be handled by Windev.

Allow me to expand my requirements...

One issue I can see is that the HAdd/HModify work on one row at a time. I need to:

Begin Transaction (SQLTranscation(sqlStart)

...HAdd or HModify Header
...For Each Item
......HAdd or HModify
......For Each SubItem
.........HAdd or HModify

End transaction (Commit/RollBack)


The Data/Processing Tier, which will probably be a class,
needs to do all the validation and updating as it may be shared by multiple interfaces,
e.g. Windows UI, Web UI, Webservice, File Import etc..

What I really need, and is not currently available, is a table buffered data source.
Therefore I am looking at lists/rows of data in memory.



Regards


Malc

Hi Malc,

Then you can use memory tables or lists. Tables seem to the best solution, since you can use hidden colums for data that is not visible to the enduser. If no ui is required, you can use arrays of structures to store the data in memory.

Best regards,
Piet

Hi Piet


I'm just figuring out the various coding issues in which to apply these methodologies.

I was wondering if anyone had actually done this previously, and could offer a few pointers.

I'll get there eventually, but, like the rest of the world, I'm after a quick fix :-)


Thanks for the reply


Malc

If you plan to use the same class for windows and webservices don't store the data in a table control, it will be a problem in webservices.

In this cases i usualy use arrays of structures, they are very easy to use and they can be used in WD/WB/webservices or windows services.