7 Replies Latest reply on May 28, 2015 6:50 AM by philmodjunk

    Help requested with structure of loop within loop and going into a portal

    VictorWarner

      Title

      Help requested with structure of loop within loop and going into a portal

      Post

      I am new to filemaker and I have imported a database from another database program, which consists of 3 tables. And I need  to replicate a particular type of table based on the 3 tables, I believe I need to use the Loop command (and perhaps a loop within the first loop, perhaps with an If statement too), but I cannot understand how to structure the script to produce what I need.  Help or pointers would be very gratefully receive.

      What follows is the briefest description of how the tables are created and what I am look to achieve I could manage.

      Table 1
      Catalogue number

      Table 2 
      Catalogue number, name of item, price of item 

      If the item has a number of sub-items then the price of item is a calculation (sum) based on the values of price of sub-item in Table 3. But not all items in Table 2 have sub-items.

      Table 3
      Name of item, sub-item name, price of sub-item

      I have created relationships between Table 1 and Table 2 (via Catalogue number), and Table 2 and Table 3 (via name of item). And Table 2 has a portal to Table 3.

      I need to create a 4th table (for onward export as a CSV) based on the possibilities :

      1. if no sub-item: Name of item, price of item
      2. if there is a sub-item and for first sub-item of name of item: (concentated) Name of item - sub-item name, price of sub-item
      3. for subsequent sub-items: sub-item name, price of sub-item

      An example: If there are two records in Table 2:

      001,Telephone,13.00
      002, Computer,24.00

      And for the second record there are a list of sub-items in Table 3:

      Computer, mouse, 12.00
      Computer, keyboard, 11.00
      Computer, USBcable, 1.00

      Then the result in the 4th Table would be:

      Telephone,13.00
      Computer-mouse,12.00
      keyboard,11.00
      USBcable,1.00

        • 1. Re: Help requested with structure of loop within loop and going into a portal
          philmodjunk

          If it wasn't for the hyphen, you wouldn't need a 4th table. Do you really need that hyphen between the item and the first sub item?

          • 2. Re: Help requested with structure of loop within loop and going into a portal
            VictorWarner

            Thank you for your response.

            If there is a way to distinguish between the two (such as a space at least), then I do not need a hyphen.

            • 3. Re: Help requested with structure of loop within loop and going into a portal
              philmodjunk

              By exporting using data from two related tables, you can get this:

              "Telephone",13.00
              "Computer", "mouse",12.00, 24.00
              "", "keyboard",11.00
              "", "USBcable",1.00

              It's not quite what you specified but avoids the need for any scripting at all let alone nested loops and a 4th table.

              • 4. Re: Help requested with structure of loop within loop and going into a portal
                VictorWarner

                Thank you very much. My problem is I need to provide the data to a third party in the way illustrated in my initial post. 

                In my database app I am moving from (DataPerfect), I was able to create a report which produced what I needed. With you suggested approach I would then need to engage in post-export formatting, something I was hoping not to have to deal with. 

                Would you be able to let me have  the outline structure at least of what I would need to do to create exactly what I need (such as exporting to 4th table)?

                 

                • 5. Re: Help requested with structure of loop within loop and going into a portal
                  philmodjunk

                  Just making sure that the added complexity is truly necessary as this other approach will be much easier.u

                  The data model (the the system of tables and relationships) you have set up, BTW, do not look like an optimum design unless there are other fields not shown. I see no purpose for Table 1 and Table 3 needs a catalog number field to serve as the link to table 2. You can, of course, match by item name, but this can set you up for problems should the name of an item need to be changed--the name change will disconnect it from any related sub-items. And this is easily avoided by not linking them by name.

                  But working with what you currently have in place, you can set up this relationship if you do not already have it:

                  Table 2::Name of Item = Table 3::Name of Item

                  From a Layout based on Table 2, you could run the following script:

                  Go to record/request/page [First]
                  Loop
                      If [ IsEmpty ( Table 3: Name of Item) ]
                           Set Variable [$Name ; value: Table 2::Name of Item ]
                           Set Variable [$Price ; value: Table 2::Price ]
                           Go To Layout ["Table 4" ; (Table 4) ]
                           New Record/Request
                           Set Field [Table 4::Name of Item ; $Name ]
                           Set Field [ Table 4::Price ; $Price ]
                      Else
                        Set Variable [$K ; value: 0 ]
                        Loop
                           Set Variable [$K ; value: $K + 1 ]
                           Exit Loop If [ $K > Count ( Table 3::Name of Item ) ]
                           Set Variable [$Name ; If ( Not IsEmpty ($Name ) ; $Name & "-" ) & GetNthRecord ( $K ; Table 3::SubItem Name ) ]
                           Set Variable [ $Price ; GetNthRecord ( $K ; Table 3::Price ) ]
                           Go To Layout ["Table 4" ; (Table 4) ]
                           New Record/Request
                           Set Field [Table 4::Name of Item ; $Name ]
                           Set Field [ Table 4::Price ; $Price ]
                           Set Variable [$Name ; value: "" ]
                           Go to Layout [original layout]
                       End Loop
                    End If
                  End Loop

                  Note, this script has not been tested so test carefully when first implementing it.

                   

                  • 6. Re: Help requested with structure of loop within loop and going into a portal
                    VictorWarner

                    Thank you for providing this code. 

                    I tried running it on my existing database and it did work as expected. In the end I created a database exactly as in my original post and it does not work on that too.

                    On running the script, it runs without stop, creating blank records in Table 4 (until the script is aborted). 

                    Further help would be very gratefully received. A copy of the database and script is here (Dropbox).

                     

                    • 7. Re: Help requested with structure of loop within loop and going into a portal
                      philmodjunk

                      How silly of me to leave out the needed steps near the end of the script:

                            Go to Layout [original layout]
                           End Loop
                        End If
                        Go to Layout [original layout]
                        Go to Record/Request/Page [next ; exit after last]
                      End Loop