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

10 replies

null
    • 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

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