3 Replies Latest reply on Nov 11, 2016 1:57 AM by erolst

    Invoice Database Line item presentation

    sam0723

      Hello everyone, i am new to filemaker. I am developing an invoice database. Here is the situation.

      My idea is creating 4 tables. Invoice table, invoice line item table, line items details table and a client's table.

       

      the relationship is invoice-->invoice line item,    client-->invoice,   invoice line item<-- line item details table.

       

      The layout should be something like the attachment.

      The most difficult thing is the description part.

      The invoice template of the client divide the description into several parts, for example, part one, part two and part three.

      For example

      Part one fruit

      apple                               4        50

      orange                             5         60

       

      Part two Meats

      chicken                           10      100

      beef                                  2         50

       

      My question is how could i present the data in this way, i have considered using portal, but one problem is if i use conditional value list

      it might present like

      fruit     apple   4    50

      fruit    orange   5    60

      meat   chicken    10   100

      meat   beef           2      50

       

      fruit, meat, apple are store in the line item details table.

       

      so it may not be the layout the client's want.  Then i have another idea, so i need to have a summary report. The portal could be the data entry page. I need to create another layout for the reporting. then the report should be based on 1 client and using food catagory as the break field to present the data.

       

      I am wondering is there any good method to present my data?

       

      i am using filemaker 14

        • 1. Re: Invoice Database Line item presentation
          akhlaq38

          Your proposed design is very typical, just the names are different. Line detail table is actually "Products" table and client is "Customers". Part 1 and part 2 are actually the Categories. So better you adopt the solution database "Invoices" which comes with every version of FMP. It is very identical to your design and well organized and programmed to learn.

          • 2. Re: Invoice Database Line item presentation
            sam0723

            Thank you. so i need to create 2 layouts. one for data entry and the other is for reporting? but the data should be specific to that customer, right?

            • 3. Re: Invoice Database Line item presentation
              erolst

              sam0723 wrote:

               

              Thank you. so i need to create 2 layouts. one for data entry and the other is for reporting?

              Adding to akhlaq38's explanation, here is a "graphical" display of your tables and their relationships:

               

              Customer --< Invoice --< LineItem >-- Product

               

              Typically, an Invoice is

              managed: from an Invoice layout in Form view (selection of customer, a portal that displays the line items added)

              printed: from a LineItems layout in List view (including sub-summary part(s), a grand total etc.

               

              As to fruit, meat etc.: each line item is based on a record from the Product table; and that is where you would add that as an attribute. Once you've done that, you can use that attribute in the LineItems table to base a sub-summary part on.

               

              sam0723 wrote:

              but the data should be specific to that customer, right?

              I think the single most important report in any Order database is the invoice layout … and that is based (indirectly) on a specific customer anyway.

               

              If beyond that you want to see overviews / statistics for a customer, use a layout that makes the most sense for the task at hand.

               

              To see the totals ordered by a customer, you could simply use a related summary field from Invoice on a Customer layout. To break orders down by period, you'd create a report based on the invoice layout. To break orders by produce category, you'd base your report on the LineItems layout.