7 Replies Latest reply on May 27, 2014 9:38 AM by philmodjunk

    Displaying data from child table on the same row as parent table data

    chelseagroup

      Title

      Displaying data from child table on the same row as parent table data

      Post

           Using a portal, I am able to provide my users with parent information, then display related information in a portal.  Using invoices as an example, I can show invoice information in the upper part of a report/form, with related line items displaying in a portal below: one row in the portal for each line item on the invoice.  When I save this as a spreadsheet, I get a row of invoice information, followed by a row for each line item with repeating invoice information suppressed.  Now my user wants to see a report that will save to a spreadsheet with invoice fields and (for example) all part numbers on the same row.

           Normally they would see for an invoice that had four line items:

           Invoice Number, Invoice date, Customer Name on one row, then part number and price.  This row would be followed by three more rows with part number and price.

           What they want to see now for the same invoice:

           Invoice number, invoice date, customer name, part number 1, part number 2, part number 3, part number 4 all on one row, with as many columns as necessary to show the ordered part numbers.  The part number is the only line item field they want to see. 

           I can't figure out how to get portal information on the same row as parent information.

            

           Can anyone help?

            

        • 1. Re: Displaying data from child table on the same row as parent table data
          philmodjunk

               Are they actually exporting to an excel file or is this "spreadsheet" a view of the data inside FileMaker?

               Either way, "as many columns as necessary" will be a potential problem. FileMaker can support as many related records (rows in a table view) as needed, but the number of columns will be finite. You can set things up to have a very large number of columns, but if an unusual case requires more than you have set up, you have a problem.

          • 2. Re: Displaying data from child table on the same row as parent table data
            chelseagroup

                 Thanks for asking.  Yes, they want to view the data as an excel spreadsheet.  They are analyzing the data for patterns of "part numbers" that occur together on invoices.  (We aren't really dealing with invoices and line items, but it is easier to get the point across using a simple example that everyone can relate to.)  The highest number of line items/part numbers that I have found is 16.  Of course, there could easily be more as time goes by.  My main problem is figurnig out how to get the portal data (part number) onto the same row as the invoice number.

                  

                 THis is my first post, so I am not sure that I am doing this correctly...I am posting an answer instead of adding a comment.  Maybe there is no "comment" action?

            • 3. Re: Displaying data from child table on the same row as parent table data
              philmodjunk
                   

                        I am posting an answer instead of adding a comment.

                   Sigh... We've asked the Right Now programmers repeatedly to rename the Post a new Answer box to something better like "Post Reply" as you are using the forum interface correctly when you use it to add another comment to the thread. For the longest time, it was not even grammatically correct as it used to read "Post a Answer". They then fixed the grammar but still have it set up with text that is misleading as to the intended function of this text box.

                   In order to get the data into columns for export, you'll need calculation fields that copy the data from the related table.

                   GetNthRecord ( RelatedTable::Field ; 1 )

                   will return the value of Field from the first related record in RelatedTable, so you could define a long series of calculation fields with GetNthRecord, specifying 1, 2, 3 and so forth as the second parameter passed to that function.

                   But what you describe, looking for part number patterns, sounds like something that could be set up on a list view layout in FileMaker and that, in turn might greatly simplify the whole process. With such a layout a horizontal portal technique could be used to produce multiple columns of data from a related table. A horizontal portal is a row of single row portals that are either setup with initial row settings of 1, 2, 3... or that use portal filter expressions to select which related record appears in that column.

              • 4. Re: Displaying data from child table on the same row as parent table data
                chelseagroup

                     I knew there had to be a technique that would allow me to do a horizontal portal!  I'm new to FMP development, but I'll see what I can discover about this.  So far I have found tutorials for fp5 and fp7.  Can you direct me to any documentation that can help me figure out how to set up a horizontal portal? This would be a much better solution than saving the data into a spreadsheet.

                • 5. Re: Displaying data from child table on the same row as parent table data
                  philmodjunk

                       If you know how to set up portals, this sums up what you need to know:

                       

                            initial row settings of 1, 2, 3... or that use portal filter expressions to select which related record appears in that column.

                       In portal setup, there are two settings that you can experiment with. You can set the initial row setting and number of rows setting to different values to see how you can set up single row portals that only display the 1st, 2nd, 3rd, etc rows. Or you can play with the portal filter expressions if you want the portal record in "category A" in column 1 and the portal record in "category B" in column 2.

                  • 6. Re: Displaying data from child table on the same row as parent table data
                    chelseagroup

                         Phil, you gave me enough information so that I was able to accomplish my objective.  Can't thank you enough!  There is one issue remaining.  When I look at my regular "invoice" layout, I see invoice information on the top of the screen, with line items appearing in a "normal" portal.  The portal rows are sorted ascending by part number.  When I create my collection of single row portals, the individual part numbers appear in essentially random order.  Since each "column" is a single filed portal, can you suggest a way to get the columns to appear in sorted order?  As I mentioned before,  the user wants to analyze  the data looking for patterns of "part numbers" that appear on multiple invoices, this is most easily accomplished by having the "part numbers" in ascending order.  Any suggestions?

                    • 7. Re: Displaying data from child table on the same row as parent table data
                      philmodjunk

                           Just sort all of your one row portals just like you did in the multi-row portal on your invoice layout.