BANK RECONCILIATION: Get data from different tables and make a new table

[peterson]

Hello guys, I am making an app for a company, and I have the following tables:

1. Payments
2. Receipts
3. Bank wires

An in these tables I have a bank ref column. But I need to create a bank reconcilliation view, where I have the daily credits and debts. However this information is partied in those 3 tables and I need them to be something like:

I am thinking of how I can achieve that result, how I can join different data into one table

Help!

[marcossglr]

Based on what I can understand fron your statement, you want to create a report view, like a balance record, I guess....

If I'm not wrong, it's not possible to achieve it just adding a new table to the source, because the tables are made to manage data, not to fetch it. Unless you want to create a solution right in your data source (if you're using Google Sheets).

You need to create a slice based on your "payments" table, but this table has to have a particular structure so your information is returned and filtered correctly.

[peterson]

Yes, I can't see an easy way out. I'm thinking of creating Manu formulas inside  a tab in my sheet, but it will make it heavier to sync, I'm also thinking of redesign my database. 

[peterson · Accepted solution]

I found a way to do that.

I created a table with a column called DAY then I added 31 rows with number 1,2,3,4 so forth.
Then I created virtual a virtual column with a SUM(SELECT()) and I summed the entries that the DAY(Date) was equal to this row.day. I also used AND() to add other criteria. I also created a filter table and made it a DASHBOARD view.

I was thinking the whole day how I could achieve that lolol