+ Start a Discussion

Reporting from two tables - help!

We're a couple of developers who are very new to the Salesforce ecosystem but understand relational database design very well. We're having some difficulty creating a particular report. Here's the scenario:

Tables Involved:
  • Account
  • Donations
  • Ticketable Events
  • Orders
  • Order Line Items

Functionality Requirements:
  • User enters a date range
  • User selects an event from Ticketable Events drop down
  • Users runs report (or presses button) and the following is displayed:
    • List of accounts
    • Whether or not they purchased the selected Ticketable Event (based on the existence of an Order Line Item with keys from both Accounts and Ticketable Event)
    • Aggregated amount of Donations (if any) made by each Account within the selected date range
    • User is given the ability to export the list as a CSV with additional account information such as email address, etc. If this information needs to be in the list presented, that's fine.
We're not afraid of diving into some Apex or Visualforce, we'd just like to get this done as cleanly as possible. We've already tried a Joined Report but that didn't seem to cut it. We figured we'd leverage best practices from all you awesome people.

Thanks for your time. :)
Swayam  AroraSwayam Arora
Hey Jake,

Can you please share the relationships among these tables as well.

Swayam Arora
Hi Swayam,

Sure! Here's a quick breakdown.

Accounts -< Donations
Accounts -< Orders
Ticketable Events -< Order Line Items
Orders -< Order Line Items

In one Order, it is possible to have Order Line Items for multiple Ticketable Events. A single Order Line Item can only be attached to one Ticketable Event. I really appreciate the response, let me know if you need any more details.