Help for using virtual columns (?) correctly or otherwise

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.

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?

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

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

1 Like

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

Yes they are ref columns to a main supplier table.

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.

image
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

And how do you want the Report to look?

1 Like

Like this:

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

1 Like

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

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>>
1 Like