Smart Business Entity Adapter - Retrieve Only 1 Record Cancel The title field is required!

1. Roger Blanchard

   

   381 posts
   Registered:
   29 Jun 2018
   03 Dec 2018

   Link to this post

   I have a form with a smart business entity adapter, a smartbindingsource and a smartviewer. We are not using a smart data browser to allow the user to see all records. This is a form in our tablet application where the user scans a barcode and the item record is displayed on the form where the user can update certain fields.
   
   So, I am not calling ItemAdapter:RetrieveData in the CONSTRUCTOR. Instead, when the barcode is scanned or key entered I am doing the following;
   
   cWhereString = "WHERE eItem.ItemNum = " + QUOTER (cItemNum) + " AND eItem.PackNum = 1". // eventually use scanner input
     
   THIS-OBJECT:ItemAdapter:EmptyDatasetOnRetrieveData = TRUE.
   THIS-OBJECT:ItemAdapter:BatchSize = 1. // we only want one record
   THIS-OBJECT:ItemAdapter:PrefetchOnFindRowWhere = 0.
   THIS-OBJECT:ItemAdapter:FindRowWhere (cWhereString, FindRowWhereModeEnum:FindOnServerOnly).
   
   The above code works but there are always two records that are fetched. Is there a way to make sure there is only one record that is returned? 
   
   Is there a better way to handle this?
   
2. Mike Fechner

   

   319 posts
   Registered:
   14 Sep 2016
   03 Dec 2018 in reply to Roger Blanchard

   Link to this post

   BatchSize = 1 does not work. After fetching a single record, the Progress.Data.BindingSource will fetch the next record, because it realizes it was already on the last record available on the client.
   
   Use BatchSize = 2 instead - or a unique query that only returns a single record.
3. Roger Blanchard

   

   381 posts
   Registered:
   29 Jun 2018
   03 Dec 2018 in reply to Mike Fechner

   Link to this post

   I am using a unique query. There is only one record that matches my query.
   
   For example. my query is "WHERE eItem.ItemNum = 13 AND eItem.PackNum = 1". 
   
   There is a unique index on the Item table for ItemNum and PackNum.
   
   After I call FindRowWhere if I dump the dataset to disk it contain not only ItemNum of 13 but the Item record following it (14)
   
   THIS-OBJECT:ItemAdapter:FindRowWhere (cWhereString, FindRowWhereModeEnum:FindOnServerOnly)
   
   
   
   
4. Mike Fechner

   

   319 posts
   Registered:
   14 Sep 2016
   03 Dec 2018

   Link to this post

   Ok. Did you try setting the Business Entity Adapter's QueryString property to 
   
   FOR EACH <eTempTableName> WHERE <unique record criteria>
   
   and then call RetrieveData?
5. Roger Blanchard

   

   381 posts
   Registered:
   29 Jun 2018
   03 Dec 2018 in reply to Mike Fechner

   Link to this post

   I did not but will right now.
6. Roger Blanchard

   

   381 posts
   Registered:
   29 Jun 2018
   03 Dec 2018 in reply to Mike Fechner

   Link to this post

   Ah, seems to work. The xml on disk only has the one record and the THIS-OBJECT:ItemAdapter:RecordPosition returns "OnlyRecord".
   
   Let me do a bit more testing.
   
   thanks
   
