Sum service amounts from three tables based on dates

Hi Shawna

Have a look at your three tables, services, products and off-site events, and see if they should be amalgamated into one table with an extra field to indicate what type they are. Adding up amounts from different tables is often a sign that this is the case.

Regards John

The picture in your original post is from which table?

Is Paydate a date field?

What kind of field is Payday in the other tables?

I don't think John is saying your formula doesn't work. If you are storing the same data but in three different tables then I think he is saying you might want to think about your design. Could all the data exist in one table with a new field field that differentiated the records?

Shawna said:
The picture shows the Earnings Table.

 So you have a view element called Earnings in the table called Earnings?

Shawna said:
Paydate is a formula field.

 It looks like Paydate ends up as date data.

When I replaced [year(Payday) = y][month(Payday) = m][day(Payday) = d] with [date (payday) = y and let y:= Date I thought it would work

So Date is a regular date field? If Payday is a date field then you don't need the date() command in front of Payday.

Can you post a sample DB, with dummy data so we don't have anyone's private info?

It took a bit for me to find the picture. It is in the Contractors table not the Earnings table. Also Paydate is a date field not a formula field. So I modified the code in Payment field to:

let a := this;
let s := sum((select Earnings where Payday = a.Paydate and Employees = a).'Total Net');
let v := sum((select Tracking where Payday = a.Paydate and 'Contractor/Consigner' = a).'Total Net');
let e := sum((select Events where Payday = a.Paydate and Employees = a).'Total Net');
s + v + e

and it gives me a total of $1,892.93 for Anita. Which is the sum of records in Earnings. There are no records in Tracking or Events that are linked to Anita.

You can take advantage of the power of relationships by changing your code to:

let a := this;
let s := sum(Earnings[Payday = a.Paydate].'Total Net');
let v := sum(Tracking[Payday = a.Paydate].'Total Net');
let e := sum(Events[Payday = a.Paydate].'Total Net');
s + v + e

This tells Ninox to only look for through linked/related records, thus we can skip the Employee/Contractor part. As you can see as your DB grows, Ninox only searches through a smaller subset of records versus the entire table.