12 Replies Latest reply on Jul 23, 2010 11:40 AM by philmodjunk

    printing spesific records

    nolak37

      Title

      printing spesific records

      Post

      Hello,

      Im trying to print onto a form, Ive already set everything up but am having two problems.

      The DB is used for billing purposes, the setup is of 2 tables, "bill" and "items" the relation is:

      bill:B_ID = Item:I_ID

      there can be up to 12 items per bill, and the form only holds space available for 6 so I created a layout holding 2 of these forms and an if statement figures out what layout to print on.

      On the "Items" table i have a summary field "total" which gets its info from the price field in the same table, this is set at the bottom of each form under the portal displaying the items. The problem is i need to display the total od the items listed only on that form instead of all items from both forms.

      Example:  Form1

      item1 5.00

      item2 5.00

      item3 15.00

      total 25.00

      Form2

      item4 5.00

      item5 10.00

      item6 15.00

      total 30.00

      2nd problem is if i billed one item incorrectly and need to re-bill how can i have the form print only that specific item without including all the others.

        • 1. Re: printing spesific records
          philmodjunk

          Are the items listed in a portal? or is this a list or table view layout?

          • 2. Re: printing spesific records
            nolak37

            the items are listed in a portal.

            On the print layout the form has a portal with initial row 1 and number of rows 6,

            The 2 sheet print layout has a portal on the 2nd sheet (form) initial row 7 number of rows 6.

            • 3. Re: printing spesific records
              philmodjunk

              A simpler approach is to set up your report on a layout based on the portal table instead. Then you can print as many items as you want in a multi page listing grouped under subsummary sub heads as needed.

              Here's a simplified tutorial that prints just such a report from an invoice system: 

              Creating Filemaker Pro summary reports--Tutorial

              • 4. Re: printing spesific records
                nolak37

                hey Phil,

                tried your tutorial and it looks good but my solution requires me to use form view IOT print out onto the pre printed forms.

                Do you have any other advise as to how I can approach this situation?

                • 5. Re: printing spesific records
                  philmodjunk

                  Hmm, we print on to preprinted invoice forms here, but avoid the problem by removing the scroll bar from the portal so that a user can't list more line items than will show when we print it. Our business is such that starting a new invoice to list additional items is very rarely needed.

                  What version of filemaker are you using? I mulling over whether a filtered portal will do the job here as there is a way to get totals of a filtered portal using a summary field and additional portals that would work in that case...

                  • 6. Re: printing spesific records
                    nolak37

                    Thanks for your reply

                    I am using FMPA11.

                    • 7. Re: printing spesific records
                      philmodjunk

                      Maybe this post will trigger some ideas from others...

                      I keep hitting road blocks in what I want to do here. If I assign each line item record a number in a number field starting with 1 and with no gaps, I can set a filter expression for the first portal as PortalTable::LineNumber < 5 and use PortalTable::LineNumber > 4 for the second. Then additional one row portals with the same filter expressions can be used to display a summary field defined in the portal table to give you the subtotals. For that matter, you could include an inequality that compares the line number field to a number field that stores a constant to define a pair of relationships and then you can use Sum Functions in the bill table.

                      Either works, but I'm just not thrilled with the extra overhead involved in giving every related line item record a line item number that starts with one...

                      • 8. Re: printing spesific records
                        nolak37

                        Ok so here is something that might work out for my case. Im thinking i can combine these tables and use 12 repetitions, then create a fiels that adds the first 6 reps and another to add the second set or last 6 reps.

                        I can use an if statement to find whether to print in the 1 form or 2 form layout.

                        Only problem is I already have many records in both these tables.

                        Can you think of a way to import the records if i were to do this so that they go to the correct Bill?

                        • 9. Re: printing spesific records
                          philmodjunk

                          I wouldn't use repeating fields for this. While it could solve the issue for these reports, it likely will greatly complicate other parts of your system. 

                          Hmm, maybe a calculation field that pulls all the data into a pair of text fields with tab characters to space the data into columns would work here...

                          I'm going to go play with a demo file and see if I can make that work. If so, I'll upload it to a share site so you can download it and take a look at it.

                          • 10. Re: printing spesific records
                            philmodjunk

                            Got it! Not quite what I speculated on in the last post, but it put me on the right track...

                            Define two calculation fields and adapt the following expressions. You need to refer to your tables and the extended cost field (Qty * Unit Price ) in it. The expressions below evaluate 4 row portals for easy testing, so you'll need to adjust to larger numbers as your portals likely have more than 4 rows in them.

                            PageOneTotal :
                            Let ( exp1 =  Substitute ( LeftValues ( List ( LineItems::Cost ) ; 4 ) ; ¶ ; " + " ) ;
                                    Evaluate ( Left ( exp1 ; Length ( exp1 ) - 3 ) ) )

                            PageTwoTotal :
                            Let ( exp1 =  Substitute ( MiddleValues ( List ( LineItems::Cost ) ; 5 ; 4 ) ; ¶ ; " + " ) ;
                                    Evaluate ( Left ( exp1 ; Length ( exp1 ) - 3 ) ) )

                            • 11. Re: printing spesific records
                              nolak37

                              Works like magic (Phil Magic). Thanks

                              • 12. Re: printing spesific records
                                philmodjunk

                                Give Thanks to Mr. Vodka also. A post of his that used evaluate in similar fashion came to mind as I was wrestling with this one.