3 Replies Latest reply on Oct 16, 2013 12:12 PM by disabled_JustinClose

    Export records, with related in nicely summarized manner

      Title

      Export records, with related in nicely summarized manner

      Post

           I am trying to achieve a record export to XLSX, with the resulting rows of the spreadsheet nicely organized based on a cascade of parent-child-child records. 

           Here are my relations (first 2 are 1-to-many, last one is 1-to-1):

           A::PK <1=m> B::A_FK

           B::PK <1=m>  C::B_FK

           C::itemID <1=1>  Items::Item_PK

            

           So there should be a nice cascade of records from TableA through to TableItems.  Essentially, TableA is a List, each List can have multiple sub-lists, and each sub-list contains a list of ItemIDs.  To get the Name of an Item, you go through the last stage to the TableItems table.

           My problem is with getting the export into XLSX to have a nicely grouped result.  I seem to always end up with things not organized correctly, and sometimes totally wrong data in a row.  I have put portals and fields on layouts based on each of the tables in question to look up/down the chain, and that information all appears to be correct and accurate.

           I would like the results to show information like this:

           

                A::List_ID1     List_Name     Sub-List1_ID    Sub-ListName     ItemID     ItemName

           

                                                                                                               ItemID     ItemName

           

                                                                                                               ItemID     ItemName

           

                                                           Sub-List2_ID    Sub-ListName     ItemID     ItemName

           

                                                                                                               ItemID     ItemName

           

                                                                                                               ItemID     ItemName

           

                A::List_ID2     List_Name     Sub-List1_ID    Sub-ListName     ItemID     ItemName

           

                                                                                                               ItemID     ItemName

           

                                                                                                               ItemID     ItemName

           

                                                           Sub-List2_ID    Sub-ListName     ItemID     ItemName

           

                                                                                                               ItemID     ItemName

           

                                                                                                               ItemID     ItemName

           It is essentially a sub-summary kind of thing, but a few layers deep.  I have seen this successfully done in Inspector Pro 4 (exporting records). 

           If I started my export process at Table A, I didn't really get related information showing up under it.  If I moved my starting point down a table, to Table B, then I would get better summary information for Table A's info.  But the final link, the ItemNames, would be messed up (i.e. ItemID 25 wouldn't be showing the proper ItemName).  If I moved further down the chain until Table C, and then I appeared to correctly get Item information exported in each row, but I also got each column of each row filled in with information.  Not TOO bad, but not really what I wanted in a nice single step.  Anywhere further up the relationship chain, though, seemed to always get me bad ItemName data.

           See attached screenshot of what I am trying to describe the output to be.

           Doing various version of 'Group By' only seems to yield less row information; i.e. if I group by Table A, then I only get one entry for each record in Table A, and no related records info.

           Any ideas?

           Thanks,

           --  J

      List_export.png

        • 1. Re: Export records, with related in nicely summarized manner
          philmodjunk

               I'm having trouble matching up the first part of your post to the example formats. Your first part documents 4 related tables. Your example formats appear to show only three. What happened to the fourth table?

               Testing with just 3 related tables does replicate the issues. Best I could do was export from Table C with fields specified from Tables A, B and C. That exported the correct data, but not in the format you have requested.

          • 2. Re: Export records, with related in nicely summarized manner

                 Oops...just realized that my Text version has an extra table listed.  There should only be four tables total, not 5.  In the spreadsheet version of what I demo'ed, I didn't include all the same columns as I did in the text version.  Will go edit the original.

                 For the Text version, it breaks down thusly:

                 ColA => TableA    ColB => TableA     ColC => TableB     ColD => TableB     ColE => TableC     ColF => TableItems

                  

                 For the spread sheet representation, it breaks down thusly:

                 ColA => TableA    ColB => TableB     ColC => TableC     Cold => TableItems

                  

            • 3. Re: Export records, with related in nicely summarized manner

                   I think I figured out why the final link, ItemNames, were not lining up correctly with the IDs:  the records in the Items table were being sorted by creation order.  And so were the records in the prior stage, the C::Item_PK fields.  But these two creation order didn't match up. 

                   I walked through a list of the IDs and compared them to the list of ItemNames I was getting, and they did match up.  So all the right data is coming through the import, it is just the matter of sorting them.

                   If I put a sort on the relationship from B to C, to sort the C:Item_PK fields, then things line up just fine.  However...the request for the export needs to keep these fields (from C) in their creation order.  (This creation order matches the visual order that the items are shown in on the UI, so we want to keep them that way.)  And the Items table, of course, can't be redone.  :)

                   So, our short term fix is to just copy the ItemName from ItemTable at the time of creating the records in C.  Then it doesn't matter.  Bit of an annoyance, though.

                    

                   --  J