1 2 Previous Next 17 Replies Latest reply on Apr 27, 2012 9:49 AM by philmodjunk

    Printable "table based" forms using FileMaker layouts

    PeterThorton

      Title

      Printable "table based" forms using FileMaker layouts

      Post

      Hello,

      I've been tasked with creating forms to be printed out (invoices, order sheets and such) The forms will display data from the company database (which I'm also designing, but that's not the issue rihgt now). The original forms which I'm to emulate were probably made in Excel, they are very "table based", by tables in this instance I don't mean database tables, but rather tables as visual elements. The picture will make clear what I'm trying to achieve and how far I got (kindly disregard the strange language and characters)

      As you can see, it's all about simple black frames. But they need to be perfectly aligned, text within them centered. Since FileMaker doesn't seem to facilitate input of visual tables (other than tables representing portals), I've been trying to make do with rectangles, lines and labels. This approach is, as you can imagine, extremely tedious. Getting the rectangles to align is not so difficult. However, getting the vertical lines to be the correct size is pain. I'm using all the functions of the Arrange menu, they are somewhat helpful, but it's still a lot of work and the results are not the best. Then comes trying to center the labels in the handmade 'cells'. I've given up on this for now, since it seems impossible to center them precisely by moving them around with the mouse.

      Yes, I'm aware that all the sizes and positions can be set precisely using the Inspector window. But that's even more tedious, and what's more, in my experience the Inspector likes to defy me when setting precise values. eg. I set the size of an element to 6,11cm, it will reset to something like "closest acceptable value", which is 6,22cm.

      Then there is the portal, which needs to stick to the header row seamlessly, like they're a single table. I suppose I can achieve this by making the fields in the portal the same height as the portal row height, make the widths identical to the columns in the header row, and give the fields borders. Again, lots of resizing, centering and aligning, which doesn't seem to work too precisely in FileMaker.

      Since I have no ruler to work with, no predefined sizes, I just try to guess the correct sizes and ratios. If it doesn't look the way it's supposed to, I resize. But resizing in this situation is even more pain. If I'm resizing the width of the columns, I need to move the vertical lines, and then re-center the labels. If I have to change the row heights, I have to resize the vertical lines on top of that, which is even more annoying. And then it's time to resize the portal fields.

      Is there a better way of doing this?

      for_fm_forum.jpg

        • 1. Re: Printable "table based" forms using FileMaker layouts
          philmodjunk

          Why can't you use the border settings for the portal and fields? that's much simpler and you can specify both vertical and horizontal centering for the fields to center the content in cases where this is necessary.


          Yes, I'm aware that all the sizes and positions can be set precisely using the Inspector window. But that's even more tedious, and what's more, in my experience the Inspector likes to defy me when setting precise values. eg. I set the size of an element to 6,11cm, it will reset to something like "closest acceptable value", which is 6,22cm.

          But are you aware that you can click the cm units to get pixels (filemaker 11 and earlier) or points (Filemaker 12) and then you can specify these changes in dimension one precise pixel at a time? And did you see the controls that will stretch or compress the height or width of a selected group of layout objects so that all are the same height/width as the object with the greatest/smallest height or width?

          You may also find that using a list view layout based on the portal's table makes for more flexible printing options and yet produces a result nearly identical to your portal--but with a fully flexible number of rows printed in your "table" of values.

          • 2. Re: Printable "table based" forms using FileMaker layouts
            PeterThorton

            Hi, thanks for your answer.

            But are you aware that you can click the cm units to get pixels (filemaker 11 and earlier) or points (Filemaker 12) and then you can specify these changes in dimension one precise pixel at a time?

             

            No, I obviously wasn't aware of this. Where can I turn this on?

            You may also find that using a list view layout based on the portal's table makes for more flexible printing options and yet produces a result nearly identical to your portal--but with a fully flexible number of rows printed in your "table" of values

             

            I haven't tried a list view layout, because I can't settle for something nearly identical. I need my forms to be identical to the template. Also, only about half of the fields on my form are part of a portal.

            • 3. Re: Printable "table based" forms using FileMaker layouts
              philmodjunk

              Where can I turn this on?

              In the inspector's position tab, click the units to the right of any of the size or position boxes. The units will cycle from inches to centimeters to points or pixels (depending on whether you have version 12 or an earlier version.) This change also changes the units shown in Layout Setup where margins can be specified.

              I haven't tried a list view layout, because I can't settle for something nearly identical.

              I think you should try this out. "nearly identical" is what I said to compare it to what you get with a portal. It may in fact be exactly the same as what you need for your report.

              only about half of the fields on my form are part of a portal.

              That's not a problem with this approach. Such a layout can easily include all the needed fields from the related parent record.

              Check out this demo file on invoicing to see the basic idea: http://fmforums.com/forum/showpost.php?post/309136/

              In this demo file, a portal is used to enter line item data, but a list view layout is used to print the invoice. The main advantage to using the list view layout is that it can print out any number of line item records. A portal is limited to only the number of rows visible in the portal. You can set the portal to slide up to make it shrink and remove unused portal rows, but you can't make it expand if you end up with more records than rows in the portal. You have better, but not perfect, sliding options for individual fields in your row with a list view layout also.

              • 4. Re: Printable "table based" forms using FileMaker layouts
                PeterThorton

                Thanks, I think you've convinced me. I'll definitely try it out. In the mean time, maybe you could help me with a bit more complicated report. I need to create something akin to an invoice, but it's rather a monthly account balance/invoice summary. (I don't have a  better name for it, in the company's native tongue it's called 請求書, which just translates to invocie) It should display details of all the orders a given customer has made in a given month.

                I have a table Order and a table Customer, it's a one-to-many relationship, so the Order table has a foreign key that points to CustomerID in the Customer table. Table OrderItem represents ordered products, it joins tables Order and Product, and breaks down the many-to-many relationship. I was thinking I'd base the report on the Customer table, or another table related to Customer, designed specifically for the report (this may not be the best approach, but the report does have some odd fields, which I can't put anywhere else at the moment. The data model will be revised in the near future anyway)

                The first problem is, how do I get multiple lines from multiple invocies on the report? I need all the item lines (OrderItems) from order 1, followed by all the item lines from order 2, and so on.

                The second problem is, how do I get only the orders I want on the form? I don't want all the orders the customer has ever made, just the ones from the current month. I'm not exactly sure if it's from 1st day of the month till the last day of them month, or some other dates, so it would be better if the user could pick the time span.

                The third problem is, I need a tax summary after each order. So it should look like this:

                -------- order1 - line 1 ----------------

                -------- order1 - line 2 ----------------

                -------- order1 - line 3 ----------------

                -------- order1 - line 4 ----------------

                    total tax for order 1: $$

                -------- order2 - line 1 ----------------

                -------- order2 - line 2 ----------------

                -------- order2 - line 3 ----------------

                total tax for order 2: $$

                -------- order3 - line 1 ----------------

                -------- order3 - line 2 ----------------

                -------- order3 - line 3 ----------------

                total tax for order3: $$

                 

                So far I have no idea how to do this, so any help would be appreciated :-)

                • 5. Re: Printable "table based" forms using FileMaker layouts
                  philmodjunk

                  Sounds like a "monthly statement" to me...Wink

                  Remember that demo file where you can print out an invoice from a layout based on LineItems (that would be "orderItems" in your database)?

                  This is the approach you can use to produce a layout that lists all the items ordered by a customer in a specified month.

                  Use a report layout based on OrderItems. Enter find mode and use related fields from Orders and (if needed) customers to specify criteria for your find. Specify the customer and specify a date range for the desired month. This might look like 1/1/2012...31/1/2012 or it might just be 1/2012. (Such finds can be scripted to produce reports for all customers for either the current month, the previous month or a specified month.) Sort these records by OrderID to group them by Order and you can use summary fields and sub summary layout parts to produce a grouped list of items ordered with sub headings, sub totals and a grand total.

                  The "tax summary" report isn't totally clear to me. Could this report be on a separate page appended to the end of the Monthly Statement? Could the tax sub totals be included in the same list of ordered items shown in the first part of the monthly statement? Does your example list all items ordered or just those subject to tax for each order?

                  • 6. Re: Printable "table based" forms using FileMaker layouts
                    PeterThorton

                    Concerning the report layouts, I've had some progress. I used a list view layout as you advised. It was a bit tricky getting it to look like a table. First I had to resize the field so they'd be the same height as the body part that contains them. Then I had to stick them all the way to the top border of the body part, so there wouldn't be a space between the lines. It resulted in lines touching, which created ugly 2 pixel lines, until it occured to me to turn off the borders on certain sides.

                    I've even found a workaround to the fact that labels can't have borders - I took a rectangle, set it to the exact position and size of the label, and grouped them. Resizing the resulting object works well, if I don't reduce the height below the text height of the label (in which case they'll desync)

                    Now I only have one problem as far as design is concerned:

                    How do I remove the line between the header and the body part?

                    • 7. Re: Printable "table based" forms using FileMaker layouts
                      PeterThorton

                      Alright, I dare say I've managed to recreate exactly the same report format I need. I used the list view layout, so my printouts should have exactly as many rows as there are entries in the OrderItem table.

                      This is where it all goes wrong: while the form looks pretty, the functionality is simply not there. Right now I'm not talking about the "monthly statement", I'm working on a simple order sheet instead.

                       

                      1. I want the list to show all the OrderItems for the current Order record. When I have the layout set to work with the Order table, the list will display a line for each order in the database. I've noticed that where the order has multiple OrderItems, FileMaker tries to put more lines in a single data row (and fails to put more than two, the rows are too narrow for that)

                      When I base the layout on the OrderItem table, it just shows all the OrderItem entries present in the database in the list. In both cases, when I try to flip through the records, a cursor appears at the beginning of the given row and moves up and down as I go through the records.

                      Needless to say, this is not the desired behavior. The portals did what I expected of them, with the only drawback being that I had to specify a fixed number of rows. I can live with that, and at least I can apply what I've learned about layout design to my old layouts. But if possible, I'd like the correct functionality AND the flexible number of rows. How can I achieve that?

                      2. I tried printing out the form. It printed the header, with the column titles, ONE ROW in the middle, and a 'Trailing Grand Summary' part where I put the grand total row. While I was going through the menus, trying to find some 'advanced print settings' I switched to 'Form View' and realized FileMaker is printing the Form View instead of the List View no matter what. How can I fix this?

                      • 8. Re: Printable "table based" forms using FileMaker layouts
                        philmodjunk

                        1

                        When I base the layout on the OrderItem table, it just shows all the OrderItem entries present in the database in the list. In both cases, when I try to flip through the records, a cursor appears at the beginning of the given row and moves up and down as I go through the records.

                        As I suggested last week this layout should be based on OrderItems. Since you are now looking at the individual items that make up an order, using the record control in the status area moves you from OrderItem record to OrderItem record instead of from Order to Order.

                        To see just the Order Items for one order, perform a find for that order or use Go To related Records from an Orders layout. Buttons with a script can be added to the header so that clicking them moves you to the group of order items for first, next, previous and last order record in your table of orders.

                        It is often the case in FileMaker as in many other database systems that you use one layout for data entry and a different one for printing the results. The list view, OrderItems layout might be kept as strictly a layout for entering data and an Orders based layout with a portal might be used for data entry.

                        2.

                        I tried printing out the form. It printed the header, with the column titles, ONE ROW in the middle

                        Sounds like you tried printing with "current record" specified instead of "records being browsed". Records being browsed prints all records in your layout's current found set, so, as I've indicated in 1 above, you need to first create a found set of just the items for a given order.

                        You may find this demo file that gives a very simple example of this approach helpful: http://fmforums.com/forum/showpost.php?post/309136/

                        • 9. Re: Printable "table based" forms using FileMaker layouts
                          PeterThorton

                          Hi,

                          Thanks again, but I believe I've already been refered to this file at least four times since I came to this forum, each time it was after asking very different questions.

                          I've seen quite a few samples so far, even ones that did what I needed, but unforutnately there is nothing in them that would tell me HOW the result was achieved. There is no source code I could analyze, no sequence of steps I could emulate.

                          I really appreciate that you're trying to help. I'm truly grateful. But every time you say "just do this and this" I get the urge to shout "yea, but tell me HOW!"

                           To see just the Order Items for one order, perform a find for that order or use Go To related Records from an Orders layout. Buttons with a script can be added to the header so that clicking them moves you to the group of order items for first, next, previous and last order record in your table of orders.

                           How? How does this translate into the actual user experience? Say the user is browsing orders, and when he finds the right one, I want him to be able to view the order sheet with a single click of a button. By what combination of seemingly unrelated features, obscure settings and counter-intuitive menus can I achieve this?

                          • 10. Re: Printable "table based" forms using FileMaker layouts
                            PeterThorton

                            About the order sheet:

                            I based the report on the OrderItem table like you said. I put a button on the Order layout. I set the button to "Go to Related Record" and chose the appropriate table and layout. When I click the button, the report is displayed correctly, with only the orderitems belonging to the order I've selected. But when I try to print it, it again prints only one OrderItem line. What the ...?

                             

                            As for the "monthly statement", the tax information appears on the same sheet, in the same table. No extra sheets. The tax in question is a 5% consumption tax, and as far as I know it applies to all products.

                            I'll show you an example of an actual form from the company. The form was not created in FileMaker, it was generated by a system that was custom made for the company by an IT firm. I'm supposed to do something similar in FileMaker, and possibly improve upon it.

                            I've removed some irrelevant columns from the right side and wiped all the sensitive information. On the form you can see that there is a tax column after the price column, and a separate line with the tax total after each order.
                            I was also asked to make a slightly modified version of the report. They told me that the tax column is confusing to the customers, so they'd like me to remove it, and leave only the total tax line. So far I don't know how to make either version. Any help would be appreciated.

                            • 11. Re: Printable "table based" forms using FileMaker layouts
                              PeterThorton

                              Here's the modified picture with the desired result. Sorry for flooding the thread with oversized pictuers.

                              • 12. Re: Printable "table based" forms using FileMaker layouts
                                philmodjunk

                                I've seen quite a few samples so far, even ones that did what I needed, but unforutnately there is nothing in them that would tell me HOW the result was achieved. There is no source code I could analyze, no sequence of steps I could emulate.

                                Did you open manage | Scripts and examine the print one invoice script in this file? That's where you'll find your "source code". It downloads as a compressed zip archive, so you have to extract it from the archive to a different directory before you can examine how it is designed. You can also go to Manage | database from the file menu to see what tables, fields and relationships have been defined. Manage | Value lists will show you how the value lists in a file were created.

                                How? How does this translate into the actual user experience? Say the user is browsing orders, and when he finds the right one, I want him to be able to view the order sheet with a single click of a button. By what combination of seemingly unrelated features, obscure settings and counter-intuitive menus can I achieve this?

                                Use Manage | Scripts to write a script and then add a button to your layout that is set up to perform the script. There's a limit to how much detail we can go into and unless you ask for more detail, I can't always tell if or not it is necessary just from what a person posts in their thread. If you have not already done so, I suggest you acquire a book and or training tutorials on FileMaker to help fill in the details.

                                Each of these script steps can also be researched in FileMaker Help.

                                A script to go from the current orders record to the set of OrderItems record might look like this:

                                If [ Not ISempty ( OrderItems::OrderID ) ]
                                   Go To Related Record [Show only related records; From table: OrderItems; Using layout: "InvoicePrint" (OrderItems)]
                                End IF

                                This scritp checks to see if any related OrderItems records exists and then pulls them up in a found set on the InvoicePrint layout. You could also include a script step to sort the order items records if you want to.

                                Once you have the script created, you can use the button tool to add a button to your orders layout and select the Perform Script button option--specifying the above script as the script to be performed when you click it.

                                • 13. Re: Printable "table based" forms using FileMaker layouts
                                  philmodjunk

                                  But when I try to print it, it again prints only one OrderItem line.

                                  Select Print from the file menu. In the dialog that appears, be sure to select "records being browsed" not "current record". This is something I mentioned in a previous post.

                                  I'm supposed to do something similar in FileMaker, and possibly improve upon it.

                                  A Summary report can produce this. You perform a find to find all relevant order items records and sort them by Order ID so that they are grouped by order. Then you use a sub summary layout part to produce the sub total rows of data on the report. The sub summary parts should be set up with the "when sorted by OrderID" option.

                                  You can then define a summary field in Order items that computes the total of your tax amount field.

                                  Place this field in the sub summary part and you see a subtotal for that order. Place this same field in the trailing grand summary, a footer or a header and you'll see a total of all the records in the current found set.

                                  The key is to pull up the correct set of records and to then sort them. This can be done by hand but will be more user friendly if you create scripts for this process.

                                  Once again, this a general description of the solution. I realize you'll need more detail, but if you look these terms up and investigate the tutorial on summary reports in the link at the end of this post, it should at least get you pointed in the right direction:

                                  Creating Filemaker Pro summary reports--Tutorial

                                  • 14. Re: Printable "table based" forms using FileMaker layouts
                                    PeterThorton

                                    thanks, I'm looking into it right now. Do you happen to have a tutorial on these 'found sets' and how to create them, particularly through scripting?

                                    1 2 Previous Next