Hello, I need to create a “Account Statement”, that is, I have 2 tables: Sales and Payments.
I have to get a list where the detail of the current account is clearly seen, as seen below:
Date | Concept | Sale Amount | Amount Paid | Balance |
02/05 | Sale of XX article | 1,000 | 0.00 | 1,000 |
02/10 | Partial payment | 0.00 | 600 | 400 |
The tables have many more fields, so I created a Slice for each table with the necessary fields.
In the Customers table I created a virtual column with the following Expression:
SELECT (SalesSlice [Sale Id], [Customer Id] = [_ THISROW]. [Customer Id]) +
SELECT (SalesSlice [Payment Id], [Customer Id] = [_ THISROW]. [Customer Id])
Do the Columns have to have the same name to be combined? because the records of the second table (Payments) do not appear in the list, but they are counted in the header
How do I control the order of the columns? The table appears as fastTable in the preview, I don’t even see it in the system views, and I can’t create a view either since it’s a “virtual” table
What would be the correct way to do it? This is the only way I can think of, but it may not be the most appropriate. Any suggestion?