I doubt that it's going to be possible to just run a report of all promotions showing their sales because in fact there can be competing overlapping promotions applying to the same sales.
But here is an approach where you can look at the sales for a particular promotion.
Create a new tale called Promo Results with 1 and only 1 record in it. Make a relationship to the promo table with the Promo table being the Parent.
Select a promo and lookup the relevant promo details. The Promo Results now knows those details.
Make a relationship to Sales where 1 Promo result has Many Sales and set the reference field on the right side of the relationship to be a formula field called Link to all sales with a formula of 1.
Lookup all the various promo details down to Sales.
Make a report of sales which meet the Promo Results lookup fields. Call that report Promo Results. BUt on fact I strongly suggest that you make a formula checkbox field called "Sales meets selected Promo Results criteria"
On the promo results form put the Report Link field for sales on the form and show thee results on the form. Set the form to use the Report called Promo Results. You can make a summary field to show the total sales.
The Promo results form will now show all the sales.
Once that its working you can put icng on your cake by making a URL formula button which when clicked on a promo record, will edit the Promo results record to load that Promo and then display the promo results record. So then its just 1 click from a promo off a URL button called show Promo results and you will see the sales.
Note also that you don't need to limit the Promo results to just showing a list of the sales. You can include that same reports link field on the Promo results form many times, and show say sales charts to see how the promo unfolded over time.
------------------------------
Mark Shnier (YQC)
markshnier2@gmail.com
------------------------------
