7 Replies Latest reply on May 27, 2012 6:06 AM by MarshaRupel

    Last Week Sales by Client

    MarshaRupel

      Title

      Last Week Sales by Client

      Post

      Hello!

      I am stuck here....I have a layout (Finances) that I have a button (Find Sales Last Week) and also 2 global date fields (g Start Date and g End Date) to do a find by a date range.  The global date fields are in my Transactions table.  I also have the fields: Total Sales Last Week, Total Cost of Goods, Total Shipping, and Total Tax.  They are all summary fields in my Contacts table.  Transactions is linked to Contacts by Client #.

      My Find button script is:

      Set Error Capture [On]

      Enter Find Mode []

      Set Field [Line Items::g Start Date; Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate )) - 6

      Set Field [Line Items::g End Date; Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ))

      Perform Find [Restore]  where:

      Contacts::Total Sales Last Week = Line Items::g Start Date & ".." & Line Items::g End Date

      Contacts::Total Cost of Goods = Line Items::g Start Date & ".." & Line Items::g End Date

      Contacts::Total shipping Cost = Line Items::g Start Date & ".." & Line Items::g End Date

      Contacts::Total Tax = Line Items::g Start Date & ".." & Line Items::g End Date

      If [not Get (FoundCount)]

        Show Custom Dialogue

        Exit Script []

      End If

      Show All Records

       

      A couple of issues here:  my Find button does not return total sales, etc for the prior week nor does entering a date range return any totals and when I click on the tab Finances it goes to another client's record other than the current client I was working with.  Then when I cancel out of the find it goes to the very first record instead of staying on the current record.

       

      Any help is appreciated!

       

        • 1. Re: Last Week Sales by Client
          GuyStevens

          You are trying to search by the global field. That's not the idea. You should use your global fields to enter the beginning and the end dates. But because your button already says "Find Sales Last Week" we don't really need the global fields in this case.

          The idea of the global fields is that you are on a certain record and you enter a beginning date and an end date. Then, because yhose fields are global, you have those two dates available on every record. So you can enter find mode and use these dates to enter a search criteria in the fild you want to search in.
          That would be the date field; And if you want to search a date range the correct form is: BeginningDate...EndDate

          The actual dates are in your date field so you search this range in your date field.

          First question: Does your line items table have a date field?
          Normally speaking you would have the date field in the "Transactions" table.

          But assuming the line items table has a date field. What you need to do is something like:

          Set Error Capture [On]

          Enter Find Mode []  - Clear pause box

          Set Field [Line Items::Date; Get (CurrentDate) -7 & "..." & Get (CurrentDate)]

          Perform Find

          If [Get (FoundCount) = 0]

            Show Custom Dialogue

            Show All Records

           Halt Script []

          End If

           

          In this script I set the dates without using the global fields. If you want to enter a start date and an end date in the global fields and search by them your set field will look like this:

          Set Field [Line Items::Date;  Line Items::g Start Date & "..." & Line Items::g End Date]

           

          Now if your date field is in your transactions table You just change the "LineItems::Date" with the "Transactions::Date"
          But this is of course assuming these tables are linked by the transactionId or something like that.

          • 2. Re: Last Week Sales by Client
            MarshaRupel

            Hi DaSaint,

            I want to be able to have the option of doing either finds: Sales Last Week or Sales by Date Range.  I changed my script to the above - the first script is attached to the button "Find Sales Last Week" and then the same script with the other set field for the "Find Sales by Date Range" button.

            I am not getting the total sales for the prior week ($384.73) or for the date range entered either.  I am getting 5 records though (all for the same client) but all the totals are the same ($107.50) which is the total for the very last invoice from the prior week.  Plus when I click on my Finances tab from the Contacts tab it jumps to a different client record.

            I do have an Invoice Date field in both Transactions and Line Items but changing them in the script did not change the totals.  I tried it both ways.  Set Field [Line Items::Invoice Date; Get (CurrentDate) -7 & "..." & Get (CurrentDate)] and Set Field [Transactions::Invoice Date; Get (CurrentDate) -7 & "..." & Get (CurrentDate)]

            The totals for the prior week or by date range I am looking for are:

            Total Sales Last Week (Calculation - Unstored, from Contacts, = Sum (Cost of Goods; Shipping Cost; Tax)

            Total Cost of Goods (Calculation - Unstored, from Contacts, = Transactions::Sub Total)

            Total Shipping (Calculation - Unstored, from Contacts, = Sum (Transactions::Shipping Cost)

            Total Tax (Calculation - Unstored, from Contacts, = Transactions::Tax)

             

            Thanks for all your help!

            • 3. Re: Last Week Sales by Client
              GuyStevens

              How come these totals are from contacts?

              Aren't they supposed to be in your Lineitems and your invoice table?


              Normally speaking you calculate, in the line items, the total per line item.
              And then you have a summary field that calculates the sum (total)  of the total field.

              Here's a list of fields you need per table:

              Invoice Table:
              Id - A number field set to auto enter a serial value
              Date  -  A date field
              ContactIdFk   -  A number field that stores the ID of the contact
              Tax  - Calculation of the tax amount
              s_Tax  -  A summary field that calculates the total of Tax
              GrandTotal  -  s_SumTotal + Tax
              s_GrandTotal  -  A summary field that calculates the total of GrandTotal


              ...

              LineItems Table:
              Id - A number field set to auto enter a serial value
              InvoiceIdFk  -  A number field that stores the ID of the Invoice the line item is related to.
              ProductIdFk  -  The Id of the product (if you have a table of products)
              PriceLookup  -  A lookup bumber field that looks up the proce of your product.
              Amount  -  A number field that has the amount of the product
              Total  -  A calculation that calculates 'PriceLookup * Amount'
              s_SumTotal  -  A summary field that gives you the total of the total field
              ...

               

              Then you make a new layout that's a list view, based on the invoices table. You include following fields:
              Date  -  Of the Invoice table.
              Contact  -  Of the Contacts table
              Tax  -  Of the Invoice table
              GrandTotal  -  Of the LineItems Table

              In this list view you add a "Trailing grand summary part"
              To do this: in Layout Mode go to "Layouts" - "Part Setup"

              Then to this new part, on the layout you add the fields:
              s_Tax
              s_GrandTotal

              This way, in your list view you will see a list of invoices, with the amount per invoice and the grand total of all of these found invoices at the bottom.

              If you want an example of a list view with totals you can look at a recent example I made:
              http://dl.dropbox.com/u/18099008/Demo_Files/BillsMonthly.fp7

              The idea is that your totals come from summary fields, and that they are displayed in a layout part or in a portal.


              • 4. Re: Last Week Sales by Client
                GuyStevens

                Or alternatively you don't create a list view but you just put these s_Sum fields on your invoice layout. That should work to.

                • 5. Re: Last Week Sales by Client
                  MarshaRupel

                  Hi DaSaint,

                  I already have an Invoices tab with the invoices (but no totals).  I have put the fields you mentioned on the Finances tab but I am still not getting the total for the week.  I am getting the total for just one of three test invoices (the latest invoice total only).  The summary total fields seem to be okay.

                  You wrote:

                  Normally speaking you calculate, in the line items, the total per line item.
                  And then you have a summary field that calculates the sum (total)  of the total field.

                  Here's a list of fields you need per table:

                  Invoice Table:  I call mine Transactions
                  Id - A number field set to auto enter a serial value 
                  Date  -  A date field  This is the invoice date field...right?
                  ContactIdFk   -  A number field that stores the ID of the contact
                  Tax  - Calculation of the tax amount
                  s_Tax  -  A summary field that calculates the total of Tax
                  GrandTotal  -  s_SumTotal + Tax
                  s_GrandTotal  -  A summary field that calculates the total of GrandTotal

                  LineItems Table:
                  Id - A number field set to auto enter a serial value
                  InvoiceIdFk  -  A number field that stores the ID of the Invoice the line item is related to.
                  ProductIdFk  -  The Id of the product (if you have a table of products)
                  PriceLookup  -  A lookup bumber field that looks up the proce of your product.
                  Amount  -  A number field that has the amount of the product
                  Total  -  A calculation that calculates 'PriceLookup * Amount'
                  s_SumTotal  -  A summary field that gives you the total of the total field

                   

                  Then you make a new layout that's a list view, based on the invoices table. You include following fields:
                  Date  -  Of the Invoice table.
                  Contact  -  Of the Contacts table
                  Tax  -  Of the Invoice table
                  GrandTotal  -  Of the LineItems Table  No mention of this field in the above.  Did you mean from the Invoice (Transactions) table?  I did create it in the Line Items table anyway.  But it was not working for me either.

                  The fields that I now have on a 'form' only layout are:

                  Date - Transactions::Invoice Date

                  Total Sales - Transactions::Running Total - Summary  = Total of Invoice Total   Where Invoice Total is a Calculation Unstored, = Sum (Cost of Goods; Tax; Shipping Cost) and Cost of Goods is a Calculation  Unstored, = Sum ( Line Items::Extended Price )

                  Total Cost of Goods - Transactions::Total Cost of Goods - Summary  = Total of Cost of Goods

                  Total Shipping Cost - Transactions::Total Shipping Cost - Summary  = Total of Shipping Cost

                  Total Tax - Transactions::Total Tax - Summary  = Total of Tax

                   

                  My script is like you said above with Set Field [Transactions::Invoice Date; Get (CurrentDate) - 7 & "..." & Get (CurrentDate) - What I am looking for is the totals for the week ending on Saturdays.

                  Also, when I cancel out of the find, when no records are found, it goes to the very first client record.  How can I make it so it stays on the same client record I was on?  So I can enter another search criteria.

                  Thank you for all your help!

                  Marsha

                  • 6. Re: Last Week Sales by Client
                    GuyStevens

                    Date  -  A date field  This is the invoice date field...right?

                    Correct

                    GrandTotal  -  Of the LineItems Table  No mention of this field in the above.  Did you mean from the Invoice (Transactions) table?

                    Sorry, I meant the s_SumTotal from the LineItems Table.

                      I did create it in the Line Items table anyway.  But it was not working for me either.

                    What did you create exactly?

                    Did you look at the example file I provided?

                    There you can see how I did it.

                    Maybe if you could provide me with some screenshots of what you've done, or a copy of your actual file (maybe without data) then I can see a little more, because right now I can't really guess what want wrong.

                     

                    P.S. Did you make a relationship between:

                    Transactions::ID---------------------LineItems::TransactionIdFk

                     

                     

                    • 7. Re: Last Week Sales by Client
                      MarshaRupel

                      I have the cloned copies but not sure how to get them to you...

                      I did look at the example but still couldn't get mine to work.

                      I believe I have the right relationships...