TeamDesk Knowledge Base & Support

   Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Running balance for two columns with separate dates in the same table
I have a table that has two amount columns, let's call them "Receivable" and "Payable". Both Receivable and Payable also have their assigned due dates. So that makes up four columns from the same table. Each record has a Receivable and Payable amount. I want to create a running balance view which brings together both these dates and amount columns, sorted by date, and a third column that maintains a running balance. So, it's a union of different columns from the same table stacked on top of each other and sorted by date.

One way I thought of achieving this would be to create another child table with a workflow assignment where two child records are created every time a new record in the parent table is created. All updates in the parent record are updated in the child table. However, I think this is not an efficient way of doing it.

I wonder if anyone has any ideas on how to achieve this within the same table. I know of the running balance database but how do I deal with the two dates?

I have attached a spreadsheet example of what I am trying to achieve.


ID
1975
Category
Customization
Author

Ansar Ahmad
Date Created
4/15/2023 1:23:17 PM
Date Updated
4/16/2023 9:01:16 AM
Comments
Pierre 4/15/2023 5:01:54 PM
Hello Ahmad

I had a similar issue and I dealt with it in the following way (I am quite sure there is a much more efficient way).

1. I created a "date" table. The index of this table is the date, in date format. I create all the dates (10 years x 365 days).
2. I create a link Table1.DateReceivable -> Date (ie each date of each receivable points to a date)
3. I create a link Table1.DatePayable -> Date (ie each date of each payable points to a date record; we have two links from table 1 to Table "Date").
4. In the table date, I create a field calculating the sum of all the Receivable whose date is on on before the date of the record.
5. In the table date, I create a second field calculating the sum of all the Payable whose date is on on before the date of the record.

---
To make the date table lighter
- You do not pre-create all the possible dates.
- Each time you create a receivable, you activate a trigger that will create the date if it does not exists yet (Trigger; if no Exists([DatePayable]) -> Create a record in date Payable)
--
You need then to optimize the date selector / you do this by creating a proxy date field in table 1 (if this is of any interest I will explain)

Kind regards,

Pierre
Pierre 4/16/2023 8:00:01 AM
In addition to my previous message,

There is another way, simpler, which gives you a bit less flexibility in the data display:

You could also create two many-to-many relationships, one for the receivables, one for the payables
The relationships would be associate the Table to itself.
You have to include a criteria Date Payable <=DatePayable (and the same for the receivable relationship)

In the formula of each relationship, create a field summarizing as a total the Payable (Resp Receivable)

Kind regards,

Pierre
Ansar Ahmad 4/16/2023 9:01:16 AM
Hello Pierre

Thanks for your input. I applied the first method that you suggested and it works fine. I will keep it until I find a more efficient way. The last method, I agree, is restrained by the less flexibility in data display.

Thank you for being so helpful.

Warm regards,

Ansar
Feedback
Back to Search Results