I’m working on a paystub formulation and have everything set including a rolling YTD total using the SUM field; however, I need to be able to pull up older checks and because my YTD is a sum of all entries, old and new, the YTD is showing today’s YTD and not the pay stubb’s.
I’m looking for some way to make a formulat that sums a particular field but only through the date of the paycheck.
Any ideas?
Hi Kim,
Not sure I understand, happy to connect if you want to walk through the scenario.
Just emailed you. Thanks!
Hoping someone else can help me with this. Carl is no vacation 
*on vacation
I thought you’d managed to connect with another Knack Expert hence why our meeting was cancelled? I’m back from vacation w/c 13th Sept if you still wish to discuss to see if I can help. 
This is a very typical problem when building database apps - and it’s made more confusing because it is something which is generally easy to achieve in a spreadsheet!
Knack does not have the ability within an Object to sum values from other records in the same object unless the records are Connected (i.e. a connection between the object and itself). I have set up an object which illustrates this - hopefully the field names make this fairly self explanatory…
So February’s record is connected to January’s and March’s to February
Using this method, you can build a set of connected record within the object which will contain your running sum. At the beginning of the year you simply set up the first record without a connection to an earlier one.
Then there is the question of how to use this in practice from a user point of view. The simplest is simply to have the user manually select the correct Previous Record (a drop down filtered by the staff member would be best) - but there is another possible way which is to have a Record Rule which creates the NEXT record when you save the current months one - I have tried this and I have had an issue with the record rule which I have reported to support. I’ll update when I hear back.
There is a third way to achieve this which would be to use Integromat to calculate it. When the latest record is added / edited get Integromat to find all previous ones in the year, add up their figures and post the total as a running sum into the record just added. There are potential complications here if an earlier record is updated because it would the be necessary to update any record after that date.
No worries, I was just on a time crunch and you asked if I had connected with anyone else, which I hadn’t. I missed our appt due to a family emergency that came up that morning. So sorry, totally appreciate you making yourself available. If I can’t figure it out, I will reach back out when you return.
Thanks for your respone @JulianKirkness I will read through this and try to process it (mentally) haha. I think this may work. I appreciate your respose!
Also, can you provide the formulas you used on the 2 equation items?
The Previous Record connection is a link to the same object (called a Self Join) - there is an issue with automating this approach which support have confirmed - the creation of the new record does not correctly link them - the connection is made in the current record rather than the ‘child’ (new) record.
Here are the two formulae though:
Hope this helps!
Julian
Hi Julian
Greetings from Perth Australia.
Thats a great solution. I have been searching for ages trying to find a solution
Can you clarify how the connection works.
I have tried setting this up, and the calc are not working.
I am not sure which way the connection works ie which is the Parent field and which is the child field?
Also is it a 1 to 1 or 1 to many?
Also, I am not sure how to set the first record without a connection. Is it just a matter of leaving the field blank?
thanks in Advance
Mike
