Forum Discussion

WadeMyers1's avatar
WadeMyers1
Qrew Cadet
25 days ago

Summarize a value in the same table

I'm building a new Table [Daily Time Cards], now trying to calculate for overtime. I built formula fields for week and year number, with the idea that I would query the same table for [Related Team Member] those number fields and only render total hours logged for the week, not on the same Record ID (or blank, since could be the case initially).

Example: A user logging time submitted 38 hours previously this week in prior saved time cards. On Friday they have 4 hours to log.

  1. The highlighted field should summarize to 38
  2. The formula for the new timecard should calculate 2 hours at regular time, and 2 hours OT

I attempted to make this field work with a formula Query, then tried doing a self to self relationship. I think the FQ is the way to go, but I get an error "Expecting Records list but found text list" on GetFieldValues.

SumValues(
  GetFieldValues(
    GetRecords(
      "{Related Team Member.EX.'" & [Related Team Member] & "'}" &
      " AND {Week Of Year.EX." & [Week Of Year] & "}" &
      " AND {Record ID#.EX.NOT." & [Record ID#] & "}",
      [_DBID_DAILY_TIME_CARDS]
    ),
    36
  )
)

Recommendations are appreciated.

formula query

1 Reply

  • WadeMyers1's avatar
    WadeMyers1
    Qrew Cadet
    24 days ago

    I figured it out.

    Looks like ChatGPT gave bad advice - insert GetFieldValues into the mix. When I pulled that out I eliminated the error. I also decided not to try calculating the OT value on an individual timecard just for the sake of capturing the day it happened, but instead will break that out in the formula on the payroll page for hours > 40.

    For those who are interested, here's a screenshot of the formula that worked, with comments inline.