Populating grid from 2 queries[Solved]

[antrax13]

Hi guys,

I have a grid with ID, Name columns.

Now in order to populate this grid I have to do 2 queries from DB.

So I select id and name from 1st table and set the dgData of group "TestGrid" to array1 ....

This works perfectly and data from 1st table will be in grid.

Next step is to populate this grid from 2nd table.

So I have again select id and name from 2nd table and set the dgData of group "TestGrid" to array2

Issue is that I will overwrite data from 1st query by 2nd query.

Code:

Code: Select all

 
 --table1 selection  
 put "SELECT id,name FROm table1" into tSQL
 put revQueryDatabase(gConnectionID, tSQL) into theCursor
   if theCursor is an integer then
      ConvertSQLCursorToArray theCursor, listArray
      put the result into theError
      if theError is empty then
         set the dgData of group "TestGrid" to listArray
      else
         answer theError
      end if
      revCloseCursor theCursor
   end if


   --table2 selection
   put "SELECT id,name FROM table2 " into tSQL
   put revQueryDatabase(gConnectionID, tSQL) into theCursor
   if theCursor is an integer then
      ConvertSQLCursorToArray theCursor, listArray
      put the result into theError
      if theError is empty then
         set the dgData of group "TestGrid" to listArray
      else
         answer theError
      end if
      revCloseCursor theCursor
   end if

What I need is something set the dgData of group "TestGrid" to listArrayFromFirstQuery + set the dgData of group "TestGrid" to listArrayFromSecondQuery OR

[antrax13]

Ok not sure if that is the only and proper way but I had to create 3rd array to populate grid with it.

[Klaus]

Hi Antrax13,

Issue is that I will overwrite data from 1st query by 2nd query.

did you REALLY exspect something different?

You need to combine the data from the two queries first and THEN set the dgdata!
Something like this:

Code: Select all

---
put query 1 into tArray1
...
put query 2 into tArray2
...
## Now do something like this (out of my head) to combine these two array to one array
...put the keys of tArray1 into tKeys
put the num of lines of tKeys into tNum

## We use repeat for each, so we need to manage our own counter
## We add all elements of tArray2 to tArray1
put tNum into tCounter
repeat for each line tKey in tKeys
  add 1 to tCounter
  put tArray2[tKey] into tArray1[tCounter]
end repeat
set the dgdata of grp "TestGrid" to tArray1
...

You get the picture...


Best

Klaus

[antrax13]

I was expecting that behavior but I was hoping there will be something easy like grab dgText and put it after dgText

This is also possible, but your script only use arrays and unless noted differently I always presume the "worst" case, which is a datagrid of type FORM

[Klaus]

Oh, sorry, I modified you posting instead of creating a new one