How can I access all rows from a form/grid?

[madMatti]

Question:

What is the recommended way to access the row data in this case?
Do I have to use an additional SQL query even though all the data is already loaded?
Is there maybe some aggregate function like in the report builder that I could use instead?

Problem:

I have a GUI form with a grid containing sums. Now I would like to calculate a total sum of those sums and display the result in a separate text field. All the numbers are loaded and I can iterate over columns of the current dataset. But I can’t find a way to look at other rows (without setting the cursor using the mouse). Maybe there is a way to cycle the cursor through the rows in a loop?

---

Example:

Rows.odb

This example does not make much sense, but it’s just an example. The relevant form is Pets, the relevant macros are in the module frm_pets

Datasource of the grid table:

SELECT pet_name, "P".pet_id, breed_name, type_name, owner_name, "P".owner_id 
FROM Pets AS "P" 
LEFT JOIN Owners AS "O" ON "P".owner_id = "O".owner_id 
LEFT JOIN "Breeds" AS "B" ON "P".breed_id = "B".breed_id 
LEFT JOIN "Types" AS "T" ON "B".pet_type = "T".type_id ;

Relevant Macro for the example:

Sub UpdateSums()
	Dim mainForm As Object
	mainForm = utils.GetGuiForm(FORM_NAME, GUI_FORM_MAIN)

	Dim tblPets As Object
	Dim colType As Object
	tblPets = mainForm.getByName("tbl_pets")
	colType = tblPets.getByName("col_type_name")
	
	REM MsgBox colType.getCurrentValue()
	
	Dim counterTotal As Integer
	Dim counterDogs As Integer
	Dim counterCats As Integer
	Dim counterHorses As Integer
	
	counterTotal = mainForm.RowCount
	
	Dim tfTotal As Object
	Dim tfDogs As Object
	Dim tfCats As Object
	Dim tfHorses As Object
	tfTotal = mainForm.getByName("tf_total")
	tfDogs = mainForm.getByName("tf_dogs")
	tfCats = mainForm.getByName("tf_cats")
	tfHorses = mainForm.getByName("tf_horses")
	tfTotal.Text = counterTotal
	tfDogs.Text = counterDogs
	tfCats.Text = counterCats
	tfHorses.Text = counterHorses
End Sub

I wish I could do something like this (pseudocode) :

Dim row As Object
Dim currentType As Object
For Each row In mainForm.Rows
  currentType = row.getByName("col_type_name").Value
    Select Case currentType
      Case "Dogs"
        counterDogs = counterDogs + 1
      Case "Cats"
        counterCats = counterCats + 1
      Case "Horses"
        counterHorses = counterHorses + 1
    End Select
Next

[Ratslinger]

Hello,

Even though you see the data on a grid does not mean you can access it directly. What is available is the selected row. So to get each item you need to cycle through each row. Have done his years ago and abandoned it. It shows (especially when records exceeds visual grid) the cycling through of records - annoying and distracting.

Best to simply use (within existing macro) SQL to retrieve the data from the View such as:

Select “type_name”, count(“type_name”) from “vPetsList” Group By “type_name”

You can add a WHERE clause for selective owners.

[madMatti]

Ah, pity. I hoped I could access the databinding layer or something. Because all the query data is loaded into memory, there are no DB transactions going on when changing cursors (or not all data, only lazy loaded for the current view?).

I made another view just for that request (because it’s a bit more complex than the pets example). Thanks for the pointer.