Merging multiple tables together, to output a 'client statement' report

[Karl_at_Easy_La]

I have a structure, similar to the below simplified version:

• EndOfMonthReports
  • ClientId
  • Date
• Payouts
  
  • EndOfMonthReportId
• Bookings
  • EndOfMonthReportId
  •  
• Expenses
  • EndOfMonthReportId

Payouts, Bookings, Expenses all fit the template whereby they all have a Date, Description and Amount. They all have very distinct fields, so I dont believing merged them all into one table would make sense. Each of these entry in a way effects the client's account balance, either with a positive transaction, or a negative transaction.

 I would like to be able to generate a report, where one can get an account statement per client, for a particular time period.  What this would do is if you say generate it from Jan 23 - Jun 23, it would get all the end of month reports within that date, and show a list of all these distinct transactions, in the form of:

• 01/01/2023 | Booking | Reservation from 1 - 10 January | +700
• 10/01/2023 | Expense | Management Fee | -200
• 30/01/2023 | Payout | End of Month for June | -500
• ...

I'm not sure if this is possible natively, or with some third-party tools. I'm open for any ideas.

[TheTimeSavingCo · Accepted solution]

Yeap, you'd need a new table where each record represented a single statement, and you'd link the appropriate records to it based on the date

Should be automatable assuming you know all the permutations of the time periods you're providing said report for too

For example, if you know that you're providing 6 month reports, you could have an automation that would trigger whenever an "Expense" record got created, and its action would be to link it to the 6 "Reports" records that it's a part of (where I'm assuming since you're providing 6 month reports, that month will be a part of 6 reports, all with different start / end months)

[Karl_at_Easy_La]

Yes, it seems like that is the only way. I will try to see a way how I can populate this table automatically using automations, while keeping it in sync.  Thanks for the pointer!