Announcement

Collapse
No announcement yet.

Using an Array in the Grid

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using an Array in the Grid

    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.

    Comment


    • #3
      Originally posted by kafecadm View Post
      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.
      Thanks kafecadm! I will give it a try and report back. Appreciate the direction very much!

      Comment


      • #4
        Originally posted by kafecadm View Post
        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.
        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,61 88134,6188134,9246065,' at line 1
        select count(*) from mytable where SubGroup IN 1005,1006,1011,6187426,6187426,6187437,6187437,618 8134,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.
        Last edited by mslatter; 02-19-2015, 10:15 AM.

        Comment


        • #5
          Originally posted by mslatter View Post
          I appreciate your help, and here's the report back.


          And my Grid SQL window calls for:

          WHERE
          SubGroup IN [sub_list]

          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!

          Comment


          • #6
            anytime =P

            Comment

            Working...
            X