0

# Sum service amounts from three tables based on dates

Hello. I pay my employees weekly based on how much they bring in for services, products and off-site events (three separate tables) throughout the week. I am using a date field. Here is a screen shot and I used the below code.

let y := Date;
let a := this;
let s := sum((select Earnings)[date(Payday) = y][Employees = a].'Total Net');
let v := sum((select Tracking)[date(Payday) = y]['Contractor/Consigner' = a].'Total Net');
let e := sum((select Events)[date(Payday) = y][Employees = a].'Total Net');
s + v + e

Thank you, Shawna

## 10replies

• John_Halls
• 6 mths ago
• Reported - view

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

• Shawna
• 6 mths ago
• Reported - view

Hi John,

It's not the tables because it works if I have the date as separate pieces where it's a year box, a month box and a day box. That formula is

let y := Year;
let m := Month;
let d := Day;
let a := this;
let s := sum((select Earnings)[year(Payday) = y][month(Payday) = m][day(Payday) = d][Employees = a].'Total Net');
let v := sum((select Tracking)[year(Payday) = y][month(Payday) = m][day(Payday) = d]['Contractor/Consigner' = a].'Total Net');
let e := sum((select Events)[year(Payday) = y][month(Payday) = m][day(Payday) = d][Employees = a].'Total Net');
s + v + e

With this formula I have to have a separate calendar (desk calendar or wall calendar) available to see what date I'm looking for each time, which is difficult when I'm not in my home office. I want to change it to the date field so I can see the calendar and choose a date. 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 but it doesn't so I'm just missing that one small part.

• Fred
• 6 mths ago
• Reported - view

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
• 6 mths ago
• Reported - view

Hi Fred, The picture shows the Earnings Table. The other two tables are below it.

Paydate is a formula field. Payday is a date field in all the tables.

I don't think he was saying that either but the tables are different because they have very different uses that I don't want to mesh. It also helps me see what I receive from one category easier. I'm just taking the total from each table and adding them together based on a payday that I put for earnings, tracking and events.

• Fred
• 6 mths ago
• Reported - view
said:
The picture shows the Earnings Table.

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

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?

• Shawna
• 6 mths ago
• Reported - view

I'm not so great at coding but I think I exported correctly.

• Fred
• 6 mths ago
• Reported - view

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.

• Shawna
• 6 mths ago
• Reported - view

Thank you Fred, this works. I also noticed what I sent you had different names in my test database than in my active one so I had to change my form to what your code says. Next time, I'll make sure my test db and active db have the exact same names. You are a gem!

• Fred
• 6 mths ago
• Reported - view

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.

• Shawna
• 6 mths ago
• Reported - view

Fred I love this! Thank you again. The shorter the code, the easier for me to understand it better on my learning journey. Also per you and John's advice I scrapped tracking and will keep events. I can weave tracking into earnings.

## Content aside

• 6 mths agoLast active
• 10Replies
• 90Views
• 3 Following