5 Replies Latest reply on Aug 22, 2010 7:14 AM by Deh Bontempos

    HELP Filling a datagrid with data from various tables

    Deh Bontempos

      MHI, this is simple.


      I have 2 tables.



      table ORDERS are columns:  order_id, client_id, status

      table CLIENTS are columns: client_id, client_name


      my datagrid would have the columsn: order_id, client_name, status.



      Thats all. I can't simply do it in Flex. HELP PLEASE.


      These are my approaches:

      1 - tried to create a new array collection with mixed data from these 2 tables to use as dataprovider in the datagrid.

      even the ac is [Bindable], the datagrid won't update. Probably Im creating the ac in a wrong way.


      2 - tried to use the ORDERS table call responder lastResult property (that works out to fill the data grid) and add a new colounm (client_name) within each item inside the ORDERS array collection.

      I'm not able to ADD a property/field/column inside the ac.  Of course, when I use .addItem, it will create a new "order"  not a new "order.property"

      if something like:  ac.source[i].push({client_name:clientName}) worked...


      My goals are simple. To fill the datagrid with those data.


      Ah.. i almost forgot...

      supose CLIENTS have 1000 registers.

      I don't have to bring all those registers within flex to look for only one ID to retrieve a name to fill the orders datagrid, right?






        • 1. Re: HELP Filling a datagrid with data from various tables
          thebouv Level 2

          Post some of your code, especially for your first approach.


          Your best bet is to send the correct data in from the very beginning; meaning, do an appropriate JOIN on your tables and send that combined data to Flex.  Why even bother combining it in Flex when it would probably be more effecient to send in the correct data from the beginning.


          Again, post some code for your first approach -- seems like that should work just fine, but you're just having a few issues getting the AC into the datagrid.

          • 2. Re: HELP Filling a datagrid with data from various tables
            Deh Bontempos Level 1

            Ok,  this is my first approach:

            this function is an auto-generated event that happens when I drag a databank operator over a datagrid. Datagrid's dataprovider IS set to "orders":


            (in my browser, the following messed lines only show decent while editting. Maybe copy it into a editor to better visualize: )


            protected function getOrders_pagedResult_resultHandler(event:ResultEvent):void             {                 orders = getOrders_pagedResult.lastResult                          //1 - this was previous declared as a Bindable Array Collection.                                             var ta:Array = new Array;                                 for (var t:String in event.result)                 {                     var tp:Array = new Array();                        tp.push(getOrders_pagedResult.lastResult[t].order_id);        //is it any difference to get data like in THIS LINE                           tp.push(event.result[t].status)                               //or THIS?                                             var cn:String = getClientsByID(event.result[t].client_id);     //this won't work in time. The getClientsByID delays to get data.                     var obj:Object = {client_name:cn};                             //so cn, in this line, will be "null". How can I handle this?                                         tp.push(obj);                                         ta.push(tp)                  }                                 orders.source.push(ta)                                             //this is what I wanted datagrid to show, but it doesn't.                                                                                    //if I leave the first statement, everything above is ignored                                                                                    //if I comment the first line, datagrid shows nothing.                            }


            I realize that the code above won't work because the properties inside each item won't have a "label" to datagrid to call in dataField property...I don't know how to do that. I thought by creating an object (like the obj above) it would work...  it wont.


            My getClientsByID is a modificated auto-generated function which doesn't work properly:


            (in my browser, the following messed lines only show decent while editting. Maybe copy it into a editor to better visualize: )

            protected function getClientsByID(itemID:int):String {         getClientsByIDResult.token = clientsService.getClientsByID(itemID);         return(getClientsByIDResult.lastResult.name); }


            It seems it doesn't work (returns null) because the function runs faster than the call responder result.

            Creating a listener for everything seems to be not the best practice, but a band-aid...



            I should create a class in php to return the "ready-to-use" data. Ok?


            Thanks a lot for your comments. Do I miss any important part of the code?



            • 3. Re: HELP Filling a datagrid with data from various tables
              Deh Bontempos Level 1

              Made it work.


              But I won't trust it. If any expert programmer may give a look, please:


              this is the workflow:


              1 -  declared bindable ordersAC  and clientsAC  (both array collection type)

              2 -  on datagrid creationcomplete, made a call responder that returns index 0 to 9 from ORDERS table.

              3 -  on the above result event, I created loop with a function to get clients_name from the CLIENTS table based on clients_id from ORDERS table.

              (ORDERS results are already stored in the getOrders_pagedResult.lastResult)

              4 -  getClientsByID() creates a call responder for every row incoming from the ORDERS table.

              5 -  after each result event, I store each client_name in clientsAC and I now can build the ordersAC with mixed results from both tables.

              See the code below.


              Again, it works, but I'm sure this is not the best practice:

              this is within the AS3 code script.

              my datagrid dataprovider is the ordersAC



              private var ordersAC:ArrayCollection = new ArrayCollection;
              private var clientsAC:ArrayCollection = new ArrayCollection;
              protected function dataGrid_creationCompleteHandler(event:FlexEvent):void
                   getOrders_pagedResult.token = ordersService.getOrders_paged(0,9);
              protected function getOrders_pagedResult_resultHandler(event:ResultEvent):void
                   for (var res:String in event.result)
              protected function getClientsByID(itemID:int):void
                   getClientsByIDResult.token = clientsService.getClientsByID(itemID);                               


              protected function clientsService_resultHandler(event:ResultEvent):void
                   var cn:String = event.result.name;               
                   ordersAC.addItem({order:getOrders_pagedResult.lastResult[clients.length-1].order_id , client_name:cn , status:getOrders_pagedResult.lastResult[clients.length-1].status});

              • 4. Re: HELP Filling a datagrid with data from various tables
                thebouv Level 2

                Seems like this approach might be a little cleaner, but it is just off the top of my head:


                1)  fetch the orders data and when result is returned, fetch the client data

                2)  when client data is finished fetching:  loop the orders data, which then loops through clients data for each client_id found to do a lookup.


                I have the code in my head and about half written right now, but my wife is bugging me to leave the computer so I'll post something later.  I just think this might be simpler than you're making it out to be.  I know I do that all the time! 



                Also, about the data coming in:  If it works, it works, but I still think your best bet is to return a JOINed dataset.


                For instance, I suppose you're getting your ORDERS data via something like:  SELECT order_id, client_id, status FROM orders;


                And something like this for your CLIENTS data:  SELECT client_id, client_name FROM clients;


                But you should be doing this to get the data combined before you even send it to SELECT orders.order_id, clients.client_name, orders.status FROM orders, clients WHERE orders.client_id = clients.client_id;


                However, that's assuming you have access to writing this SQL.  I know nothing of your backend system.  And we probably shouldn't go too far into that on the Flex forum. 

                • 5. Re: HELP Filling a datagrid with data from various tables
                  Deh Bontempos Level 1

                  The truth is that I'm a designer trying to make a "presentation" that merely "works".

                  Later a real programmer will take my work as a base.


                  I'm very limited in flex and php, however I worked with sql (in vb) where I could deal everything inside vb, and I fairly know AS3.

                  For flex, I have to know a server language (php, in my case) to write a query.  Flex generates classes in php to help me at this point.

                  So I have 2 classes: ClientServer.php  and OrderServer.php.


                  I tried to include ClientServer into OrderServer so I could build a query like this you are suggesting. But I couldn't even include one class into another.


                  if possible, take a look at how flex structures the php class and would you tell me how do I make a connection using 2 tables.


                  By the way, I'm afraid I will need to wait for your flex code to cleary understand what you mean by those 2 directions.


                  Thanks a lot again!