Forum Discussion

RyanLocke1's avatar
RyanLocke1
Qrew Cadet
4 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...
formula queries
SeanConnaughto1's avatar
SeanConnaughto1
Qrew Cadet
4 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
4 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
------------------------------