Board index » off-topic » Nested Dataset How To Question

Nested Dataset How To Question


2005-08-12 11:48:05 PM
off-topic1
I've been using nested datasets for showing master detail relationships and
it works great.
Up till now I've been linking the two datasets on non user editable key
field. Now I want to hook it to a field(s) that are user editable.
For example I have an item, which had a vendor field. When the vendor field
is changed I the vendorInfo nestedDataSet to resync. It's not doing that?
Any suggestions?
Preston
 
 

Re:Nested Dataset How To Question

Preston wrote:
Quote
I've been using nested datasets for showing master detail relationships and
it works great.

Up till now I've been linking the two datasets on non user editable key
field. Now I want to hook it to a field(s) that are user editable.

For example I have an item, which had a vendor field. When the vendor field
is changed I the vendorInfo nestedDataSet to resync. It's not doing that?

Any suggestions?

Preston


Setting provider.Option poCascadeUpdates should do that.
This might not succeed (see QC 1488 and 11029) if the link fields (in
the dsp.Dataset) don't have the same name, because midas isn't able to
initialize MD_LinkFields correctly
 

Re:Nested Dataset How To Question

It's still not working. Do I have to do something special like ApplyUpdates
to get the provider to recognize the cdsData change?
Quote
Setting provider.Option poCascadeUpdates should do that.

This might not succeed (see QC 1488 and 11029) if the link fields (in the
dsp.Dataset) don't have the same name, because midas isn't able to
initialize MD_LinkFields correctly
 

{smallsort}

Re:Nested Dataset How To Question

Preston wrote:
Quote
It's still not working. Do I have to do something special like ApplyUpdates
to get the provider to recognize the cdsData change?


>Setting provider.Option poCascadeUpdates should do that.
>
>This might not succeed (see QC 1488 and 11029) if the link fields (in the
>dsp.Dataset) don't have the same name, because midas isn't able to
>initialize MD_LinkFields correctly




I suggest you write the cds to an xml file and then search for
MD_LINKFIELDS (attribute IIRC). If it is there (followed by FieldNo of
link fields), then you don't have an issue related to the QC reports I
mentioned.
Another reason MD_LinkFields initialization might fail is if dsp.dataset
doesn't have persistent fields (at least this happens with dbexpress).
Are you using dbexpress components?
 

Re:Nested Dataset How To Question

Quote

I suggest you write the cds to an xml file and then search for
MD_LINKFIELDS (attribute IIRC). If it is there (followed by FieldNo of
link fields), then you don't have an issue related to the QC reports I
mentioned.

Another reason MD_LinkFields initialization might fail is if dsp.dataset
doesn't have persistent fields (at least this happens with dbexpress). Are
you using dbexpress components?
Yes, I'm using DBExpress and I have persistant fields.
If if just change the cdsField when does the DSP become aware? I doubt it's
on edit, so maybe on post or ApplyUpdates?
 

Re:Nested Dataset How To Question

Preston wrote:
Quote

Yes, I'm using DBExpress and I have persistant fields.

If if just change the cdsField when does the DSP become aware? I doubt it's
on edit, so maybe on post or ApplyUpdates?


