9 Replies Latest reply on Aug 10, 2011 1:49 PM by Kat11_1

    Bill Summary fields

    Kat11_1

      Title

      Bill Summary fields

      Post

      I have tables

      Customer information -----< Bills-------< Line items

      Customer Services ---------< Line Itmes 2 (2nd occurance)

      I have a script that creates a new bill record and finds all the active services for a customer and creates bill line items for them.

      I have a layout / report - Bill based on Line Items table that lits all the line items for each customer's inividual bill. I have summary fields (Category  subtotal, Bill subtotal, Bill tax subtotal, total  due) in the Line Items table that properly display on this report.

      I would like to get these bill totals into the Bills table record so I can display just the bill totals via a portal on the customer bills' tab.

      I tried to create a loop that puuts the summary fields from the Line Items into a Global  and than into the field in the Bills table. Doesn't work.

      I am sure there is an elegant  solution ... can any of you help?

       

        • 1. Re: Bill Summary fields
          philmodjunk

          Try this simple approach (can't guarantee it will work without knowing more about how you've set everything up):

          Enter layout mode on the bills layout. Use the field tool to add each of these summary fields from the line items table to your bills based layout. If each of these summary fields computes a "grand total" for all the billed items, this should work for you. (Values in these fields may not update smoothly if data entry on the bills layout changes a summarized value.)

          • 2. Re: Bill Summary fields
            Kat11_1

            I tried that, in the portal that is to display all of one customers billss I included fields (Bill ID, Date, Status, etc) from Bills and the summary fields from the Line Items. They come up empty.

            Se below how I set up the summry field in Line items.

            • 3. Re: Bill Summary fields
              philmodjunk

              Actually, I meant for you to add them to the bill layout outside of the portal.

              • 4. Re: Bill Summary fields
                Kat11_1

                Yes that works. But is there a way to get them to display in a portal so that from there you can click on a link and go to a Bill layout where you see the whole bill with line items and all?

                I apologize for being confusing!

                • 5. Re: Bill Summary fields
                  philmodjunk

                  I am confused. Since the summary fields should display the same value for every line of the portal, why do you want to see it repeated on every line of that portal? (Putting the summary field, there should work, it just seems odd to do that.)

                  You can place a button inside the portal row that performs a script that uses either Go To Related Records or a Find to pull up the same records on your report layout.

                  • 6. Re: Bill Summary fields
                    Kat11_1

                    Let me refraze.

                    Let's say I need two portals. One to list all the line items for an individual bill and yes this one needs to show the summary fields in the summry area outside of the portal or layout body. I have that.

                    The 2nd portal needs to list all of individual customer's bills which will all have diffrent IDs, totals etc.

                    • 7. Re: Bill Summary fields
                      philmodjunk

                      You'll need to add a self join relationship for that portal.

                      Bills--<BillsSameCustomer-----<SameCustomerLineItems

                      Bills::CustomerID = BillsSameCustomer::CustomerID
                      BillsSameCustomer::BillID = SameCustomerLineItems::BillID

                      With that set up, you can add a summary field from SameCustomerLineItems to the portal row of a portal to BillsSameCustomer.

                      (Create the "same customer" table occurrences here by selecting the Bills and LineItems occurrences, then click the button with two green plus signs...)

                      You may want to only see unpaid bills. If so, you can either put a filter on the BillsSameCustomer portal (requires fileMaker 11) or to include and additional "status" field in the relationship between Bills and BillsSameCustomer that limits the match to only bill records with an unpaid status (only option for versions older than 11).

                      • 8. Re: Bill Summary fields
                        Kat11_1

                        Ok let me work on this :)

                        • 9. Re: Bill Summary fields
                          Kat11_1

                          Thank you, This works! :)