Using an Array in the Grid

Applications Grid / Reports
#1

Good morning,

Still a newbie here… I’ve come a long way with my applications, but now I’ve come to a road block. Simplified, my current application is like this:

Table1

  • Account number
  • SubGroup number

Table2

  • SubGroup number
  • Widget sales

Table 2 holds literally thousands of Widget Sales, and my grids work great by pulling, for example, the widget sales related to Account number 1 (WHERE SubGroup = SubGroup)…very simple.

But now if the Account Number has several SubGroups of its own, how can I pull the total Widget Sales into a Grid for a single Account number? Again, my current example is looking up Account number 1, then pulling the Widget sales for the one SubGroup number associated with Account number 1. But now I need to query for the total Widget sales for ALL SubGroups belonging to Account number 1.

I found an example online for a regular MySql script, but can’t grasp how to apply it to Scriptcase. Essentially the example queries for the SubGropus belonging to Account Number 1 and assigns them to an array. But how do I write my SQL code in the grid (SQL section under Grid properties), to instead of looking for [SubGroup], to look for the new array, which I assume I pull in “onApplicatinoInit”?

Thanks very much for any directives or examples.

Mark.

#2

change your SQL statement for the grid to:

[SQL]

SELECT *
FROM mytable
WHERE subgroup IN ([g_Subgroup]);

[/SQL]

here [g_Subgroup] should be equal to the subgroups separated by comma.

#3

[QUOTE=kafecadm;32888]change your SQL statement for the grid to:

[SQL]

SELECT *
FROM mytable
WHERE subgroup IN ([g_Subgroup]);

[/SQL]

here [g_Subgroup] should be equal to the subgroups separated by comma.[/QUOTE]

Thanks kafecadm! I will give it a try and report back. Appreciate the direction very much!

#4

[QUOTE=kafecadm;32888]change your SQL statement for the grid to:

[SQL]

SELECT *
FROM mytable
WHERE subgroup IN ([g_Subgroup]);

[/SQL]

here [g_Subgroup] should be equal to the subgroups separated by comma.[/QUOTE]

I appreciate your help, and here’s the report back.

onScriptInit =

$sql_area = “SELECT SubGroup FROM Table1 WHERE (SubGroup = “. 1234567 .”)”;

sc_lookup(rs, $sql_area);
if(count({rs}) !== 0)
{
$subs = array_map(function($item) { return $item[0]; } , $rs);
[sub_list] = implode(’,’,$subs);
}

And my Grid SQL window calls for:

WHERE
SubGroup IN [sub_list]

But when I run the application, I get:

Error
Error while accessing the database:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘1005,1006,1011,6187426,6187426,6187437,6187437,6188134,6188134,9246065,’ at line 1
select count(*) from mytable where SubGroup IN 1005,1006,1011,6187426,6187426,6187437,6187437,6188134,6188134,9246065,9246041,6188132,6188132

The positive thing is that the query is indeed grabbing all the SubGroups, but I assume the Grid SQL is not parsing it correctly. I’ll keep working at it because it’s probably an oversight on my part.

Thanks again!

Mark.

#5

[QUOTE=mslatter;32943]I appreciate your help, and here’s the report back.

And my Grid SQL window calls for:

WHERE
SubGroup IN [sub_list]

[/QUOTE]

I was right - it was my oversight to what you had even given me - sorry. Should have been (which works):

WHERE
SubGroup IN ([sublist])

I didn’t include the brackets…argh. Thanks so much for your help!

#6

anytime =P