Hi I don’t know if I’m the only one with the problem: I’m searching for a proper way of calculating a sum from several lines in a list form (like in the Excel status bar). Note: It should work on each visible numeric field on each listform. Currently I do the follwing: - mark the records - copy and paste them to Excel - mark the desired field range - see the sum in the status bar I want to know if there is a solution already before I try to solve it by Excel automation. Any help will be greatly appreciated. bye André
Hi Andre! Maybe you could do this: - Select the records you want to sum - Then trigger a function (F9, F11 ?), to calculate the sum: Function CalcMySum() **CurrForm.SETSELECTIONFILTER(MyRecordVar);** IF MyRecordVar.FIND('-') THEN // Calcualte Sums or any other stuff // ... REPEAT UNTIL MyRecordVar.NEXT = 0; - Show the result Regards, Jörg
quote:
Originally posted by stryk
Hi Andre! Maybe you could do this: - Select the records you want to sum - Then trigger a function (F9, F11 ?), to calculate the sum:Function CalcMySum() **CurrForm.SETSELECTIONFILTER(MyRecordVar);** IF MyRecordVar.FIND('-') THEN // Calcualte Sums or any other stuff // ... REPEAT UNTIL MyRecordVar.NEXT = 0;- Show the result Regards, Jörg
But I don’t know the fields. So I can’t calculate the sum by this way. Or do you know a way to get the current visible fields on runtime. And this in all listforms. I’m just testing a way to do this with Excel. We will see if it works. bye André
<—Edit----08/05/03 Hi I made some modifications to copy the records by code. Our accounting department is happy about this small tool. They said: They couldn’t imagine to work without this tool. Have fun. 08/05/03—Edit----> Hi All Ok. Now I’m able to present a solution. I tested it on three different forms (Item Ledger Entry/Cust. Ledger Entry/ Chart of accounts). It works (on my system [:p]). My solution works with an (hidden) Excel automation. The steps are: The user - selects the records he wants to summarize then the system - copies them to Excel - selects all Integer and Decimal columns - shows the user an optionsbox where he can select his desired field - shows the total of this field and offers another total of the selected records. So the system is able to calculate the sum of each visible field on each listform. All I need on the form is - one button to copy the records - one button to start the calculation Button **Copy** Visible on OnOpenForm **OnPush()** *<---Edit----* CurrForm.SETSELECTIONFILTER(Rec); IF Rec.COUNT < 2 THEN BEGIN MARKEDONLY(FALSE); CLEARMARKS; IF Rec.COUNT < 2 THEN BEGIN xRec.GET(Rec."No."); //customize for each form!!! CLEAR(Rec); Rec.GET(xRec."No."); //customize for each form!!! END; ERROR('Please select at least two records!'); END ELSE BEGIN GetSum.SendCopy; MARKEDONLY(FALSE); CLEARMARKS; CurrForm.Copy.VISIBLE(FALSE); CurrForm.GetSum.VISIBLE(TRUE); END; *---Edit---->* Button **GetSum** Hidden on OnOpenForm **OnPush()** GetSum.GetSum; CurrForm.Copy.VISIBLE(TRUE); CurrForm.GetSum.VISIBLE(FALSE); **CodeUnit GetSum** Name DataType Subtype Length xlApp Automation 'Microsoft Excel 9.0 Object Library'.Application xlBooks Automation 'Microsoft Excel 9.0 Object Library'.Workbooks xlBook Automation 'Microsoft Excel 9.0 Object Library'.Workbook xlSheets Automation 'Microsoft Excel 9.0 Object Library'.Worksheets xlSheet Automation 'Microsoft Excel 9.0 Object Library'.Worksheet xlRange Automation 'Microsoft Excel 9.0 Object Library'.Range OpStr Text 400 SearchStr Text 500 SearchStrI Text 500 tmpTXT Text 50 i Integer ix Integer cTxT Text 30 ColumnFirst Integer Letters Text 30 column Integer **SendCopy()** CLEAR(xlApp); IF CREATE(xlApp,TRUE)THEN; xlApp.SendKeys('%BK'); //Copy Selection German! English: '%EC'?? xlApp.Quit; **GetSum()** CLEARALL; IF CREATE(xlApp,TRUE)THEN; xlApp.Workbooks.Add; xlBook:=xlApp.ActiveWorkbook; xlSheet:=xlBook.ActiveSheet; xlRange:= xlSheet.Range('A1'); xlSheet.Paste; xlRange.Select; i:= 0; REPEAT IF (xlRange.Offset(1,0).Value.ISDECIMAL) OR (xlRange.Offset(1,0).Value.ISINTEGER) THEN BEGIN i:= i + 1; IF OpStr = '' THEN OpStr:=FORMAT(xlRange.Value) ELSE OpStr:= OpStr + ',' + FORMAT(xlRange.Value); SearchStr:= SearchStr + '#'+FORMAT(i) + FORMAT(xlRange.Value); //instead of dimensions (>10?) END; xlRange:= xlRange.Offset(0,1); xlRange.Select; UNTIL FORMAT(xlRange.Value) = ''; REPEAT xlRange:= xlSheet.Range('A1'); xlRange.Select; i:= STRMENU(OpStr); IF i <> 0 THEN BEGIN IF i > 9 THEN ix:= 3 ELSE ix:= 2; SearchStrI:= COPYSTR(SearchStr,STRPOS(SearchStr,'#'+FORMAT(i))+ix); SearchStrI:= COPYSTR(SearchStrI,1,STRPOS(SearchStrI,'#'+FORMAT(i+1))-1); END; IF FORMAT(xlRange.Value) <> SearchStrI THEN REPEAT xlRange:= xlRange.Offset(0,1); xlRange.Select; UNTIL FORMAT(xlRange.Value) = SearchStrI; column:=xlRange.Column; cTxT:= GetCol(column); xlRange:= xlSheet.Range(cTxT+':'+cTxT); tmpTXT:= FORMAT(xlApp.WorksheetFunction.Sum(xlRange)); UNTIL CONFIRM('Total of selected records\'+ '------------------------------\'+ 'Field: '+SearchStrI+'\'+ 'Total: '+tmpTXT+'\'+ '\'+ 'Do you need another total of\'+ 'your selection?',FALSE) = FALSE; xlBook.Close(FALSE); xlApp.Quit; CLEARALL(); **GetCol(VAR Column : Integer) : Text[2]** //Thanks to Marcus Fabian BEGIN IF Column > 26 THEN BEGIN ColumnFirst := Column DIV 26; Column := Column MOD 26; END ELSE ColumnFirst := 0; Letters :='ABCDEFGHIJKLMNOPQRSTUVWXYZ'; IF ColumnFirst <> 0 THEN EXIT (STRSUBSTNO('%1%2',Letters[ColumnFirst],Letters[Column])) ELSE EXIT (STRSUBSTNO('%1',Letters[Column])); END; Perhaps it ins’t the best way to get the result but you get a result [8D]. And this much faster than to copy/paste it by hand or to put it in the calculator. If somebody knows how this could be solve better please post it. bye André