Access data before Security Filter Date Range

[Rachit_Taparia]

Hey There!

So, I'm building a stock management app, and had a quick, but major query.

Since there will be loads of data (coming from a cloud MySQL server), I've provided a star date & end date security filter. Now, since it's a stock app, I'm using Opening & Closing Balances as Virtual columns in the stock ledger. The issue is I don't know how to calculate opening balances, since I wouln't have access to the date range before the selected security filter...

Example:

Let's say the user picks 1st Jan to 10th March as the date range. Now, in order to calculate opening balance as on 1st Jan, I would need all data before 1st Jan as well (since Opening Balance on 1st Jan = Closig Balance on 31st Dec).., but I obviously won't have access to that..

So, can anyone suggest how do I go about it?

Thanks:))

[TeeSee1]

Couldn't you setup your security filter so that the start date is one biz day before the date the user specifies? You can hide this date's data using a slice or something..

[WillowMobileSys · Accepted solution]

There are two things you can do:

1)  Only support date ranges that coincide with your Opening/Closing Balance periods.  I think you are suggesting that period is 1 year right now.  If that is too much data, then consider moving to Monthly Opening/Closing periods.  You can still use the results of those to compute the Yearly Open/Close.

2)  Move the Opening/Closing Balance columns to "normal" table columns and keep them UPDATED through actions or automation.  Then you will have values you can access no matter what inventory data you are showing.  This will also help with Sync times as you will be removing the Virtual Column calculations from the Sync.  The flip side is that it is more implementation within the app to identify when and do the updates.

I hope this helps!

[SkrOYC]

I suggest this second option.

You can even have individual tables for any close you need. Yearly, Monthly, Weekly.. etc.

Then a bot can populate a new row at the end of each year, month or week

[Rachit_Taparia]

Thanks. I think I'd prefer the second option. But then, would that not mean that even one change in a back-dated entry would require changes to every single row after that....?

[SkrOYC]

Afaik, old data shouldn't change

[Rachit_Taparia]

What I mean is, let's say I had entered a bill dated 23rd March under sales, and then I have to enter another bill dated 22nd March...it would need change in balances..!

[WillowMobileSys]

Once a period is closed it should be closed and LOCKED.  That is the whole point of a Close.

If you are referring to before a Close, then any updates should correct related values and perform any necessary updates.

When you say..."one change in a back-dated entry would require changes to every single row after that"... what rows are you referring to?