Help for using virtual columns (?) correctly or otherwise

[Pratyay_Rakshit]

There’s a table that records all bills from suppliers.

There’s a table that records all payments to those suppliers.

Let’s say, generalizing, one payment is made after 12-13 bills are recorded.

A payment date can not necessarily share a bill date.

What I want is a ledger for bills and payments made out in a pdf.

I have done it up until now by making another table and using actions to send payments and bills as rows to that table. Generating a report is easy after that.

But I don’t want to use an extra table anymore if I can, because its making things slower.

---

Virtual columns can be a way - that I make VCs within the bills table to pull the payment record for that date. But the problem is there are payments on days where there are no bills. So in the report finally those records will be left out.

Can anybody help? My only aim is to get rid of the third table.

[Marc_Dillon]

Pratyay_Rakshit:

There’s a table that records all bills from suppliers.

There’s a table that records all payments to those suppliers.

So, is supplier the common data point here that you’re wanting to report these things grouped by? I assume you have a supplier table, yes? Are these not Ref type columns to this table?

Pratyay_Rakshit:

But the problem is there are payments on days where there are no bills. So in the report finally those records will be left out.

How is this a problem? Why are records being left out if the dates do not match? I do not understand.

Pratyay_Rakshit:

I have done it up until now by making another table and using actions to send payments and bills as rows to that table.

I’m confused about what this third table is. Can you elaborate, preferably with some screenshots?

[Pratyay_Rakshit]

Thanks for an early reply. I was literally waiting on the screen for someone to reply, lol.

Marc_Dillon:

So, is supplier the common data point here that you’re wanting to report these things grouped by? I assume you have a supplier table, yes? Are these not Ref type columns to this table?

Yes they are ref columns to a main supplier table.

Marc_Dillon:

How is this a problem? Why are records being left out if the dates do not match? I do not understand.

Let’s say for Supplier A,

In Bill table, there are 5 records:

1. 21/04/21 - $100
2. 21/04/21 - $50
3. 25/04/21 - $100
4. 26/06/21 - $75
5. 28/04/21 - $50

In payments table, there is one record:

1. 27/04/21 - $200

So, in the bill table, say if I make a VC to pull the payment records, I would miss the 27/04/21 dated payment since there are no bills on that date.

Marc_Dillon:

I’m confused about what this third table is. Can you elaborate, preferably with some screenshots?

Based on the data I just used as an example, this would be the format of the third table. I have actions in both bill and payment table to add rows to this third table in the required format. But they are actions and forget it once and the whole thing fails

[Marc_Dillon]

And how do you want the Report to look?

Marc_Dillon:

So, is supplier the common data point here that you’re wanting to report these things grouped by?

[Pratyay_Rakshit]

Like this:

[Marc_Dillon]

So just a list of all payments and bills, sorted by date?

[Pratyay_Rakshit]

Yes, but I need it in a table-like format like shown

[Marc_Dillon]

Option 1

Restructure your data so that Bills and Payments are in the same Table. Once you have this, your Report can simply be like:

<<START: ORDERBY( FILTER( bills-payments , condition ) , [date] ) >>

---

Option 2

Create a new simple table that is just one big column, holding all possible date values. Your outermost START expression in your Report will iterate across this table, then will have 2 inner START expressions to iterate across either Bills or Payments for each individual date. Like so:

<<START: Dates[Date] >>

<<START: FILTER( Bills , [Date] = [_THISROW-1].[Date] )>>
---display bills---
<<END>>
<<START: FILTER( Payments , [Date] = [_THISROW-1].[Date] )>>
---display payments---
<<END>>

<<END>>