Could someone please help me. I want to be able to total all sales made in the current calander year.
The cleanest method would be to add the sales to a field every time you make one, and deduct, if you had to refund the funds.you could have a global number field that you add to.
Now, if this is not an option because you want to tally up something that's already there and not going forward, you could create a calculation. You would need to link to the sales table via the sale date to another table that has the current date and the first date of the year. Then sum the sales amounts from the second table.
Hope thus helps.
Sent from my iPad
Not questioning your answer, just trying to understand...
Assuming that every sale is a separate record and they are all stored in the same table, wouldn't a simple list view with a summary field do the trick? The perform find for date range could be scripted. This would provide the option to look at sales not only for the calendar year, but by month, week, day, random dates - and provide lot's of options for reporting with sub-summaries.
noushie wrote: Could someone please help me. I want to be able to total all sales made in the current calander year.
Either find all sales in the current year and use a summary field to produce the total, or define a relationship to show only records from current year and a calculation field to sum them.
Create these fields:
Year = Year(DateCreated) or InvoiceDate
ProductIDPlusYear = Product ID & "T" & GetAsNumber(Year)
Where ProductID is a unique identifier for each unique part sold
ProductIDPlusCurrentYear = Product ID & "T" & Year(Get( CurrentDate))
Create relationship based on the two fields above
called say: RetailSparesSaleList_ProductIDPlusYear
Change the name to suit your current table name, mine is called RetailSparesSalesList and this is the line item table for the RetailSparesSalesInvoices Table
If you only have one table with the invoiced sales items in use that.
TotalSales = InvQuantity * SoldPrice
YearSalesByPart = Sum(RetailSparesSaleList_ProductIDPlusYear::TotalSales)
This gives a rolling twelve months that automatically gives sales in the calendar year. the downside is that the more sales the slower the calculation but in the main it works reasonably well.
Otherwise use the summary based on a date range as mentioned by Michael Huber.
He wanted ALL sales not sales per product.
Yes, what I've proposed does just that assuming that the sales have a unique ID per item which they should if the database is set up properly
noushie I think you need to tell us more about the DB structure: where the sales are kept, are they in one table, in one file or scattered about the place:)
Every sale is a separate record and they are all stored in the same table. I want to have a field at the base of a portal summarising total sales for that client in the current year. I already have a Total field summarising total sales made over the life of the clients relationship with the company.
How is the portal displaying the records?
Is it all one customers records, all customer records, records for a calendar year for one customer and what relationship are you using to display the portal records. this info will help to get the best solution.
Sorry, I misunderstood, you are correct another sum would be neede to add up all the totals over the entire year
Assuming a stored CustomerID in the Invoice Table.
Calculated fields: 'CIDPusYear' = CustomerID & " " & Year(CurrentDate) Unstored, 'CIDPusYearSales' = CustomerID & " " & Year(InvoiceDate) Stored
Self Join Relationship between the two fields in the Invoice Table (CustIDToInvoiceIncDate)
Calculated Field: sum(CustIDToInvoiceIncDate::InvoiceTotal) In the Invoice Table
New layout in Invoice Table with a portal from the new relationship, show whichever fields you want from the Invoice Table
Place the Sum(CustIDToInvoiceIncDate::InvoiceTotal) The field will show the rolling year sales for each customers record you are on.
To smarten it up you could have the 'CustomerID & " " & Year(CurrentDate)' field reference a global field instead of the Year(CurrentDate) containing a drop down of years then it would be easy to compare by changing the year in the global.
Assuming Fields called CustomerID and InvoiceTotal, change to suit.
Haven't tried it with lots of records and there are probably better ways so see how it works for you.
Since version 7, you don't need to concatenate matchfields. A relationship defined as:
Clients::ClientID = Sales::ClientID
Clients::cCurrentYearStart < Sales::Date
will work just as fine.
cCurrentYearStart would be an unstored calculation field (result is Date) =
Get (CurrentDate) - DayOfYear ( Get (CurrentDate) )
Sorry, I'm not quite sure which fields not to concatenate. Which does the above relationship replace?
These two concatenated fields:
'CIDPlusYear' = CustomerID & " " & Year(CurrentDate) Unstored,
'CIDPlusYearSales' = CustomerID & " " & Year(InvoiceDate) Stored
Can be handled in the relationship itself but in a slightly different way. there is nothing wrong with using the concatenated route although it adds the overhead of two or more extra fields and may not appear as elegant as the same calc being done in the relationship. Try the way that makes sense and get it working, all code is iteration and improvement and it's a good way to learn.
Retrieving data ...