0

Sum related field with specific criteria issue

I have a table called Projects with a reference to a table called Proposals, and there can be multiple Proposals per Project. Within Proposals I have two fields; Total Cost and a Yes/No field called Awarded.

I can see all the Proposals within the Project, however I want an Awarded Total Value Formula field that ONLY sums the value of all the Proposals with Awarded marked as Yes. When I try to create a sum where Proposals.Awarded = Yes, it is summing ALL the Proposals that are awarded, even if they are not associated with the Project.

Any suggestions on how to code this so I get only the Value of the Awarded Proposals for that specific Project? Thanks in advance!

7 replies

null
    • Cloud DevOps Fullstack Engineer
    • Martin_Mueller
    • 2 yrs ago
    • Reported - view

    How about
     

    let project := this;
sum((select Proposals where Awarded = Yes and Project = project).Costs);
    • Holly_M
    • 1 yr ago
    • Reported - view

    Thanks Martin and it yielded basically the same results

      • Cloud DevOps Fullstack Engineer
      • Martin_Mueller
      • 1 yr ago
      • Reported - view

      Holly M mhh can you upload the related part of the app :) ?

    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view

    Since Proposals is a child of your Projects table, you don't need the select() function.

    It should work like this:

    sum(Proposals[Awarded].'Total Cost')

    • Holly_M
    • 1 yr ago
    • Reported - view

    Looks like I am all good now folks and thank you for the help!

      • Fred
      • 1 yr ago
      • Reported - view

      Holly M When you get a chance can you post your solution so other people in the same situation can learn from what you did?

      Thanks,

    • Holly_M
    • 1 yr ago
    • Reported - view

    Per the suggestion of RoSoft_Steven using the sum(Proposals[Awarded].'Total Cost') gave me  just the Awarded sum of the Total.

Login to reply

Content aside

  • 1 yr agoLast active
  • 7Replies
  • 84Views
  • 5 Following