DatasetModel and fetching from child level table upwards Cancel The title field is required!

1. Patrik Wikström

   

   68 posts
   Registered:
   15 Oct 2018
   17 Oct 2022

   Link to this post

   With the business entity interface one can fetch, and use, data easily from a child, or grandchild, level of a table. Am I assuming correctly that with the datasetmodel interface there is a restriction that one always has to fetch from the root level table in the dataset?
   
   Asking because one of our developers has optimized the business task code by using a key-table to Fill() exactly the child records he needed in that use case but that didn't work because the query of the child table, when calling GetFirst(), still contained the where clause to the parent, which data was of course missing in this case.
   
   Might, of course, be that we don't understand how to control the datasetmodel in this case also.
2. Mike Fechner

   

   319 posts
   Registered:
   14 Sep 2016
   17 Oct 2022 in reply to Patrik Wikström

   Link to this post

   DatasetModels do also support fetching data only from a child table.
   
   Maybe it would be helpful if you'd show some code that demonstrates the flow of events that leads to the query of the child table still containing the where close to the parent.
   
   You can also inspect the FetchDataRequest object issued to the Business Entity (overriding FetchData in the Business Entity) and message the poFetchDataRequest:Queries and Tables properties for instance. 
3. Patrik Wikström

   

   68 posts
   Registered:
   15 Oct 2018
   18 Oct 2022

   Link to this post

   I asked a bit more about this case and it seems that the following is true:
   - If you just fetch one child table, like ProductionOperation (of a ProductionOrder), then everything works fine through the DatasetModel.
   - If you also add a grandchild table to be fetched together with the child (ProductionOperation) table, then the DatasetModel query for the ProductionOperation table is pointing to the father (ProductionOrder) and the DatasetModel will not show any records, not even for the child (ProductionOperation) table.
   
   The table hierarchy:
   - eProductionOrder
   -    eProductionOperation
   -        eProductionResourceGroup
   
   Some code:
   ...
       xDM = NEW ProductionOrderDatasetModel().
       xDM:UseInterface = UseInterfaceEnum:ServiceManager.
       xDM:ProductionOperation:FillChildTables = "eProductionResourceGroup".
       xDM:ProductionOperation:Filter:PrOrderNum:EQ(5).
       xDM:ProductionOperation:Fill().
   
       DO WHILE xDM:ProductionOperation:Available:
           MESSAGE xDM:ProductionOperation:OperPosNum VIEW-AS ALERT-BOX.
           DO WHILE xDM:ProductionResourceGroup:Available:
               MESSAGE "ProductionResourceGroup is available" VIEW-AS ALERT-BOX.
               xDM:ProductionResourceGroup:GetNext().
           END.
           xDM:ProductionOperation:GetNext().
       END.
   ...
   Hopefully I didn't left something out when I copied some code.
   
   
   

4. Mike Fechner

   

   319 posts
   Registered:
   14 Sep 2016
   18 Oct 2022 in reply to Patrik Wikström

   Link to this post

   I cannot reproduce what you're describing. 
   
   I have a DatasetModel around this Dataset:
   
   DEFINE {&ACCESS} DATASET dsOrder{&SUFFIX} {&REFERENCE-ONLY} FOR eOrder{&SUFFIX}, eCustomer{&SUFFIX}, eOrderLine{&SUFFIX}, eItem{&SUFFIX}, eSalesrep{&SUFFIX} 
       DATA-RELATION eOrdereCustomerRelation FOR eOrder{&SUFFIX}, eCustomer{&SUFFIX} 
           RELATION-FIELDS (CustNum,CustNum)
       DATA-RELATION eOrdereOrderLineRelation FOR eOrder{&SUFFIX}, eOrderLine{&SUFFIX} 
           RELATION-FIELDS (Ordernum,Ordernum)
       DATA-RELATION eOrdereSalesrepRelation FOR eOrder{&SUFFIX}, eSalesrep{&SUFFIX} 
           RELATION-FIELDS (SalesRep,SalesRep)
       DATA-RELATION eOrderLineeItemRelation FOR eOrderLine{&SUFFIX}, eItem{&SUFFIX} 
           RELATION-FIELDS (Itemnum,Itemnum)
   
   And when I run 
   
   oOrder = NEW OrderDatasetModel () .
   oOrder:Order:FillChildTables = "eOrderline,eItem" .
   oOrder:Order:Filter:Ordernum:EQ(1):Run () .
   
   MESSAGE oOrder:Order:QueryHandle:PREPARE-STRING SKIP
           oOrder:OrderLine:QueryHandle:PREPARE-STRING SKIP
           oOrder:Item:QueryHandle:PREPARE-STRING
       VIEW-AS ALERT-BOX.
   
   I see:
   
   ---------------------------
   Message (Press HELP to view stack trace)
   ---------------------------
   FOR EACH eOrder 
   FOR EACH eOrderLine  WHERE eOrderLine.Ordernum=eOrder.Ordernum 
   FOR EACH eItem  WHERE eItem.Itemnum=eOrderLine.Itemnum
   ---------------------------
   OK   Hilfe   
   ---------------------------
   
   So it's using the queries of the Data-relations.
   
   Parent -> Child -> Grand-Child.
   
   
   
   
   
   
   
   
   
   
