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
10 replies
-
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?
-
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?
-
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.
Content aside
- Status Answered
- 1 yr agoLast active
- 10Replies
- 108Views
-
3
Following