1 2 Previous Next 15 Replies Latest reply on Sep 21, 2010 9:04 AM by philmodjunk

    Loop same record

    MikeGraham

      Title

      Loop same record

      Post

      Hello,

       

      I’m trying create a loop that will reprint print a label of a known quantity of the same product sku before moving on to the next product inline.

      “Qty_Recd” is the field that contains the total for each label. 

      The following fields are the fields to print. They are found based on a Relative Purchase Order receipt ID (which is all pretty straight forward.)

       “Product_ID”

      “Attrib_2”

      “Attrib_4”

       Have done this in Basic and later in Visual Basic but moving on I'm trying to get a handle on Filemaker syntax.

       

      Any insight would be greatly appreciated.

       

      Mike 


        • 1. Re: Loop same record
          philmodjunk

          While you can use a loop to generate a separate print job for each item, that shouldn't be necessary with filemaker. If these "labels" form a single column, you can specify "page break after every 1 occurrence" for the body of your layout and then can print the whole set (using Records being browsed option)--with each record being printed on a separate page.

          If these items are listed in a portal, you'd first pull them up in a found set on a layout based on the portal's table for printing.

          • 2. Re: Loop same record
            MikeGraham

            Phil,

            Thanks for the comment.  The labels run across 8 wide. 80 per sheet.

            The Portal is and interesting thought, I'll explore that some.

            Below is an example of how the output needs to work.

            • 3. Re: Loop same record
              philmodjunk

              You can set up your layout to print in columns. This should give you what you want here if each of the above "labels" is a separate record.

              Multiple column out put can be specified in Layout Setup.... | Printing

              • 4. Re: Loop same record
                MikeGraham

                That is the set up I currently have, 8 columns. hence the output above in the attached image.

                Each label is a separate record but I need to multiply each by the red number shown under each record. Like in the example of "21059,dwa,34" at the bottom. it needs to be printed 10 times. "21059,dwa,32 need to print 15 times...

                Thanks

                • 5. Re: Loop same record
                  philmodjunk

                  OK, Got it finally.

                  Most developers use a special print table for this. You then create one record in this print table for each label. The simplest approach is to link the records to the original table's primary key so that you merely create the new record and assign the primary key's value to it so that it now displays data from the correct record. You can do this in a loop where the number in red is compared to your loop counter to get the correct number of records in your label printing table.

                  • 6. Re: Loop same record
                    MikeGraham

                    Thanks Phil,

                    I think the loop is where I am getting stuck. I tried setting up a counter with no luck. below is my current idea but still not getting the result. The Variable $n is set from the Qty_recd (number in red above) 

                    Not sure if I'm on the right track with this...

                    Mike

                    Script for print Layout;

                    Set Error Capture [On]

                    Go to [Layout #48 {Receiver_Items}]

                    Enter Find Mode [Pause]

                    Perform Find []

                    Set Variable [$n; Value:Receiver_Items::Qty_Recd]

                    Go to Record/Request/Page [First]

                    Loop

                    Exit Loop if [Receiver_Items::Qty_Recd = $n]

                    End Loop

                    Go to Record/Request/Page [Next; Exit after last]

                    Enter Preview Mode []

                    • 7. Re: Loop same record
                      philmodjunk

                      You've got several problems there.

                      Go To Record/Request/Page [Next ; Exit after last]

                      Is generally used inside a loop so that the exit after last will exit the loop after the last record in the found set is processed.

                      If you use a variable such as $n in your script, it should be either incremented or decremented so that it reaches a value you can test with exit loop that tells the script to exit the loop.

                      Here's the script I think you need:

                      Go to [Layout #48 {Receiver_Items}]
                      Enter Find Mode [Pause]
                      Set Error Capture [On]
                      Perform Find []
                      If [ Get ( FoundCount > 0 ) /* records where found */]
                             Go to Record/Request/Page [First]
                             Loop
                                     Set Variable [$n; Value:Receiver_Items::Qty_Recd]
                                     Set Variable [$ID; Value: Receiver_Items::PrimaryKeyField]
                                     Go To Layout ["Labels" (Labels)]
                                     Loop
                                             New Record/Request
                                             Set Field [Lables::ID ; $ID]
                                             Set Variable [$n ; Value: $n - 1]
                                             Exit Loop if [ $n = 0]
                                     End Loop
                                     Go To Layout [Original Layout]
                                     Go To Record [next ; Exit after last]
                             End loop
                             Go To Layout ["Labels" (Labels)]
                             Enter Preview Mode [Pause]
                             Enter Browse Mode
                             Delete All Records [No Dialog]
                      Else
                             Show Custom Dialog ["No records were found"]
                      End If

                      This script assumes you have a labels layout that refers to a utility table called Labels with the following relationship linking it to Receiver_Items:

                      Lables::ID = Receiver_Items::PrimaryKeyField

                      This will allow you to place all the needed fields from Receiver_Items on your Labels layout and they'll show the correct data when the set field step in the script assigns the primary key value from Receiver_Items to Labels::ID.

                      • 8. Re: Loop same record
                        MikeGraham

                        Phil,

                        Thanks a lot for all this effort!

                        I think its almost working. I have your script below as I entered it.

                        I Used the the "Receiver_ID" as the primary Key but it only prints the receiver number, although the proper amount of times. so I know the Loop is working. 

                        it seems obvious that I need other fields besides the ID on the print layout. but when I add them they are just ignored. With the two tables associated shouldn't I be able to call data from one to the other using other Set Field commands?

                        Go to Layout [ “Layout #48” (Receiver_Items) ] 

                        Enter Find Mode [  ] 

                        [ Pause ] 

                        Set Error Capture [ On ] 

                        Perform Find [  ] 

                        If [ Get ( FoundCount ) > 0 ] 

                        Go to Record/Request/Page 

                        [ First ] 

                        Loop 

                        Set Variable [ $n; Value:Receivers::Qty_Rec'd ] 

                        Set Variable [ $ID; Value:Receivers::Receiver_ID ] 

                        Go to Layout [ “Layout #49” (Labels) ] 

                        Loop 

                        New Record/Request 

                        Set Field [ Labels::ID; ($ID) ] 

                        Set Variable [ $n; Value:$n - 1 ] 

                        Exit Loop If [ ($n = 0) ] 

                        End Loop 

                        Go to Layout [ original layout ] 

                        Go to Record/Request/Page 

                        [ Next; Exit after last ] 

                        End Loop 

                        Go to Layout [ “Layout #49” (Labels) ] 

                        Enter Preview Mode 

                        [ Pause ] 

                        Enter Browse Mode 

                        Delete All Records 

                        [ No dialog ] 

                        Else 

                        Show Custom Dialog [ Message: "No records were found"; Buttons: “OK”, “Cancel” ] 

                        End If 


                        • 9. Re: Loop same record
                          philmodjunk

                          I can see that you are using a layout that refers to Reciever_Items, but your set variable steps refer to Receivers. This is dangerous. Even though Receivers and Receiver_Items may be boxes on your Relationship graph that refer to the same data source table, they have different current records, found sets and sort orders. Since you are performing a find on a layout that refers to Receiver_Items, your set variable steps should refer to Receiver_Items as well. The following steps assume you have a relationship between reciever_items and Labels, not Receivers and Labels.

                          If you have defined the relationship I described between the two tables, you can simply do this to add the other fields to your labels:

                          1. Enter layout mode, drag from the field tool to the location in your label where you want the field to be located.
                          2. Select Receiver_Items (not receivers) from the drop down, then select the desired field from the list of fields.

                           

                          If you want to use merge text, you can simply select the text tool, click on your layout, select insert merge field and make the same selection as above.

                          For more about Table Occurrences (the boxes on your relationship graph) and how FileMaker uses them, see this thread:  

                          Tutorial: What are Table Occurrences?

                          • 10. Re: Loop same record
                            MikeGraham

                            Phil,

                            Good eye I failed to notice I was grabbing the the other table. I corrected the Relationship to the Items table. Added the field to the layout, When I run the Script it runs through one set then stops showing two records. it's the correct number of labels for that record but it's curious its out of sort order. now the issue seems to be we're not advancing to the next record.

                            Go to Layout [ “Layout #48” (Receiver_Items) ] 

                            Enter Find Mode [  ] 

                            [ Pause ] 

                            Set Error Capture [ On ] 

                            Perform Find [  ] 

                            If [ Get ( FoundCount ) > 0 ] 

                            Go to Record/Request/Page 

                            [ First ] 

                            Loop 

                            Set Variable [ $n; Value:Receiver_Items::Qty_Recd ] 

                            Set Variable [ $ID; Value:Receiver_Items::Receiver_ID ] 

                            Go to Layout [ “Layout #49” (Labels) ] 

                            Loop 

                            New Record/Request 

                            Set Field [ Labels::ID; ($ID) ] 

                            Set Variable [ $n; Value:$n - 1 ] 

                            Exit Loop If [ ($n = 0) ] 

                            End Loop 

                            Go to Layout [ original layout ] 

                            Go to Record/Request/Page 

                            [ Next; Exit after last ] 

                            End Loop 

                            Go to Layout [ “Layout #49” (Labels) ] 

                            Enter Preview Mode 

                            [ Pause ] 

                            Enter Browse Mode 

                            Delete All Records 

                            [ No dialog ] 

                            Else 

                            Show Custom Dialog [ Message: "No records were found"; Buttons: “OK”, “Cancel” ] 

                            End If 


                            • 11. Re: Loop same record
                              philmodjunk

                              Cleaned up, your script looks like this:

                              Go to Layout [ “Layout #48” (Receiver_Items) ]
                              Enter Find Mode [ Pause ]
                              Set Error Capture [ On ]
                              Perform Find [ ]
                              If [ Get ( FoundCount ) > 0 ]
                                     Go to Record/Request/Page[ First ]
                                     Loop
                                             Set Variable [ $n; Value:Receiver_Items::Qty_Recd ]
                                             Set Variable [ $ID; Value:Receiver_Items::Receiver_ID ]
                                             Go to Layout [ “Layout #49” (Labels) ]
                                             Loop
                                                     New Record/Request
                                                     Set Field [ Labels::ID; $ID ]
                                                     Set Variable [ $n; Value:$n - 1 ]
                                                     Exit Loop If [ $n = 0 ]
                                             End Loop
                                             Go to Layout [ original layout ]
                                             Go to Record/Request/Page [ Next; Exit after last ]
                                     End Loop
                                     Go to Layout [ “Layout #49” (Labels) ]
                                     Enter Preview Mode[ Pause ]
                                     Enter Browse Mode
                                     Delete All Records [ No dialog ]
                              Else
                                     Show Custom Dialog [ Message: "No records were found"; Buttons: “OK”, “Cancel” ]
                              End If

                              When I run the Script it runs through one set then stops showing two records. it's the correct number of labels for that record but it's curious its out of sort order. now the issue seems to be we're not advancing to the next record.

                              There is no sort order specified in the above script so I don't see how it can be "out of sort order". If you need a specific order, you'll need to add a step after the Perform Find step. The script looks correct. If it's "not advanced to the next record", I suspect there is no record to advance to after the find. If you have a copy of FileMaker Advanced, run the script with the debugger enabled and step watch what happens step by set. If you don't have advanced, insert either some Pause/Resume steps or Show Custom Dialog steps in strategic locations such as right after the Perform find step and then run the script to see what is happening.

                              • 12. Re: Loop same record
                                MikeGraham

                                There does seem to be more than just one record set based on this Browse mode I added with a pause.

                                Continuing to the preview 

                                I'll look into getting the Debuging tool. When Pause the script in various places it seems to just jump out after the first set.

                                Anyway a Big thanks for all the help...

                                MIke 

                                • 13. Re: Loop same record
                                  philmodjunk

                                  You might try changing Go To Layout [Original layout] To Go To Layout [ Layout #48]

                                  That seems to be where things are breaking down. Prior to making that change, you might even insert a pause immediately after that go to Layout step to see if it's returning to the correct layout.

                                  • 14. Re: Loop same record
                                    MikeGraham

                                    Phil,

                                    I Got it working right now. yeah!

                                    I did need to call out the layout instead of using [Original layout] but still had an issue of pulling in the same field info for all labels.

                                    I know this looks Awkward but Defining and Setting all those Variables did the trick...

                                    Thanks A Ton!

                                    Go to Layout [ “Layout #48” (Receiver_Items) ] 

                                    Enter Find Mode [  ] 

                                    [ Pause ] 

                                    Enter Browse Mode 

                                    [ Pause ] 

                                    Set Error Capture [ On ] 

                                    Perform Find [  ] 

                                    Sort Records [ Specified Sort Order: Receiver_Items::Product_ID; ascending 

                                    Receiver_Items::Attrib_2; ascending 

                                    Receiver_Items::Attrib_4; ascending ] 

                                    [ Restore; No dialog ] 

                                    If [ Get ( FoundCount ) > 0 ] 

                                    Go to Record/Request/Page [ First ] 

                                    Loop 

                                    Set Variable [ $ID; Value:Receiver_Items::Receiver_ID ] 

                                    Set Variable [ $PROD; Value:Receiver_Items::Product_ID ] 

                                    Set Variable [ $COL; Value:Receiver_Items::Attrib_2 ] 

                                    Set Variable [ $SZ; Value:Receiver_Items::Attrib_4 ] 

                                    Set Variable [ $n; Value:Receiver_Items::Qty_Recd ] 

                                    Go to Layout [ “Layout #49” (Labels) ] 

                                    Loop 

                                    New Record/Request 

                                    Set Field [ Labels::ID; ($ID) ] 

                                    Set Field [ Labels::PROD; ($PROD) ] 

                                    Set Field [ Labels::COL; ($COL) ] 

                                    Set Field [ Labels::SZ; ($SZ) ] 

                                    Set Variable [ $n; Value:$n - 1 ] 

                                    Exit Loop If [ ($n = 0) ] 

                                    End Loop 

                                    Go to Layout [ “Layout #48” (Receiver_Items) ] 

                                    Go to Record/Request/Page [ Next; Exit after last ] 

                                    End Loop 

                                    Go to Layout [ “Layout #49” (Labels) ] 

                                    Enter Preview Mode [ Pause ] 

                                    Enter Browse Mode 

                                    Delete All Records [ No dialog ] 

                                    Else 

                                    Show Custom Dialog [ Message: "No records were found"; Buttons: “OK”, “Cancel” ] 

                                    End If

                                    1 2 Previous Next