7 Replies Latest reply on Apr 7, 2017 6:41 AM by meyerge

    List only returning first row of portal data

    meyerge

      Hey Everyone,

       

      I have an order entry form that has an attached portal for Line Items. In a previous post i had asked how to get the data from the form into a csv format that I could import into our accounting system. As per the suggestions I created a new field and used the Substitute (List(Table1::Field1; Table1::Field2 etc); "¶"; ",") to place the data into the field for export. Which works great

       

      Now, the problem I am having is that I need to get the line items in the same format. I followed suggestions from other queries and created a layout from the portal table, scripted a find to return the correct record set and used the same Substitute ( List ()) command to assimilate the data. When I debug the script the layout shows all the records but the List () command only shows the first row of data. Is the List function suppose to return an array or just one row?

       

      This is what the layout shows:

       

      PartNo     Qty     Price

      A               1          $

      B               2          $

      C               3          $

       

      The list command only returns A,1,$.

       

      Could you please let me know what I am doing wrong and let me know if I misunderstand how List is supposed to work?

       

      Thanks for your time.

       

      Gordon

       

      FMP15.0.3.305 running on latest FileMaker server 15

        • 1. Re: List only returning first row of portal data
          philmodjunk

          List does not return an array. It produces a list of values separated by returns.

           

          But if your layout is based on LineItems, then

           

          List ( LIneItems::field )

           

          will only return the value of LineItems::field for the current record--in your case, that would be the first record.

           

          List ( value1 ; value2 ; value3 ) will return a list of up to 3 items separated by returns

           

          List ( RelatedTable::field ) will return a list of values from field over the full set of related records.

           

          So it's a matter of context.

           

          Either use a "list of" summary field with your current layout to get your list or use list from the context of a layout based on the parent table, your invoice table rather than a layout based on the items.

          • 2. Re: List only returning first row of portal data
            rgordon

            To add to Phil's comments you do not want a Substitute when you compile all of the line items with the second List.  The Substitute was initially used to change the ¶ to commas in the first list.  In the second list you want to create you want the ¶ because it creates a new line for each record.

            • 3. Re: List only returning first row of portal data
              meyerge

              Thank you for the response. So what I did was open the order entry form searched for an order that has multiple line items. (I believe this should be the record set that the script will perform the operations on.)

               

              The script has been changed to

                   Set Field (z_tempTable::csvLineItems ;

                        List ( RelatedTable::PartNo ; RelatedTable::Qty ; RelatedTable ::Price ).

               

              Stepping through the script I still only get the first row of the portal records. Not the three that are associated with the order that show up on the order entry form.

               

              I must still be missing something.

               

              Gordon

              • 4. Re: List only returning first row of portal data
                philmodjunk

                Are you on the layout with the portal when these script steps execute?

                 

                Is "relatedTable" what is specified in "show related records from" in portal set up?

                 

                Part number, Qty, and Price should be combined in a single field in your line items table so your list function "lists" a single field. Either that or use ExecuteSQL to produce your list instead so that you can use a comma as the field separator.

                • 5. Re: List only returning first row of portal data
                  rgordon

                  Here is a sample file that I think will show you how to get the results you want.

                  • 6. Re: List only returning first row of portal data
                    meyerge

                    Yes, I am on the layout that contains the portal when I execute the script and the related table is the one that is listed in portal setup.

                     

                    As a point of clarification List () will only return a “Single” column list.

                     

                    Using the following table as an example:

                     

                    Field     A     B     C

                              1     2     3

                              4     5     6

                     

                    List (Table::FieldA) will result in: 1¶4¶

                     

                    And List (Table::FieldA ; Table::FieldB ; Table::FieldC) will result in: 1¶2¶3¶

                     

                    It will not return an array of:

                         1¶2¶3¶

                         4¶5¶6¶

                     

                    Am I understanding this function correctly?

                     

                    Thanks again for your help!

                    • 7. Re: List only returning first row of portal data
                      meyerge

                      Thanks for the file. To recap I will create an extra combined field in my Invoice table that is a calculation Substitute ( List (partNo field ; Quantity Field ; Price Field) “¶” ; “,”). The combination field will be referenced in the script List (Combined Field).

                       

                       

                      Much appreciated.