Page 1 of 1

Using JOINS to build datagrid

Posted: Mon May 09, 2016 10:58 am
by nrprioleau
Good morning,

Is it possible to use joins to build columns in a datagrid? I have a "sales" datagrid that has a column productID, I want the datagrid to display the name. My code looks like this:

Code: Select all

put sqlquery_createObject("sales") into theQueryA
   sqlquery_set theQueryA, "related table joins", "LEFT OUTER JOIN customers, LEFT OUTER JOIN products"
   sqlquery_set theQueryA, "order by", "sales.sales_ID"

   sqlquery_retrieveAsRecords theQueryA, theDataA
   put the result into theError
   if theError is empty then
      set the dgData of group "salesDataGrid" to theDataA
   end if
   if theError is not empty then
      answer "Error populating customers:" && theError & "."
   end if
Columns in my datagrid for products would be products.prod_name for example. The products information is in the returned array but I don't know how to reference it.
Please help

Re: Using JOINS to build datagrid

Posted: Mon May 09, 2016 11:56 am
by nrprioleau
I have figured it out. I used retrieveAsArray instead of retrieveAsRecord.

However, now that I have all my data, I have noticed that there are two addresses related to the customer record but the returned data is only returning one address record. What am I doing wrong?

Re: Using JOINS to build datagrid

Posted: Tue May 10, 2016 1:41 pm
by trevordevore
Can you show me the query being generated by SQL Yoga? Just use

Code: Select all

set the clipboardData to sqlquery_get(theQueryA, "query")

Also, can you show me a small sample of what the

Code: Select all

array looks like? Here is a function that will print out the contents of an array.

Code: Select all

function PrintArray @pArray, pDimension, pFullData
   if pDimension is empty then put 0 into pDimension
   put the keys of pArray into theKeys
   sort theKeys numeric
   repeat for each line theKey in theKeys
      if pArray[theKey] is an array then
         put _printCharXTimes(space, pDimension * 5) & theKey & cr after theText
         put pArray[theKey] into theTempArray
         put PrintArray(theTempArray, pDimension + 1, pFullData) after theText
         if pFullData then
            put _printCharXTimes(space, pDimension * 5) &  theKey & ":" && pArray[theKey] & cr after theText
            put _printCharXTimes(space, pDimension * 5) &  theKey & ":" && line 1 of pArray[theKey] & cr after theText
         end if
      end if
   end repeat
   return theText
end PrintArray

private function _printCharXTimes pChar, pTimes
   local theStr
   repeat with i = 1 to pTimes
      put pChar after theStr
   end repeat
   return theStr
end _printCharXTimes