8 Replies Latest reply on Aug 24, 2009 8:20 AM by philmodjunk

    I want it all (A subsummary report showing all open POs and Only income billed in the current month)

    Susannah

      Title

      I want it all (A subsummary report showing all open POs and Only income billed in the current month)

      Post

      As suggested by my subject line, Im so muddled Im not sure I can articulate this clearly...

       

      I am working in FMP9. Imagine a simple project summary view that shows the following columns:

       

      "Current PO Amount" (Number From JOB DETAIL Table)

      "Amount Billed" (Number From JOB DETAIL Table hand entered to avoid a circular reference in my INVOICE table)

      "Projected Additional Revenue"  (calculation ="current estimate" - "amount billed").

       

      I want to add an additional column that shows a number "revenue billed in the current month" beside each prodject that meets this condition.  This would be found by referencing the "Invoice Date" (date from the INVOICE table) and then showing the "Invoice Amount" (Numeric Calculation from INVOICE table).  An amount would show up ONLY when there was an invoice dated in the current month.   

       

      My goal is to display information on every project with a current PO, not only am confused with the initial step of how to make a field show data only from the current month,  I also cannot wrap my head around a way to show "revenue billed in the current month" without getting rid of all of the other records that dont meet this condition.

       

      (There are already 2  sub summaries based on Client and Product, I'd like to avoid adding an additional subsummary) 

       

      Thank you SO MUCH for your advice 

      Susannah

      (I know what I know really well, but there is so much I don't know) 

        • 1. Re: I want it all (A subsummary report showing all open POs and Only income billed in the current month)
          philmodjunk
            

          There's a lot you don't describe about your database that would help us to help you.

           

          If you could list each table and document the relationships your report uses to produce the values you want, that would help a lot. I see you have at least a Job detail table and an Invoices table and you probably have a one to many link between the two so that each job detail record is a line item in the invoice, but I could be wrong. What table are you basing your report on? one of the above two or a third table?

           

          A few items I can respond to immediately:

          "...hand entered to avoid a circular reference in my INVOICE table"

          You shouldn't have to do this. If I've deduced your relationships correctly, you should be able to refer to this field from your Job Detail table using the existing relationship.

           

          "...am confused with the initial step of how to make a field show data only from the current month..."

          I assume you have a date field in Invoices you can use for this. Create a date calculation field that looks like this:

           

          Date(Month(invoiceDate), 1, Year(invoicedatefield)).

           

          Since this will return the same value for all invoices from the same month, you can use it in a relationship to refer to all invoices for a given month. You might want to set up a "self join" for this, depending on how you have structured your report and its underlying table.

          • 2. Re: I want it all (A subsummary report showing all open POs and Only income billed in the current month)
            Susannah
              

            If you could list each table and document the relationships your report uses to produce the values you want, that would help a lot.

            My DB is created using the FMP Template for "Time Billing" as a starting point.  I have added the two tables described below. The new ones are linked to each other and to TIME BILLING LINE ITEMS using the "Customer" field:

             

            >>JOB DETAILS contains "Customer", "project title", "current estimate", "amount billed" and the "hourly rate" time is billed at

            >>INVOICES contains "customer" "Invoice Date" ,  "Rate" (look up from JOB DETAILS::hourly rate) "invoice credit" (Look up from JOB DETAILS::amount billed) and "Balance now due" (Caclulation= Time billing line Items::Total amount - "invoice credit")**

             

            What table are you basing your report on?

            JOB DETAILS

             

            RE: Circular relationship and hand entering.  If I've deduced your relationships correctly, you should be able to refer to this field from your Job Detail table using the existing relationship.

            Yeah, you'd think.  The problem is I want the invoice to reflect the amount previously billed to create the "Balance now due" amount (Time Billing Line Items::Total amount) see ** above.   Teach me!

             

            I assume you have a date field in Invoices you can use for this. Create a date calculation field that looks like this: Date(Month(invoiceDate), 1, Year(invoicedatefield)).

            Im off to try right now.  

             

            In the meantime let me know what other details your require. 

             

            Your time and guidance is appreciated.

            • 3. Re: I want it all (A subsummary report showing all open POs and Only income billed in the current month)
              philmodjunk
                

              More questions:

              "The new ones are linked to each other and to TIME BILLING LINE ITEMS using the "Customer" field:"

              Is there a link from invoices to Time Billing Line Items?

              Is there a link from Job Details to Time Billing Line Items?

               

              There are three table occurrences of Time Billing Line Items in the original template Line_Items_All, LineItemsByBillingID, and LineItems_FilterByMonth_Year. Which did you link to?

               

              Design Comment: Say you have started a project with a customer named "Acme Consulting", but before you're finished, the business changes its name to "Pinnacle Consulting". If you try to update the name in your database, it'll break the relational links to all your other tables. The way to avoid this is to assign a customer ID number to each customer (possibly as part of a customer table where one record = 1 customer) and use it as a key field instead of the name.

              • 4. Re: I want it all (A subsummary report showing all open POs and Only income billed in the current month)
                Susannah
                  

                Design Comment: Say you have started a project with a customer named "Acme Consulting", but before you're finished, the business changes its name to "Pinnacle Consulting". If you try to update the name in your database, it'll break the relational links to all your other tables. The way to avoid this is to assign a customer ID number to each customer (possibly as part of a customer table where one record = 1 customer) and use it as a key field instead of the name.

                 

                Im on the same page with your here.  Actually the field is only called "Customer" in the time billing line items table.  Everywhere else it is called "Job code". In all cases  it is a unique job number representing each project (Example: Y90002)

                 

                More questions:

                "The new ones are linked to each other and to TIME BILLING LINE ITEMS using the "Customer" field:"

                Is there a link from invoices to Time Billing Line Items?   Yes, '"Customer = "Job Code" see response to design recco

                Is there a link from Job Details to Time Billing Line Items? Yes, '"Customer" = "Job Code" see response to design recco

                 

                There are three table occurrences of Time Billing Line Items in the original template Line_Items_All, LineItemsByBillingID, and LineItems_FilterByMonth_Year. Which did you link to?

                When you open a new time billing DB and go to Define Relationships it is located directly below the TIME BILLING table.  In its original form, it is not linked to anything 

                 

                 

                 

                 

                • 5. Re: I want it all (A subsummary report showing all open POs and Only income billed in the current month)
                  philmodjunk
                    

                  OK, sorry for all the questions, but now I'm getting close to getting sufficient detail to venture some suggestions. My installation of FMP 10 did not generate exactly the same relationship graph you describe, when I selected the Time Billing starter solution. That's why I asked for clarification on which table occurrence.

                   

                  I've confirmed the following Relationships:

                  Job Details :: Customer = Time Billing Line Items :: Customer

                  Invoices :: Customer = Time Billing Line Items :: Customer

                   

                  You stated that the tables are "Related to each other by Customer" that would suggest a third relationship between Job Details and Invoices, but that would require a additional table occurrence such as:

                  Invoices :: Customer = Job Details 2 :: Customer

                   

                  It looks like you have a single Job Detail record for a given project with Multiple Time Billing Line Items and Multiple Invoice records for that same project. Correct?

                   

                  OK Revenue billed in current Month:

                  Add an unstored calculation field, "ThisMonth", to Job Details: Date ( Month ( Get ( currentdate ) ; 1 ; Year ( Get ( Currentdate ) ) ).

                  Create the date calculation I described earlier for Invoices and name it MonthYear.

                  Link this to a new table occurrence of Invoices ("InvoicesByMonth"): Job Details :: ThisMonth = InvoicesByMonth :: MonthYear

                  Now return to Job Details and define another calculation field: CurrentMonthBilled,  Sum(InvoicesByMonth :: Invoice Amount).

                  • 6. Re: I want it all (A subsummary report showing all open POs and Only income billed in the current month)
                    Susannah
                      

                    I added a calculation field to my JOB SUMMARY table with the following formula:

                     

                    Date(Month(Invoices::invoice date); 1; Year(Invoices::invoice date))

                     

                    Every field where there is an invoice has the same content "733620"  Impossible to know if it is only finding invoices with August Dates, since all my records started in August.

                    • 7. Re: I want it all (A subsummary report showing all open POs and Only income billed in the current month)
                      Susannah
                         I have to pick up my babies.   I'll reply again when I am able.  Thank you so much for your help so far.
                      • 8. Re: I want it all (A subsummary report showing all open POs and Only income billed in the current month)
                        philmodjunk
                          

                        When you create a calculation, you can set it to return different data types such as text, date or number. The default setting is number and when you set up a date calculation but leave the return type as number, you will get this type of integer value returned. (That's because date fields are really a special kind of number field.)

                         

                        Re-open your calculation definition and change the return type setting (pop-up menu in lower left corner) from "number" to "date" and you'll be able to see what date is being returned.