2 Replies Latest reply on Jul 10, 2007 6:51 PM by hylim

    Complex database display for Datagrid

      Hi to all,

      I am new to Flex (& Web GUI design).
      I am trying (unsuccessfully) to develop a display that monitors the condition of items at different locations in the form of a datagrid.
      The database system provided to me basically use a "many-to-many" relationship to store the data:
      1. Table Item: item_id
      2. Table Appl: appl_id, location_name
      3. Item-Appl Mapping Table: maps item_id to appl_id
      Basically, 1 item is being monitored by several Appl at different locations; while 1 Appl can also monitor multiple Items.

      In my design, I have to continously query the database to obtain the current condition of the Item, where I try to obtain the following information:
      Notification table: item_id, Appl_id, Time, Type
      ; where Type indicates the change in the Item (item_id) condition at location (Appl_id).
      Based on this query, i need to update the item status on the Datagrid

      The main issue here is:
      1. How do I design the dataProvider for the dataGrid (Item vs Location)
      - I have to dynamically create the dataGrid Column since the location can be dynamically changed/added/removed from the database.
      My dataGrid should look like this :
      Item "A" "B" "C"
      M X O O
      N O O X
      R O O O

      "A","B", "C" are Location names; which are user-inputs & I do not have any information about it beforehand. I have to obtain this locations from the location_name in Appl Table.
      The X,O indicates the condition of the Item.

      Has anyone done something similar to this before & point me to the right direction?

      Many thanks,

        • 1. Re: Complex database display for Datagrid
          ntsiii Level 3
          Well, your "item"s are the rows. So on the server, query the database by item, returning the locations as "columns". I'll leave that sql to yu, but it shouldn't be too hard. Actually, if it was me, I might use multiple queries to do that, but my sql isn't all that great.

          Build an xml string, and send that bvack to Flex. Use e4x as the resultFormat of the HTTPService call.

          You can build the columns dynamically based on information in the data. I have a simple example on cflex.net. Search for "dynamic columns".

          • 2. Re: Complex database display for Datagrid
            hylim Level 1
            Very much thanks for the reply.

            In my case, more than 1 Appl can monitor 1 item at same location (which can be a big hierarchy of places: A->AA->AAA).
            E.g.: Appl #1 monitor Item M at Location A.AA & Appl #2 monitor Item M at Location A.AB. I need to display the overall result for Location A (Location A.AA and A.AB)
            This means that I need to store the result 1st somewhere in my cache & then do some post-query processing to obtain my final result.
            What would be the best arrangement to store this data & how can I set it as my dataProvider to the dataGrid?

            Thank you very much.