Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
7 years ago

Summing up a numeric field from different entries.

Hi, I would like to ask how to solve the example below: Under the Salary table, I have three records: Record 1, Record 2 and Record 3. Record 1:   Month: 201902   Money: 80 Record 2: ...
Formulas and functions
QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
7 years ago
no problem,
Just make a new table called Years and load it with a bunch of years.
2019
2020
2021  etc

then make a formula numeric field on your Records table

ToNumber(Left([Month],4))

Then use that field in a relationship to Years and sum up your records.


QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
7 years ago
I think that this will work
var number YYYYraw = ToNumber(Left([Month],4));
var number MM = ToNumber(Right([Month],2));

var number YYYY = If($MM<=2, $YYYYraw - 1, $YYYYraw);
var number NextYYYY = $YYYY+1;

"FY" & Right(ToText($YYYY),2) & "/" & Right(ToText($NextYYYY),2)

The entries in your years table will need to be in that that FY format as well like FY17/18