7. Roger Blanchard

   

   381 posts
   Registered:
   29 Jun 2018
   03 Dec 2018 in reply to Mike Fechner

   Link to this post

   Okay, this works for my ItemAdapter where the dataset is limited to the unique record.
   
   This ItemAdapter is also the LinkDataSource for another adapter (HistoryTotsAdapter) that uses ForeignFields = "ItemNum,ItemNum,PackNum,PackNum" as well as well as QueryString = HistoryTots.Period = "M" AND. This HistoryTotsAdapter is the LinkDataSource for a SmartDataBrowse. If I use the QueryString on the ItemAdapter I see no records in the SDB. However, in reviewing the appserver logs the client query, default query and prepare query seem identical. 
   
   This is without using the QueryString on the ItemAdapter
   
   [18/12/03@10:55:14.072-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eItem: no
   [18/12/03@10:55:16.699-0500] P-017108 T-000740 1 AS-4 DataAccess     Client-Query: HistoryTots.Period = "M" AND WHERE  eHistoryTots.ItemNum = "           6" AND eHistoryTots.PackNum = "1"
   [18/12/03@10:55:16.699-0500] P-017108 T-000740 1 AS-4 DataAccess     Default-Query: FOR EACH HistoryTots INDEXED-REPOSITION
   [18/12/03@10:55:16.702-0500] P-017108 T-000740 1 AS-4 DataAccess     Prepare-Query: FOR EACH HistoryTots WHERE HistoryTots.Period = "M" AND HistoryTots.ItemNum = "           6" AND HistoryTots.PackNum = "1" INDEXED-REPOSITION
   [18/12/03@10:55:16.702-0500] P-017108 T-000740 1 AS-4 DataAccess     Index Information on Buffer HistoryTots: pItemDate
   [18/12/03@10:55:16.702-0500] P-017108 T-000740 1 AS-4 DataAccess     Restart-Rowid for Table eHistoryTots: ?
   [18/12/03@10:55:16.702-0500] P-017108 T-000740 1 AS-4 DataAccess     Next-Rowid for Table eHistoryTots: ?
   [18/12/03@10:55:16.702-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eHistoryTots: yes
   
   This is with using the QueryString on the ItemAdapter
   
   [18/12/03@10:55:45.037-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eItem: yes
   [18/12/03@10:55:45.182-0500] P-017108 T-000740 1 AS-4 DataAccess     Client-Query: HistoryTots.Period = "M" AND WHERE  eHistoryTots.ItemNum = "           6" AND eHistoryTots.PackNum = "1"
   [18/12/03@10:55:45.182-0500] P-017108 T-000740 1 AS-4 DataAccess     Default-Query: FOR EACH HistoryTots INDEXED-REPOSITION
   [18/12/03@10:55:45.185-0500] P-017108 T-000740 1 AS-4 DataAccess     Prepare-Query: FOR EACH HistoryTots WHERE HistoryTots.Period = "M" AND HistoryTots.ItemNum = "           6" AND HistoryTots.PackNum = "1" INDEXED-REPOSITION
   [18/12/03@10:55:45.185-0500] P-017108 T-000740 1 AS-4 DataAccess     Index Information on Buffer HistoryTots: pItemDate
   [18/12/03@10:55:45.185-0500] P-017108 T-000740 1 AS-4 DataAccess     Restart-Rowid for Table eHistoryTots: ?
   [18/12/03@10:55:45.185-0500] P-017108 T-000740 1 AS-4 DataAccess     Next-Rowid for Table eHistoryTots: ?
   [18/12/03@10:55:45.185-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eHistoryTots: yes
   
   
   
   
   
   
8. Roger Blanchard

   

   381 posts
   Registered:
   29 Jun 2018
   03 Dec 2018 in reply to Roger Blanchard

   Link to this post

   As a follow up if I add a call to HistoryTotsAdapter:RetrieveData in the ultratab:ActiveTabChanged I then see the records. If you look at the appserver logs you can see RetrieveData was called twice.
   
   
   [18/12/03@11:27:27.438-0500] P-017108 T-000740 1 AS-4 DataAccess     Client-Query: FOR EACH eItem WHERE eItem.ItemNum = "           6" AND eItem.PackNum = 1
   [18/12/03@11:27:27.438-0500] P-017108 T-000740 1 AS-4 DataAccess     Default-Query: FOR EACH Item INDEXED-REPOSITION
   [18/12/03@11:27:27.439-0500] P-017108 T-000740 1 AS-4 DataAccess     Prepare-Query: FOR EACH Item WHERE Item.ItemNum = "           6" AND Item.PackNum = 1 INDEXED-REPOSITION
   [18/12/03@11:27:27.439-0500] P-017108 T-000740 1 AS-4 DataAccess     Index Information on Buffer Item: upItem
   [18/12/03@11:27:27.439-0500] P-017108 T-000740 1 AS-4 DataAccess     Restart-Rowid for Table eItem: ?
   [18/12/03@11:27:27.440-0500] P-017108 T-000740 1 AS-4 DataAccess     Next-Rowid for Table eItem: ?
   [18/12/03@11:27:27.440-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eItem: yes
   [18/12/03@11:27:27.592-0500] P-017108 T-000740 1 AS-4 DataAccess     Client-Query: HistoryTots.Period = "M" AND WHERE  eHistoryTots.ItemNum = "           6" AND eHistoryTots.PackNum = "1"
   [18/12/03@11:27:27.592-0500] P-017108 T-000740 1 AS-4 DataAccess     Default-Query: FOR EACH HistoryTots INDEXED-REPOSITION
   [18/12/03@11:27:27.595-0500] P-017108 T-000740 1 AS-4 DataAccess     Prepare-Query: FOR EACH HistoryTots WHERE HistoryTots.Period = "M" AND HistoryTots.ItemNum = "           6" AND HistoryTots.PackNum = "1" INDEXED-REPOSITION
   [18/12/03@11:27:27.595-0500] P-017108 T-000740 1 AS-4 DataAccess     Index Information on Buffer HistoryTots: pItemDate
   [18/12/03@11:27:27.596-0500] P-017108 T-000740 1 AS-4 DataAccess     Restart-Rowid for Table eHistoryTots: ?
   [18/12/03@11:27:27.596-0500] P-017108 T-000740 1 AS-4 DataAccess     Next-Rowid for Table eHistoryTots: ?
   [18/12/03@11:27:27.596-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eHistoryTots: yes
   [18/12/03@11:27:30.753-0500] P-017108 T-000740 1 AS-4 DataAccess     Client-Query: HistoryTots.Period = "M" AND WHERE  eHistoryTots.ItemNum = "           6" AND eHistoryTots.PackNum = "1"
   [18/12/03@11:27:30.754-0500] P-017108 T-000740 1 AS-4 DataAccess     Default-Query: FOR EACH HistoryTots INDEXED-REPOSITION
   [18/12/03@11:27:30.756-0500] P-017108 T-000740 1 AS-4 DataAccess     Prepare-Query: FOR EACH HistoryTots WHERE HistoryTots.Period = "M" AND HistoryTots.ItemNum = "           6" AND HistoryTots.PackNum = "1" INDEXED-REPOSITION
   [18/12/03@11:27:30.756-0500] P-017108 T-000740 1 AS-4 DataAccess     Index Information on Buffer HistoryTots: pItemDate
   [18/12/03@11:27:30.756-0500] P-017108 T-000740 1 AS-4 DataAccess     Restart-Rowid for Table eHistoryTots: ?
   [18/12/03@11:27:30.757-0500] P-017108 T-000740 1 AS-4 DataAccess     Next-Rowid for Table eHistoryTots: ?
   [18/12/03@11:27:30.757-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eHistoryTots: yes