Hi,
I have a ultragrid that has batching enabled. It also has a few calculated fields in it.
I populate the ultragrid with a prodataset through a bindinsource. I pass a query through to the logic to populate the prodataset. This query contains the sort query logic "by fieldname" as needed for the batching. That then populates the prodataset.
The issue I have is with the calculated fields. These are created in the after-row-fill callback and are held in the prodataset temp-table but not against the db I'm querying against when building the query.
I'm a bit stumped as to what I can do to sort it properly on the calculated fields in this case. Anyone any ideas/suggestions?
Thanks
Paul
I assume you want them to display in the grid. Or do you just want to sort by that field?
How does the ultragrid know that there are calculated fields? You are giving only half of the picture. Or is that actually your question - that the grid doesn't know and you have no idea how to get this calculated value displayed?
Though your question seems to be about sorting. Can't answer about sorting until I know the whole picture.
Regarding displaying calculated field values, at a very high level, I think there are 2 ways - one is to actually store all the data in the temp-table, along with the calculated fields and bind the grid to the temp-table. The other (though I'm shaky on the details of this one) is to add fields to the grid at design time that are "unbound" (I may not be using the right Infragistics terminology). Then the grid has a mechanism for asking for the value from the code by firing events when it needs to display it.
Hi Laura,
Sorry I typed that in a bit of a rush last night and maybe didn't explain it quite as well as I could.
I can display the calculated fields no problem. They are in a prodataset temp-table and get bound as required through a bindingsource. Grid displays fine and sorting works on all fields except the calculated ones. Ultragrid doesn't really know they are calculated fields as its all being done before it gets there in the prodataset creation.
The issue I'm having is with regards to how sorting works for those calculated fields when batching is enabled. When reading the info online for batching it says you need to pass through the "by field" clause to the backend query that is used to populate the prodataset to ensure that what you populate for that batch is correct. I'm then doing a dataset fill and using the after-row-fill callback to populate these calculated fields from a number of fields against the real db table.
The part I'm stuck with is how can i sort the prodataset correctly for a calculated field when it doesn't know what the calculated field is until after the row has been created.
I've included a snippit of some of the code I'm using. Not all of it but enough to give an idea on what I'm doing.
The pcSortPhrase gets passed through as basically "by columnname" which is fine for real fields but for calculated fields won't work as they don't exist in the real table.
Backend logic.
/*
** Set a callback that deals with the calculated fields and
** whether the fields can be displayed.
*/
DATASET pdsEmpSelection:GET-BUFFER-HANDLE("ttEmpSelection"):SET-CALLBACK("BEFORE-ROW-FILL","FetchEmpSelectionDS_BeforeRowFill",THIS-OBJECT).
DATASET pdsEmpSelection:GET-BUFFER-HANDLE("ttEmpSelection"):SET-CALLBACK("AFTER-ROW-FILL","FetchEmpSelectionDS_AfterRowFill",THIS-OBJECT).
/*
** Get a query of all the payrolls that are required.
*/
ASSIGN cQueryPayrollList = THIS-OBJECT:PayrollListQuery().
/*
** Set the security string
*/
ASSIGN cSecString = (IF iUserNo <> 0 THEN "SUBSTRING(employee.secstring," + STRING(iUserNo) + ",1) <> ~"N~"" ELSE "").
/*
** Build the query string as required.
*/
ASSIGN cQuery = "PRESELECT EACH employee NO-LOCK WHERE ".
ASSIGN cQueryWhere = cQueryWhere + (IF cQueryPayrollList <> "" THEN
cQueryPayrollList
ELSE
"").
ASSIGN cQueryWhere = cQueryWhere + (IF pcQuery <> "" THEN
(IF cQueryWhere <> "" THEN " AND " ELSE "") + pcQuery
ELSE
"").
ASSIGN cQueryWhere = cQueryWhere + (IF cSecString <> "" THEN
(IF cQueryWhere <> "" THEN " AND " ELSE "") + cSecString
ELSE
"").
ASSIGN cQueryWhere = cQueryWhere + (IF pcSortPhrase <> "" THEN
" " + pcSortPhrase
ELSE
"").
/*
** Combine the query and the where clause
*/
ASSIGN cQuery = cQuery + cQueryWhere.
/*
** Open the query while dealing with batching etc as required.
*/
QUERY qEmployee:QUERY-PREPARE (cQuery).
QUERY qEmployee:QUERY-OPEN().
IF lBatching = TRUE THEN
DO:
BUFFER ttEmpSelection:BATCH-SIZE = piBatchSize.
IF iopcContext <> "" THEN
DATA-SOURCE srcEmployee:RESTART-ROWID = TO-ROWID(iopcContext).
DATASET pdsEmpSelection:FILL().
IF BUFFER ttEmpSelection:LAST-BATCH = FALSE THEN
ASSIGN iopcContext = STRING(DATA-SOURCE srcEmployee:NEXT-ROWID).
ELSE
ASSIGN iopcContext = "LASTBATCH".
END.
ELSE
DO:
/*
** Paul Mowat, 9 Jun 2016
** Making sure we set the batch-size to 0 when were are expecting to retrieve all the rows.
*/
BUFFER ttEmpSelection:BATCH-SIZE = 0.
DATASET pdsEmpSelection:FILL().
END.
BUFFER ttEmpSelection:DETACH-DATA-SOURCE().
QUERY qEmployee:QUERY-CLOSE().
/*------------------------------------------------------------------------------
Purpose: AfterRowFill logic for the fetch.
Notes:
------------------------------------------------------------------------------*/
METHOD PUBLIC VOID FetchEmpSelectionDS_AfterRowFill(INPUT DATASET pdsEmpSelection):
/*
** Set values as required.
*/
ASSIGN ttEmpSelection.fullname = SUBSTITUTE("&1, &2 &3",
TRIM(ttEmpSelection.surname),
TRIM(ttEmpSelection.etitle),
TRIM(ttEmpSelection.forename1))
ttEmpSelection.taxcodedisp = CreateTaxCode(ttEmpSelection.taxbasis,
ttEmpSelection.taxcode,
ttEmpSelection.taxletter,
ttEmpSelection.taxprefix)
ttEmpSelection.employind = ENTRY(LOOKUP(ttEmpSelection.employind, lv-empind-codes), lv-empind-descs).
END METHOD.
Thanks
Paul
So the question is really about the db query to populate the temp-table. There is no way to do that at the db query since the data is generated by your code and we would need to know the data for all records before you would sort them. You would have to populate the temp-table (let's called it ttCache) with all records and then run the query on ttCache to get the sorting you want. But then you would need to somehow cache that ttCache temp-table.
Just throwing this out there - You could actually have that ttCache temp-table be the data-source for your dataset. The ttCache table possibly just needs to have the calculated fields and the key to find the actual record in the db so you don't duplicate the records in both temp-tables. That would require more coding on your part, and the data would be out-of-date at some point - all things to consider.
Hi Fernando,
Thanks for your response.
The whole reason I'm using batching is to try and improve performance. Not sure having to build another temp-table with all the records would help with that as it can become quite a large table.
I can give it a go and do some bench marking if that's the only route i can take.
Regards,
Paul