Forum Discussion

RyanLocke1's avatar
RyanLocke1
Qrew Cadet
3 years ago

New Formula Queries

I am using the new formula queries to sum the total days billed in an unrelated table like this

SumValues(GetRecords("{96.EX.'" & [Record ID#] & "'}",[_DBID_BILLINGS]),120)

I want to add another field like this just with one more query within it to just return the total days billed in the last 7 days and not sure the best approach.

Thank you!

------------------------------
Ryan Locke
------------------------------
formula queries
  • SeanConnaughto1's avatar
    SeanConnaughto1
    Qrew Cadet
    3 years ago
    Do you have a field that indicates when it started billing? You could do something like

    var number billing = SumValues(GetRecords("{96.EX.'" & [Record ID#] & "'}",[_DBID_BILLINGS]),**Billing Field Value**)

If([Your Previous Formula]>=7,
    $billing * 7,
    
    $billing * [Previous formula with # of days billed]​

    Granted, I have no idea if that will work as I didn't test it first. That is what I would start with though, it seems at least to be in the right path. I hope that helps!

    ------------------------------
    Sean Connaughton
    ------------------------------
    • RyanLocke1's avatar
      RyanLocke1
      Qrew Cadet
      3 years ago

      Hey Sean,

      Thanks for replying! I have a Projects Table that is not linked to the actual time entry records but through a grandchild table and for Project Management I have added a field for Total Days Billed which works great which is the formula above which is saying Get Records where field 96(Project Number) in the Billings Table exactly matches the Record ID from the Projects Table and then sum Field '120' in the Billings table and return the total to the 'Total Days Billed' in the Projects Table. I am wanting to take that one step further and add another field in the Projects Table the sums the billing for the last 7 days versus all time.  So I am trying to figure out how to use the Date field from my Billings Table in my query like this:

      if(ToDays(Today()-[Date])<=7,SumValues(GetRecords("{96.EX.'" & [Record ID#] & "'}",[_DBID_BILLINGS]),120),0)

      But not sure how to do it since I am having to query those records as well and get them from the Billings table.



      ------------------------------
      Ryan Locke
      ------------------------------