9 Replies Latest reply on Jun 2, 2014 1:02 PM by philmodjunk

    Field w/ Repeating Lines... Print Help

    LeilaRegan

      Title

      Field w/ Repeating Lines... Print Help

      Post


           Hi,

           I have a field (Item Description) with 60 repeating lines. I have the repeating lines broken up into 2 pages on the print layout (so I have a quotes layout with 60 repeating lines then a formal print layout for clients with 2 pages of possible information -Page one is lines 1-25 and page 2 is lines 26-60) which makes our contract 4 pages long. Is there any way to only print the second page (lines 26-60) when there is actual data in those lines. I was trying to come up with a way to revise the script I have in place but am not sure how to put specifications on only certain lines within a field. I set up two printing layouts one that is only 3 pages long and one that is 4 pages long but can't figure out how to create a script to only print Layout3Pages if Lines 26-60 of field Item Description are empty otherwise print Layout4Pages. I'm working on FileMaker Pro 11.

            

           The closest forum post I've found is this one ... http://forums.filemaker.com/posts/04440ef856 ... But it doesn't really work for my situation or maybe it does and I'm just not seeing it.

            

           Any Help would be greatly appreciated. Thank you.

      Screen_Shot_2014-05-28_at_3.19.00_PM.png

        • 1. Re: Field w/ Repeating Lines... Print Help
          philmodjunk

               It is an option that can work for this, but requires that you create and use multiple layouts--each identical except for the number of additional pages.

               What would be a far more flexible option is to set up a table of related records and move all of the data in your repeating fields--a design option that became 80% obsolete when FileMaker became Relational instead of Flat File with the release of FileMaker 3 into this related table.

               Then you can set up a list view layout based on the related table and printing becomes a matter of pulling up a found set of all your line items in that related table and then printing. The number of records will automatically adjust and you can print invoices that are 1, 2, 200 pages long without needing more than a single layout to use for printing your invoices.

          • 2. Re: Field w/ Repeating Lines... Print Help
            LeilaRegan

                 Very good advice, alas, it will not work. I did not design this database and I'm trying to figure out what crazy things someone set up. The problem is the Item Description field should be related to an inventory table making the repeating fields utterly useless just like you said (I'm slowly doing the FileMaker Pro 13 training to be able to hopefully design a much more efficient and useful database but I'm not there yet) however, the data in the Item Description field is manually entered and is not any one set of data that can be pulled from a related table. It varies greatly depending on who is entering the data and can even include very specific instructions that will never again be used. I agree that this database is really basic but until we can design a new database this is what I have to work with and this is what I have to fix (sadly). 

                  

                 So all that being said and with no common thread of data shared by anyone, how can I set this up to print either the 3pagelayout if lines 26-60 are empty or the 4pagelayout if lines 26-60 have data.

                  

                 Thank you so much. I know this is heavily outdated.

                  

                 Example of the long winded type of data going into this field.

            • 3. Re: Field w/ Repeating Lines... Print Help
              philmodjunk

                   None of what you describe prevents you from replacing your set of repeating fields with a table of related records. Back when FileMaker 3 introduced related tables and portals, Import Records was enhanced with an option that makes it very easy to move the contents of repeating fields into a set of records in a new table with one record for each repetition that contains data.

                   Thus you can do the following:

                   a) Define a primary key field in your current table if one does not already exist. Make it an auto-entered serial number field and use Replace Field Contents to give all your existing records a unique serial number value while also updating the auto-entered serial number settings to prevent new recors from getting a duplicate value.

                   b) Define a new table with one field for each repeating field shown in your screen shot. Add a foreign key field and link it to your original table by linking the primary key from your original table to this foreign key field of your new table.

                   c) Use Import Records to import all the records from your original table into the new table. Map the primary key to the foreign key field in the new table. Map the repeating fields to their corresponding non-repeating fields in the new table.

                   d) when you click "Import", a small dialog will appear asking if you want to split up the repeating fields in to separate records. Select that option and you'll get a new table with all the data from your current set of repeating fields. You can then duplicate your current layout and try modifying this layout to use a portal in place of the repeating fields. (And now relationships to look up product descriptions and unit prices can be made to work as you need them to work.)

                   e) and now you can try setting up a list view layout based on this new table to use for printing your invoices.

              • 4. Re: Field w/ Repeating Lines... Print Help
                LeilaRegan

                     Thank you so much for the step-by-step. I'm just a little lost and feel I'm not up to the task of developing this type of system right now. I will try to attempt it, however. I feel I need to explain the above database a bit more all of those fields - Qty, Item Cat, Description, Unit Price, Ext Price, Replace Cost are repeating fields ... As I said I didn't develop this database and would never have done a quote/proposal/invoice this way. All of those fields are also entered manually and need to be able to continue to be entered manually and each record (the primary key field is/would be the Proposal ID) is different. No 2 records have the same description, quantity, etc.

                      

                     Rather than re-inventing the wheel (we are hiring someone to come in and design a new functional database from scratch within the year) is there not a way to simply print the two layouts depending on if certain lines in a repeating field are valid or empty?

                • 5. Re: Field w/ Repeating Lines... Print Help
                  philmodjunk
                       

                            is there not a way to simply print the two layouts depending on if certain lines in a repeating field are valid or empty?

                       I answered that question in my first response. A script can select one of several layouts to select the one with the correct number of repetitions for the size of the invoice.

                       The count function can count the number of repetitions in a repeating field that contain data so this can be used in a script in order to select the appropriate layout for the number of line items.

                       

                            All of those fields are also entered manually and need to be able to continue to be entered manually and each record (the primary key field is/would be the Proposal ID) is different.

                       None of this should be an issue for the portal to a related table method that I have recommended.

                  • 6. Re: Field w/ Repeating Lines... Print Help
                    LeilaRegan

                         I must have missed your answer to that query in the first response because all I read was:

                         

                              It is an option that can work for this, but requires that you create and use multiple layouts--each identical except for the number of additional pages.

                         For now since the company has made it clear I can't go mucking about re-creating relationships, etc. because if I do it incorrectly they do not have a back-up to their database. I have also been unable to clone this database to practice on because apparently no one has access to the FileMaker Pro Server. I would prefer the script option I already have 2 layouts for the script I am just unsure how to fit it into the script I currently have in place which I attached with my original post.

                          

                         I will go with the script for now until I can develop your second suggestion. Can you please help me understand how to fix my script.

                    • 7. Re: Field w/ Repeating Lines... Print Help
                      philmodjunk
                           

                                For now since the company has made it clear I can't go mucking about re-creating relationships, etc. because if I do it incorrectly they do not have a back-up to their database.

                           Working from a copy of the file is the right way to develop and test changes to your database, but:

                           Any such database should be set up with frequent scheduled back ups. Each such back up copy should be retained for an extended period of time. We keep one nightly back up copy for a month and then move one of those copies to a different storage location once a month where we keep it forever.

                           Such backups not only protect the business against issues from changes made to the file's design but also from user errors brought about due to deleting the wrong records or incorrectly editing some data in a field...

                           If your company does not currently do this, they risk catastrophic issues with their data. If they do this, their concerns about you redesigning their database--provided you use proper care, test carefully and keep a "plan B" unmodified copy of the file handy to revert to--are not realistic.

                           Note that setting up the related table does not require that you remove the repeating fields. You can make this change,  leave the original data in place for several months while demonstrating that the new version works, and only then reduce the size of your file by removing these now redundant fields.

                      • 8. Re: Field w/ Repeating Lines... Print Help
                        LeilaRegan

                             I know that we have backups being stored, however,  I do not have access to the FileMaker Pro Server and/or Backups and am unable to create a clone or even duplicate this database. So I do not have the abilities to work from a copy of this file as no one seems to be able to access the FileMaker Pro Server. This database might only be in place for another 6 months as we are working on a re-design. Once we upgrade and have a newly developed database the repeating fields will be obsolete and the way you described above will be in place along with an inventory list and no more manually entered data.

                              

                             That being said, would you please help me figure out the script I should write to modify the script I have in place (attached in my original post). Thank You.

                        • 9. Re: Field w/ Repeating Lines... Print Help
                          philmodjunk

                               If they are expecting you to modify the design of the database, they should give you access to the Server, both the admin console and the backups. I suggest going "hat in hand" and respectfully pointing out that making design changes to the existing file (such as the script change that you want) is like changing the spark plugs on a car while driving it down the road. It risks damaging the file if a "glitch" occurs at the wrong time.

                               (I do make certain changes--such as script changes routinely to a hosted database file here where I work, but I also have full access to both the admin console and the back ups copies on the server so I also have the ability to rapidly intervene--even replace files from a back up if something should go wrong.)

                               And You'll need access to those files if you plan on either a) incorporating part of the current file's design in the new version and/or b) importing your past data into the database for "historical purposes".

                               As to those script and layout changes:

                               Option 1) This is your best bet if you can make it work--no guarantees from me. Go to a copy of your current layout, select each of the repeating fields and use the Inspector to set them to "slide up" and to "reduce enclosing part". You can modify this layout to show all repetitions over multiple pages, but in Preview mode or when printing/saving as PDF, it should shrink down to just the repetitions that actually contain data. All objects in the body located below these fields--such as any fields computing totals will also need the same slide/resize settings.

                               Key facts about sliding layout objects:

                                 
                          1.           It's only visible in preview mode and when you print/save as PDF...
                          2.      
                          3.           Sliding fields will shrink but not expand.
                          4.      
                          5.           All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
                          6.      
                          7.           Objects in headers and footers will not slide.
                          8.      
                          9.           Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
                          10.      
                          11.           Fields will slide up only if Top alignment is specified for it and will slide left only if Left alignment is specified.
                          12.      
                          13.           Consistent side borders are difficult to achieve with sliding fields.
                          14.      
                          15.           In FMP13, hidden objects (”Hide object when”) will slide/resize.


                               If that doesn't work, first create layouts for the varying numbers of pages if you do not already have such. For this example, I'm going to pretend that you only have layouts for 1 or 2 pages, but you can extend the example to handle more possible layouts:

                               Put this code inside the "Else" portion of your existing script:

                               If [ Count ( YourInvoiceTable::Qty ) < put max number of repetitions that will fit on one page layout here ]
                                  Go to Layout [ "One page Invoice layout" (Invoices) ]
                               Else If [ Count ( YourInvoiceTable::Qty ) < put max number of repetitions that will fit on two page layout here ]
                                  Go To Layout [ "Two page invoice layout" (Invoices) ]
                               Else
                                  Show Custom Dialog {"Error: there are two many repetitions to print" ]
                               End IF
                               Print []