Board index » off-topic » Nested Dataset How To Question
Preston
Delphi Developer |
Preston
Delphi Developer |
Nested Dataset How To Question2005-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 |
Kostas Terzides
Delphi Developer |
2005-08-13 01:09:36 AM
Re:Nested Dataset How To Question
Preston wrote:
QuoteI've been using nested datasets for showing master detail relationships and the dsp.Dataset) don't have the same name, because midas isn't able to initialize MD_LinkFields correctly |
Preston
Delphi Developer |
2005-08-13 01:13:05 AM
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? QuoteSetting provider.Option poCascadeUpdates should do that. {smallsort} |
Kostas Terzides
Delphi Developer |
2005-08-13 01:35:44 AM
Re:Nested Dataset How To Question
Preston wrote:
QuoteIt's still not working. Do I have to do something special like ApplyUpdates 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? |
Preston
Delphi Developer |
2005-08-13 01:44:23 AM
Re:Nested Dataset How To QuestionQuote
on edit, so maybe on post or ApplyUpdates? |
Kostas Terzides
Delphi Developer |
2005-08-13 03:43:33 AM
Re:Nested Dataset How To Question
Preston wrote:
Quote
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 :( |
Preston
Delphi Developer |
2005-08-13 04:11:58 AM
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 |
Kostas Terzides
Delphi Developer |
2005-08-13 04:25:14 AM
Re:Nested Dataset How To Question
Preston wrote:
QuoteHi Kostas, 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. |
Preston
Delphi Developer |
2005-08-13 04:31:33 AM
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. |
Kostas Terzides
Delphi Developer |
2005-08-13 05:36:59 AM
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 |
Preston
Delphi Developer |
2005-08-16 03:59:48 AM
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
|
Anderson Farias
Delphi Developer |
2005-08-16 04:16:30 AM
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 Quote1. The origin is set. |
Kostas Terzides
Delphi Developer |
2005-08-16 05:50:53 AM
Re:Nested Dataset How To Question
Preston wrote:
Quote1. The origin is set. 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. |
Preston
Delphi Developer |
2005-08-16 06:38:32 AM
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 |
Preston
Delphi Developer |
2005-08-16 06:53:51 AM
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 |