Take a look at a sample application I posted on attachments (against Ms
Access Northwind application) and tell me if this application behaves as
you expect (change the CustomerID of CustomerCds and see that the
OrdersCds CustomerID is updated- there is no need to ApplyUpdates, or
notify the dsp whatsoever).
Then take a look at kostas.xml. It has this line:
<PARAM Name="MD_FIELDLINKS" Value="3 1 1" Type="IntArray"/>
That's the reason it behaves as it should.
Finally, if you see the CommandText of OrdersDs:
select CustomerID, OrderDate from Orders where CustomerID=:CustomerID
, you will notice that the parameter name (CustomerID) (the field we are
looking for in the Self.DataSource.Dataset) should have the same name as
the field we are trying to do the synchronise.
If this doesn't help you, then I am sorry but obviously I've missed what
you are trying to do at first place :(
 

Re:Nested Dataset How To Question

Hi Kostas,
Thanks for the demo. I don't have access but I did spend sometime looking at
how things were set up. It looks very similar to what I currently have that
works.
If you were to replace the parent grid with an edit box that was hooked up
to the customer id, and then change the value does the detail grid work?
That's what I'm trying to do.
Preston
 

Re:Nested Dataset How To Question

Preston wrote:
Quote
Hi Kostas,

Thanks for the demo. I don't have access but I did spend sometime looking at
how things were set up. It looks very similar to what I currently have that
works.

If you were to replace the parent grid with an edit box that was hooked up
to the customer id, and then change the value does the detail grid work?
That's what I'm trying to do.

Preston


I put a TDBEdit and a TDBNavigator, wired them up and when I change the
value inside the edit box and post (with the navigator), the related
fields in the detail grid get updated. You need to post (either in code
or using the tdbnavigator) for the changes to be cascaded to the detail
dataset.
 

Re:Nested Dataset How To Question

I started to put together my own sample but DBDemos doesn't really support
what I've got. I thought I'd try to briefly show what's going on to see if
that helps.
<Rough table schemas>
Table Items
RecordID as ParentRecordID
ItemID
VendorID
Table VendorInfo
RecordID
ParentRecordID
VendorID
Cost
VendorItemID
<Master and detail queries>
Select RecordID as ParentRecordID, ItemID, VendorID from Items;
Select Cost, VendorItemID from VendorInfo where VendorID = : VendorID AND
ParentRecordID = ParentRecordID
All fields are persistant on both the Queries and ClientDataSets.
On the Item Query ParentRecordID is set as a key field (VendorID is not...
though I have tried it)
On the VendorInfo query RecordID is set as a key field.
ProviderOptions.poCascadeUpdates := True;
ProviderOptions.poPropogateChanges := True;
<Behavior>
Navigating the master dataset causes the detail dataset to load the right
information.
Changing the Items.VendorID does not cause the detail dataset to reload.
Thoughts:
I don't have VendorID as a key field, does it need to be. The help file for
CascadeUpdates would suggest so. However, when setting it, it didn't seem to
matter.
 

Re:Nested Dataset How To Question

3 thoughts:
1) Set Origin property of ParentRecordID of Items query to
'Items.RecordID' without the quotes (as proposed by Dave RownTree in QC
1488)
2) Do what I asked you and save to a XML file and search for a line
like the following:
<PARAM Name="MD_FIELDLINKS" Value="3 1 1" Type="IntArray"/>
If it there, then I am searching towards the wrong direction
3) Changing Items.VendorID would not cause the detail dataset to
reload. It would just automatically change (cascade) all
VendorInfo.VendorID to the new value
 

Re:Nested Dataset How To Question

1. The origin is set.
2. MD_FIELDLINKS is not any where in the XML file. I also read the two QC's
you mentioned and I don't think they apply here. Maybe they do?
3. I thought a nested dataset only contained the data that was relevant to
the master. So, wouldn't changing the master value force the detail to load
new values (fetch from the db) ? That's my whole problem. If I navigate
around I see the correct values. However, If I change the master value I
don't see the new detail values, the old detail values are still in place.
Preston
"Kostas Terzides" < XXXX@XXXXX.COM >wrote in message
Quote

3 thoughts:
1) Set Origin property of ParentRecordID of Items query to
'Items.RecordID' without the quotes (as proposed by Dave RownTree in QC
1488)
2) Do what I asked you and save to a XML file and search for a line like
the following:
<PARAM Name="MD_FIELDLINKS" Value="3 1 1" Type="IntArray"/>

If it there, then I am searching towards the wrong direction

3) Changing Items.VendorID would not cause the detail dataset to reload.
It would just automatically change (cascade) all VendorInfo.VendorID to
the new value
 

Re:Nested Dataset How To Question

|3. I thought a nested dataset only contained the data that was relevant to
|the master.
no. When using nested datasets, for each master record loaded all its child
records are also loaded at once.
T+
"Preston" < XXXX@XXXXX.COM >escreveu na mensagem
Quote
1. The origin is set.
2. MD_FIELDLINKS is not any where in the XML file. I also read the two
QC's
you mentioned and I don't think they apply here. Maybe they do?
3. I thought a nested dataset only contained the data that was relevant
to
the master. So, wouldn't changing the master value force the detail to
load
new values (fetch from the db) ? That's my whole problem. If I navigate
around I see the correct values. However, If I change the master value I
don't see the new detail values, the old detail values are still in place.

Preston


