1 2 Previous Next 19 Replies Latest reply on Dec 23, 2012 11:39 PM by philmodjunk

    creating multiple labels from a single record

    ghfarm

      Title

      creating multiple labels from a single record

      Post

            

           I am trying to create multiple shipping labels based on a single record.  When a customer orders a product the number of cases is stored in one field (quantity sent).  But I need a label for each case that is shipped and the label should indicate “1 of 3, 2 of 3, etc”.  This is a simple problem in Access which is what I’m currently using, but I can’t figure out how to do it in Filemaker Pro 12.  Any clues would be much appreciated.

        • 1. Re: creating multiple labels from a single record
          philmodjunk

               What kind of label printer are you using?

               A plain paper printer where you have multiple columns of labels?

               A special label printer that can print one label at a time?

               I would think that a special label printer would be the better option here so as to avoid the challenge of printing all the labels on a given sheet of labels.

          • 2. Re: creating multiple labels from a single record
            ghfarm

                 The printer formatting is not reaaly the issue. But to be clear I am printing on sheets (8.5 X 11, 2 colum, 5 up).  What I am trying to figure out is how to extract the information from the database to print individual labels for each case of product ordred - e.g if someone orders 6 cases and the number "6" is in the order field then I want 6 labels for that product.

            • 3. Re: creating multiple labels from a single record
              philmodjunk

                   Yes, but the basic type of printer determines the best method to use to print multiple labels for the same record.

                   If you have multiple labels per page such as is typical for Avery type labels, a special "labels" table where a script can create one record for each label will be useful.

                   If you have on label per page such as is typical of a special purpose lable printer, you can use the same method or you can use a script that prints the same record multiple times.

              • 4. Re: creating multiple labels from a single record
                ghfarm

                     OK...Well, I'm using a HP Laser Jet and I'm pringting sheets (2x 10) as I've said before.  I'm not using a single purppose label priter.  the labels themsleves are pre- printed with our name, address, logo, etc so all I need to do is print information based on the the particular order.  The scripting part is what I'm stuck on.  I am pulling information from related tables tha iclude the customer, date, invoice, and the individual item - which s what I need to split up based on the quntity that the custoomer purchased.  If a customer buys more than one item I need more than one label for each box.  That's what I need to figure out.  I've attached a picture of what I'm trying to accomplish.  Please note the circled area that incates cases count.

                • 5. Re: creating multiple labels from a single record
                  philmodjunk

                       Apologies for missing the printer info in that post.

                       I recommend using the labels table that I mentioned.

                       You can set up a relationship between the labels table and the table where your label data is stored like this:

                       ItemsToLabel::__PrimaryKey = Labels::_fkForeignKey

                       A pair of number fields in Labels can record the label number and the quantity.

                       A Labels layout can then be set up and fields from ItemsToLabel can be included on this layout along with the two number fields in Labels to product your labels.

                       Then a looping script can create the Labels records for printing purposes and you can delete all records from the Labels tabel when you are finished.

                       Here's a looping script to produce the labels needed for a found set of records in ItemsToLabel.

                       Freeze Window
                       Go to Layout [Labels (Labels)]
                       Delete All Records [No Dialog]
                       Go To Layout [Original layout]
                       Go to Record/Request/Page [First]
                       Loop
                          Set Variable [$Qty ; ItemsToLabel::Qty]
                          Set Variable [$ID ; ITemsToLabel::__pkPrimaryKey]
                          Go to Layout [Labels (Labels)]
                          Loop
                             Set Variable [$K ; value: $K + 1]
                             Exit Loop If [ $K > $Qty]
                             New Record/Request
                             Set Field [Labels::Qty ; $Qty]
                             Set Field [Labels::_fkForeignKey ; $ID]
                             Set Field [Lables::Label# ; $K]
                          End Loop
                          Go to Layout [original layout]
                          Go to record/request/Page [ next ; exit after last]
                       End Loop
                       Go to Layout [Labels (Labels)]
                       Enter Preview Mode [pause]

                  • 6. Re: creating multiple labels from a single record
                    ghfarm

                         This was very helpful.  You set me off in the right direction.  However I am unable to populate the “Labels” table with the script.  I’ve set up the new table and the relationship with the source table (which stores the line items for each order).  I can generate a label by manually typing in the parameters in the labels table and the script does delete old records, but it doesn’t repopulate when the label is open for the first time.  I have the script set to run on OpenLayout.

                         Here’s what I have set up so far:

                         Labels (table):

                           
                    •           Id – auto increment
                    •      
                    •           InvoiceLineItem_fk (Number)
                    •      
                    •           LabelNumber (Number)
                    •      
                    •           CaseCount (Number)

                         Invoicedetails_ORDERS_whl (line item table)

                           
                    •           Id – primary key
                    •      
                    •           fld_txtVegetable (This is a fk for a crops table.  I pull the actual description from that related table.)
                    •      
                    •           CaseCount (calculated field “Ceiling ( fld_iQtySent )” to produce an integer)

                         Labels layout called “Whl Labels”

                         Here’s how I’ve adapted your script:

                         Freeze Window

                         Go to Layout [“Whl Labels” (Labels)]

                         Delete All Records [No Dialog]

                         Go To Layout [Original layout]

                         Go to Record/Request/Page [First]

                         Loop

                            Set Variable [$Qty ; Value:Invoicedetails_ORDERS_whl::CaseCount]

                            Set Variable [$ID ; Value:Invoicedetails_ORDERS_whl::id]

                            Go to Layout [“Whl Labels” (Labels)]

                            Loop

                               Set Variable [$K ; value: $K + 1]

                               Exit Loop If [ $K > $Qty]

                               New Record/Request

                               Set Field [Labels::CaseCount ; $Qty]

                               Set Field [Labels:: InvoiceLineItem_fk; $ID]

                               Set Field [Lables::LabelNumber ; $K]

                            End Loop

                            Go to Layout [original layout]

                            Go to record/request/Page [ next ; exit after last]

                         End Loop

                         Go to Layout [Whl Labels (Labels)]

                         Enter Preview Mode [pause]

                          

                         This script seems to delete the old items but I’m obliviously missing something in the loop part.  Any hel;p would be appreciated.  Thanks for all your help so far.

                    • 7. Re: creating multiple labels from a single record
                      philmodjunk

                           I see an error in my original script.

                           You need to add this line:

                           Set Variable [$K ; value: 0]

                           immediately after Go to Layout [“Whl Labels” (Labels)] and just before the second "Loop" step.

                           This will reset the value of this variable back to 0 after the labels for the first record have been created.

                           Also, be careful what layout is current when this script is performed. The labels layout should not be current when this script starts up so if you are using the onLayoutEnter trigger on the Labels layout to perform this script, you don't have the correct "context" for it to work. Make sure that you start from a layout based on line item table such as the Invoicedetails_ORDERS_whl layout.

                      • 8. Re: creating multiple labels from a single record
                        ghfarm

                             Thanks again for your help.  The tip on which layout to run the script was the key to aking this work - aside from your script.  Of course, what I intially got was the entire contents of the invoice details table (about 26,000 records) so I've been working on whittling to make it manageable. 

                             I have four criteira:

                             1 Filtering for wholesale customers

                             2 Filtering for crops only from one source

                             3 Omitting one customer because they require another label

                             4 Filtering for the delivery date

                             I was succesful in accomplishing the first three by editing your script with a perform find parameter.  The last has been a little tougher for me.  Since that critieria changes on a weekly basis I tried creating a separate table just to input the date and define a variable in the script that would establish the delivery date for the invoice from that table.  (This is how I have it set up in Access by defing a macro).  So far I haven't had any success. 

                             Any suggestion on how I could accomplish this in Filemaker would be appreciated.  Thanks.

                        • 9. Re: creating multiple labels from a single record
                          philmodjunk

                               Define a date field with global storage for entering/selecting the delivery date. If it is possible that you need a range of delivery dates, define two such fields. Global fields can be defined in any table in your database and they will still be accessible from any layout so you may want to create a table just for global fields.

                               Use a scripted Find like this:

                               Enter Find mode [] --> clear the pause check box
                               Set Field [YourTable::DeliveryDate ; Globals::GlobalDateField]
                               Set Error Capture [on]
                               Perform Find[]
                               If [Get ( FoundCount ) ]
                                 #Do your labels printing stuff here
                               Else
                                 Show Custom Dialog ["No records with that delivery date were found"]
                               End IF

                               To find on a date range:

                               Enter Find mode [] --> clear the pause check box
                               Set Field [YourTable::DeliveryDate ; Globals::GlobalDateField1 & "..." & Globals::GlobalDateField2]
                               Set Error Capture [on]
                               Perform Find[]
                               If [Get ( FoundCount ) ]
                                 #Do your labels printing stuff here
                               Else
                                 Show Custom Dialog ["No records with that delivery date were found"]
                               End IF

                          • 10. Re: creating multiple labels from a single record
                            ghfarm

                                 I just wanted to wrap this up and thank you for your help.  All your suggestions were very helpful and I learned a lot about desinging other scripts.  The only glitch so far is that when I first open the form that genrates the labels the script takes a long time to run.  As long as that form is open subsequent searches are nearly instantaneous.  The form (based on the original invoices_Orders_whl table) is a simple dialog box with the Global date field and a button that actuates the script.  I've been trying to figure out how to create a modal form that disappears after the labels are generated but with no success.  The script is set to pause so it just hangs there until I manually dismiss the labels form and the orginal search form.  If you could point me in the right direction for dealing with this sort of design I'd appreciate it.

                                 I was about to give up on filemaker ( and $300) when I hit this impass.  I still think it's a little "slippy sloppy in the larder" but it has the same potential as Access if I can figure out how to approach what is usally just a simple matter of designing queries. I've attached the finished product - my homework.

                                  

                                 Thanks

                                 Tony

                            • 11. Re: creating multiple labels from a single record
                              philmodjunk

                                   I've been trying to figure out how to create a modal form that disappears after the labels are generated but with no success.  The script is set to pause so it just hangs there until I manually dismiss the labels form and the orginal search form.  If you could point me in the right direction for dealing with this sort of design I'd appreciate it.

                                   Use New Window to open the form in a new window. "Dialog window (modal)" is one of the advanced window styles you can specify.

                                   To see some working examples of how this can be done, see this file, the Known Bugs List database. Clicking "edit" on the first screen that appears, uses New Window to open such a 'dialog box' window.

                                   PS. and many developers who are familiar with SQL struggle a bit with Filemaker's non SQL approach to working with Relational Databases. On the other hand, FileMaker 12 introduced a new function: ExecuteSQL that you may find a useful and familiar tool for some parts of your solution.

                              • 12. Re: creating multiple labels from a single record
                                ghfarm

                                     Thanks Phil.  I figured it out.  Unfortunately while I was trying to design a new report I locked myself out of the datatbase by setting a script trigger to close on a certain form.  The form exited the program but now the first file it goes to is the one with the exit script trigger.  Kind of stuck in a loop.  Is there anyway around this?   This is the kind of thing that drives me crazy with this program.  There's very little latitude for making mistakes.

                                      

                                     Tony

                                • 13. Re: creating multiple labels from a single record
                                  philmodjunk

                                       If you have FileMaker Advanced--something that I recommend that all developers acquire, launch fileMaker without opening this file. Then enable the script debugger, then use the file menu to open the file. This problem script will pop up in your debugger and pause on the first line of the script. Now you can click the X to halt the script and you can fix the problem. (You can even edit the script while it is open in the debugger.)

                                       Without advanced, create a new filemaker file. No need to create tables or anything, just go to the script manager, create a script and use the Perform Script step to perform a script in the other file. Try to perform a script that changes layouts and/or pauses such that you can cancel the script. (Pressing esc on windows and Command Period on Macs, aborts a running script if "allow user aborts" was not used in the script to prevent this.)

                                  • 14. Re: creating multiple labels from a single record
                                    ghfarm

                                         Thanks for that Phil.  I redeisgned the database to open on a neutral form so that wouldn't happen again.  Unfortunately I do not have advanced but I think I can muddle through right now for the purposes of this database.

                                         I've incorporated your inital script for printing labels to set the sales date globally for all the reports that I need to generate for a sales day.  This is something I've been wanting to do for a long time and Filemaker has made that task pretty easy.

                                         I have one more question regarding Subsummary reports.  I have a packing list report that lists the customer and their order.  some orders are large some are small so there are usually multiple customers on one page.  The problem I'm having is keeping a group together if the body goes beyond the page.  There are options for breaking according to a subsummary section (e.g. customer) which would give me a single page per customer; or breaking after so many occurances.  But there is no option for simply keeping a group together if it runs past the end of the page.  Am I missing something or is this another candidate for a formating script?  

                                         I've attached a picture to illustrate.  Note the end of the page which cuts off the list and gose to the next page.  I want the break to be at the subsummary level if it can't fit.

                                    1 2 Previous Next