AnsweredAssumed Answered

Export records, with related in nicely summarized manner

Question asked by disabled_JustinClose on Oct 15, 2013
Latest reply on Oct 16, 2013 by disabled_JustinClose

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

Outcomes