"Kostas Terzides" < XXXX@XXXXX.COM >wrote in message
news:42fd14cd$ XXXX@XXXXX.COM ...
>
>3 thoughts:
>1) Set Origin property of ParentRecordID of Items query to
>'Items.RecordID' without the quotes (as proposed by Dave RownTree in QC
>1488)
>2) Do what I asked you and save to a XML file and search for a line
like
>the following:
><PARAM Name="MD_FIELDLINKS" Value="3 1 1" Type="IntArray"/>
>
>If it there, then I am searching towards the wrong direction
>
>3) Changing Items.VendorID would not cause the detail dataset to
reload.
>It would just automatically change (cascade) all VendorInfo.VendorID to
>the new value


 

Re:Nested Dataset How To Question

Preston wrote:
Quote
1. The origin is set.
2. MD_FIELDLINKS is not any where in the XML file. I also read the two QC's
you mentioned and I don't think they apply here. Maybe they do?
3. I thought a nested dataset only contained the data that was relevant to
the master. So, wouldn't changing the master value force the detail to load
new values (fetch from the db) ? That's my whole problem. If I navigate
around I see the correct values. However, If I change the master value I
don't see the new detail values, the old detail values are still in place.
No, it would *update* the related nested dataset link field values of
*existing* records to reflect the fact that they belong to this
particular master record (or at least it should do that if you didn't
have the problems with MD_FIELDLINKS). It is the same as setting a
referential constraint in a database (e.g. Interbase) like that:
ALTER TABLE VendorInfo ADD CONSTRAINT SomeConstraintName FOREIGN KEY
(VendorID) REFERENCES Items (VendorID) ON UPDATE CASCADE;
In that case, whenever you alter the value of VendorID of Items table
for example from 15 to 20, all records in VendorInfo table having
VendorID value=15 will automatically change to 20 (cascade)
However, there are some things I still haven't been able to understand
related to your design. An Item from items table can belong to a single
Vendor and a Vendor can have multiple items, is that correct?
If that is the case, then VendorInfo table should be the master one and
Items should be the detail (or VendorInfo could be the lookup table for
Items table).
What you are obviously trying to achieve is to use a TEdit and *search*
for VendorInfo of a particular item. That means you don't need to use
nested datasets. You could use the following 2 options depending on how
often VendorInfo table is changed and how many records it has.
1) If VendorInfo changes often, then you could use two independent
datasets, with poAllowCommandText of ProviderVendorInfo set to true and
then you could reload the particular VendorInfo record using a
CdsVendorInfo.CommandText clause like this:
SELECT FROM VendorInfo WHERE VendorInfoID=: Param
and set in a button onClick the following code:
CdsVendorInfo.Close;
CdsVendorInfo.ParamByName('Param').Value:=Edit1.Text;
CdsVendorInfo.Open;
, or something like that.
2) If VendorInfo is rather small (100-500 records) and is rarely
changing (static lookup table), the best option is to simply fetch all
records in client side (in CdsVendorInfo) and use a lookup field in
CdsItems that has as LookupDataSet the VendorInfo Cds.
 

Re:Nested Dataset How To Question

Thanks, for the clarification
"Anderson Farias" < XXXX@XXXXX.COM >wrote in message
Quote
|3. I thought a nested dataset only contained the data that was relevant
to
|the master.

no. When using nested datasets, for each master record loaded all its
child
records are also loaded at once.


T+

 

Re:Nested Dataset How To Question

"there are some things I still haven't been able to understand related to
your design. "
I was trying to keep it simple which has probably led to the confussion.
There are items and vendors. Items are ordered from Vendors. Some items can
be ordered from multiple vendors. The relationship between the item and the
vendor is stored in a ItemVendorInfo table. It basically has vendorItemID
(what ID the vendor uses to identify the item), cost, and lead time (how
long it takes for the vendor to ship the item).
When viewing an item record one of the fields is the Primary Vendor. Along
with that I'd like to show the (nested) ItemVendorInfo fields as well. Most
people order an item from only one vendor so it would speed up the data
entry process if they had access to both records at once.
Should the user change the Primary Vendor to someone else I'd like the new
detail info to load, or in the event they select a vendor where there isn't
a current relationship, it clears the fields and creates an insert record.
Regarding the MD_FIELDLINKS. I've been saving my other nested dataset's to
file (per your suggestion) and I can see the entry in there. I'll keep
poking around this one and see if I can get it to pop up.
Preston