DataSet nested relations

[Vad]

Hi, all.

I'm trying to set relations between two DataSet tables.
The problem is, that the key of the parent table has more columns
than the child table, something like this:

ParentTable(id, name, amount)
ChildTable(id, name)

So I can't relate the column "amount" to any column of ChildTable.
And if I don't consider this column, the program fails on "A child row
has multiple parents."

Any suggestions?
Thanks in advance.

 

[Cor Ligthert [MVP]]

Vad,

In my idea it is impossible what you want to do. The dataset has no
invisible keys or other relation elements.

Therefore if there are more Parents with the same id, name combination, than
you should have to find yourself a solution in the data.

Just my thought,

Cor

 

[W.G. Ryan - MVP]

As it stands, you can't make it work without adding an amount column to the
child so they match - which is ill advised. What you may want to do though,
is change the key in the parent table and remove amount. Add [amount] to
the child table in a mannner that the aggregate of all child records will
equal the actual Amount you want in the parent record. YOu can use an
Aggregate such as DataTable.Compute(Sum(Amount)); to derive the Amount and
then have it available in the parent. But isn't this the same thing you
just told me not to do? , you may be asking.

No. The difference here is that you can choose whether or not to make the
computed column part of the parent table, and even if you do, you're
removing it from the Key so the tables won't be constrained by it.
Aggregates work pretty elegantly so you can make the new parent Amount
either a Calculated column (using its expression property) or you can just
leave it out altogether and use Compute to just populate an integer or
whatever number you're looking for.

Hopefully the distinction is clear - if not, let me know.

Thanks,

Bill

 

[johneevo]

I'm trying to set relations between two DataSet tables.
The problem is, that the key of the parent table has more columns
than the child table, something like this:

ParentTable(id, name, amount)
ChildTable(id, name)

Hi Vad,

Besides what Cor and Bill have said, you might want to try flipping the
parent and child tables in the relationship. Make ChildTable the
parent.

Although I haven't tried this, but I would think you should be able to
find the "parent" record (the ChildTable record) for each record in
ParentTable.

HTH

 

[Cor Ligthert [MVP]]

Johneevo,

That was what I was thinking as well about, but it fails if the key from
what is now the child row is not unique.

Cor

 

[johneevo]

Hi Cor,

I'm not sure what key you are referring to.

To help clarify how about if we change Vad's table names as below:
ParentTable changes to TableB
ChildTable changes to TableA

TableA is now the parent and TableB is the child. Which table's key are
you referring to?

thanks

 

[Cor Ligthert [MVP]]

Table A if that becomes the parent it has for sure non unique keys in the
part Id Name, and the keys for a parent have to be unique.

Cor

 

[johneevo]

OK, now I'm with you.

I made the assumption (which I should have been explicit with) that
since Vad used the term "key", that "id" and "name" are TableA's primary
key. But I guess Vad could have just as easily meant that (id, name)
are a foreign key.