1 2 Previous Next 22 Replies Latest reply on Feb 17, 2011 11:10 AM by philmodjunk

    Invoice 'Report' - going quite insane



      Invoice 'Report' - going quite insane


      Before I kill someone..................... (metaphorically speaking)

      I'm trying to replicate an existing MS Access report (see screenshot) in Filemake Pro 11.

      Although the Invoice is quite busy with a number of tables and header, footer and icon, it's fundamentally a one to many Invoice <<<  Invoice Details relationship. It's for my Contract Services business so it needs (currently) to count the number of days (recorded in each Invoice Detail record) worked in an Invoice period (typically a month) and multiply that total by my daily rate (stored in the Invoice table). Additionally it'll need to add up any additional expenses for the Invoice grand total (thank heavens I'm not VAT registered at the moment).

      In Access there's a top down way of doing this - create the Invoice record and then make up the Body part by including the Invoice detail records..

      I've been Googling Filemaker solutions and they all suggest starting at the Invoice Detail (child) level, creating a Body section with these listed in it - so far so good

      Then they suggest creating a Sub-Summary, then my problems start!!! First, I'm not sure if I'm sub-summarising by a record in this 'Child' record set or by finding the 'Parent' Invoice ID. And when I put either field in the Sub-Summary area I don't see the field data, just the field label, grrrrrrr

      Could someone let me know if words of no more than two syllables how I can achieve my Invoice solution, thank you

      To summarise the data tables I'm using

      Invoice Header table containing Invoice Number and Date, Agency ID, Client ID, expenses details...

      Agency The Contract agency linked to Invoice by Agency ID

      Client The Customer I'm working for, linked to Invoice by Client ID

      Invoice details The Daily Invoice Line for each day worked. A child of Invoice linked by Invoice ID

      The final Invoice needs

      a) Report/Page Header (logo and company info)

      b) Invoice Header (sub summary?) (Invoice details inc invoice number, agencyu, client and invoice dates)

      c) Invoice Details (body?) (daily invoice detail)

      d) Invoice Footer (sub summary?) (totalling days worked, calculating totals and including expenses

      e) Report/Page Footer (more company info)


        • 1. Re: Invoice 'Report' - going quite insane

          PS and the reasons, if I can do this in MS Access why I'd want to do it in Filemaker, is I have Filemaker on my iPad and want to use this database and invoice on the move and I don't want to be defeated  - I want to know how to do this in Filemake

          • 2. Re: Invoice 'Report' - going quite insane

            This is meat-and-drink to Filemaker, so don't despair or become homicidal.  Yet.

            Start from the Invoice detail table, definitely.  The best answer may differ slightly depending on whether you use a script to find one invoise at a time to print or pdf, etc, or you find a bunch and then sort them and break them.  But the details will be small.

            The final Invoice needs

            a) Report/Page Header (logo and company info)

            - OK - just have a Header part and put in it any logos or fields you want.  These will be the same on every page.

            b) Invoice Header (sub summary?) (Invoice details inc invoice number,  agency, client and invoice dates)

            - Sub summary (Leading) will do fine.  Just sort by the InvoiceHeaderID (InvoiceNumber, whatever) and put all the Invoice Header details in this section.

            c) Invoice Details (body?)

            - Yup.  Stick the Invoice Detail fields there in the Body part

            d) Invoice Footer (sub summary?) (totalling days worked, calculating  totals and including expenses

            -Sub summary part (Trailing) will do fine, broken by the InvoiceHeaderID  (InvoiceNumber, whatever) again, and put all the summary fields in this  section.

            e) Report/Page Footer (more company info)

            - Yup: a footer part (will be the same on every page)

            • 3. Re: Invoice 'Report' - going quite insane

              No - it's not working after all!!!!

              Hi and thanks for your help but  despite my earlier reply there are still problems….…

              1. I only can see my Invoice as a proper invoice when I select [preview], which means I can’t see the Invoice properly on the iPad. The view I see on the iPad (and on the PC when I don’t click [preview] is of:

              Page/Report Header

              Invoice Header


              Invoice Footer

              Page/Report Footer

                              I can see on the navigation bit at the top of the screen (PC)iPad view, as many records as there are Invoice Detail records

                              In [preview] mode there are as many records as there are Invoices

              2              If I select Print (from [preview] and chose ‘print Current Record’ (which I assume means the record I can see on screen, ie an Invoice with all its associated detail records) I get the report header and just one detail record

               Preview mode

              I can get around the PC issues but I really wanted to get this working on the iPad (otherwise I may as well go back to MS Access) – do you know if I can replicate the [preview] mode of the PC on the iPad?


              • 4. Re: Invoice 'Report' - going quite insane

                You are using View as Form settings which shows only one record at a time. Click View as List and you'll be there. You can use Layout Setup... options to make sure that this layout is always in View as List mode.

                • 5. Re: Invoice 'Report' - going quite insane

                  OK, now I don't know what's happening!!!

                  Can't see the sub-summary parts at all in preview mode - I don't know what has happened. They are visible in Edit mode and in Form mode but not showing at all in Preview - now I remember why I felt murderous at the beginning - this application makes no sense

                  • 6. Re: Invoice 'Report' - going quite insane

                    Using View as List hides the sub-summary parts completely

                    • 7. Re: Invoice 'Report' - going quite insane

                      "If I select Print (from [preview] and chose ‘print Current Record’  (which I assume means the record I can see on screen, ie an Invoice with  all its associated detail records)"

                      No - you are printing from the detail table, so you should choose 'Records being browsed'.

                      FM11 will display summary parts perfectly (can't speak for iPad, haven't got one) but the problem will be there anyway if you are only looking at the current record.  By the way, you have found all that invoice's line items in the detail table, haven't you?

                      • 8. Re: Invoice 'Report' - going quite insane

                        View as list will not hide sub summary parts. Not sorting your records in the order required by the settings you've specified for your sub summary parts will hide them. In browse mode, you must have your layout in View as List in order to view the report.

                        • 9. Re: Invoice 'Report' - going quite insane

                          Guys, thanks for your help but I'm more confused than ever

                          I DID have my layout nice so, in Preview mode it was a perfect invoice but now the sub-summary parts don't show - I've no idea why.

                          In FORM view it looks good but only shows one detail record but DOES show sub-sumarries

                          In LIST view I only see Page/Report header/footer and detail records  - no sub-summaries

                          If I understand you correctly, Phil, the sort order of the sub-summary (currently by Invoice ID) doesn't equal the sort my records currently have which is Invoice ID descending order. I can't see an option in the sub-summary Part Definition to sort in descending order - it just says "when sorted by". I suppose I can reset the sort of my records on display but it's handy to sort descending as the Invoice I usually want to edit or print is the last one, i.e. most recent.

                          By following your advice I've got my nice invoice look on the PC back

                          I can see how it looks almost perfect in 'List mode' but when one invoice finishes the next one comes along behind. How do I limit the number of records displayed on the list screen. I would like to only see one Invoice (and all associated detail and totals) per but page'? Is there a page break option

                          • 10. Re: Invoice 'Report' - going quite insane

                            Ascending or Descending options don't matter for your sub summary part what matters is whether or not the table occurrence::FieldName you specified is part of the current sort order. Both ascending and descending groups the records by that field and that's what is needed for your sub summary part. The key thing is that your records do not sort themselves. You have to sort them to get the desired order--either manually or via a script.

                            If your sub summary part specifies TableOccurrenceX::Field1 as its "sorted by" field. Then the current sort order for your found set of records must include TableOccurrenceX::Field1 in its sort order or the sub summary part will not be visible. Select Sort from the Records menu and check to be sure that this is the case.

                            • 11. Re: Invoice 'Report' - going quite insane

                              The ascending or descending order isn't important (this time).  The sort field must be the same as the sub-summary break field.

                              You can set a page break against the sub-summary as well. You can set it to break after every new invoice.

                              You can have a script select only one invoice number first, as well.

                              • 12. Re: Invoice 'Report' - going quite insane

                                Thanks Phil.

                                I don't know what I did to get my sub-summary bits back then. I thought I'd just resorted my records back into ascending order and then the sub-summaries reappeared. I suppose all's well that ends well, but I wish I'd known what I'd done.

                                I've checked (maybe I should have done this first) and the iPad doesn't have a preview mode but frustratingly it DOES have a save to PDF function.

                                I wanted to see my Invoice properly on screen and use this function to email invoices  from whatever location I was.

                                It's a like a mad plot to thwart me (paranoia fast setting in?)

                                PC has a perfect preview mode for a perfect Invoice look BUT it's not available on the iPad

                                Form Mode is really nice as it only shows one overall record at a time, BUT it also only shows ONE detail record

                                List Mode also looks good cos it has the Invoice record with the many detail records BUT it doesn't have any onscreen pagination


                                • 13. Re: Invoice 'Report' - going quite insane

                                  I think I'm out of sequence Sorbsbuster as I've only just seen your reply.

                                  I know I can do things with page breaks with the sub-summaries to ensure the printed report paginates properly but because I can't use the preview functionality on my ipad I'm wondering if I can either:

                                  -- do something in Form View layout to see all details for each invoice OR

                                  -- do something to paginate the layout screen in List Mode

                                  • 14. Re: Invoice 'Report' - going quite insane

                                    Not really sure what aspects of "Paginate" you want here...

                                    While you can't really "paginate" the list view screen, there are things you can try to make it a little easier to work with. You can add buttons that scroll the list one page at a time. You can use a layout customized for browse mode that uses a footer so that data in that layout part is always visible when you scroll through your list of records.

                                    1 2 Previous Next