P
Paul Mowat
Guest
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
ayrollListQuery(). /* ** 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
ETACH-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
Continue reading...
ayrollListQuery(). /* ** 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 ttEmpSelectionETACH-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 PaulContinue reading...