5. Patrik Wikström

   

   68 posts
   Registered:
   15 Oct 2018
   18 Oct 2022 in reply to Mike Fechner

   Link to this post

   Don't start from the root table, then you'll get into trouble? Try Filling from OrderLine.
6. Mike Fechner

   

   319 posts
   Registered:
   14 Sep 2016
   18 Oct 2022 in reply to Patrik Wikström

   Link to this post

   Same.
   
   oOrder = NEW OrderDatasetModel () .
   oOrder:OrderLine:FillChildTables = "eOrderline" .
   oOrder:OrderLine:Filter:Ordernum:EQ(1):Run () .
   
   
   MESSAGE oOrder:Order:QueryHandle:PREPARE-STRING SKIP
           oOrder:OrderLine:QueryHandle:PREPARE-STRING SKIP
           oOrder:Item:QueryHandle:PREPARE-STRING
       VIEW-AS ALERT-BOX.
   
   ---------------------------
   Message (Press HELP to view stack trace)
   ---------------------------
   FOR EACH eOrder 
   FOR EACH eOrderLine  WHERE eOrderLine.Ordernum=eOrder.Ordernum 
   FOR EACH eItem  WHERE eItem.Itemnum=eOrderLine.Itemnum
   ---------------------------
   OK   Hilfe   
   ---------------------------
   
   
   
   
   
7. Patrik Wikström

   

   68 posts
   Registered:
   15 Oct 2018
   18 Oct 2022 in reply to Mike Fechner

   Link to this post

   EDIT:
   You'll have to use the grandchild table, probably like:
   oOrder:OrderLine:FillChildTables = "eItem" .
   ...and can you iterate (GetNext()) through OrderLine and Item after that?

   Last modified on 18 Oct 2022 13:10 by Patrik Wikström
8. Patrik Wikström

   

   68 posts
   Registered:
   15 Oct 2018
   21 Oct 2022 in reply to Patrik Wikström

   Link to this post

   Did you notice my last comment and how it is different? Also the iteration using the DatasetModel.
9. Mike Fechner

   

   319 posts
   Registered:
   14 Sep 2016
   22 Oct 2022 in reply to Patrik Wikström

   Link to this post

   Works fine that way.
   
   If you want us to analyze that, please log a support ticket and attach
   
   - DF of required database tables
   - bedgm file of Business Entity 
   - code snipped to demonstrate the issue
   
10. Patrik Wikström

    

    68 posts
    Registered:
    15 Oct 2018
    27 Oct 2022 in reply to Mike Fechner

    Link to this post

    Hmm, can't understand how iteration is possible GetFirst()/Next() if the query is:
    "FOR EACH eOrderLine  WHERE eOrderLine.Ordernum=eOrder.Ordernum "
    ...when eOrder records are not, in this case, available in the dataset?

    Last modified on 27 Oct 2022 09:10 by Patrik Wikström
11. Mike Fechner

    

    319 posts
    Registered:
    14 Sep 2016
    Answered

    28 Oct 2022 in reply to Patrik Wikström

    Link to this post

    To iterate a table in the dataset model regardless of the data-relations, you can all the UseBufferQuery method of the TableModel.
12. Patrik Wikström

    

    68 posts
    Registered:
    15 Oct 2018
    28 Oct 2022

    Link to this post

    Ok, thanks!
    
    Got it working, now iterates the records, even if it is a child table in the dataset. 
    
    Had to call QueryOpen on the TableModel for the grandchild before iterating through that table. I assume that is how you should do it in this case because we don't rely on the dataset relations anymore after calling UseBufferQuery? Just asking so we write the code the right way.
13. Mike Fechner

    

    319 posts
    Registered:
    14 Sep 2016
    28 Oct 2022 in reply to Patrik Wikström

    Link to this post

    You should know me by now. I prefer to answer questions on are we "coding the right way" when I see come code :)
14. Patrik Wikström

    

    68 posts
    Registered:
    15 Oct 2018
    28 Oct 2022

    Link to this post

    I think this is the right way for now. 
    
    I will open an enhancement ticket for this type of DatasetModel use e.g. so it would work more naturally for the developer without the need to remember to use the UseBufferQuery on the main tablemodel. You can then ponder about it when consuming a beer or three. 
    
    Thanks again for your help! Have a nice weekend!
    
    
    
15. Mike Fechner

    

    319 posts
    Registered:
    14 Sep 2016
    28 Oct 2022 in reply to Patrik Wikström

    Link to this post

    Same to you!