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. The highlighted field should summarize to 38 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.Duplicate Values Between Two Tables
Hi Everyone, I will preface this with formulas are new to me. I have two tables Table 1 (Preferred Provider): This is the master table of only the preferred providers identified by Provider ID Table 2 (Member Assignments by Provider): This table is the universe of all member assignments-both preferred and non preferred. Providers are identified by Provider ID What formula (and formula field) do I use in Table 2 that will look for a Provider ID match in Table 1 that will populate the new field in Table 2 as yes or no? This app won't have user entries so I don't think I need a relationship. I just need to be able to match the preffered providers assigned in Table 2 to the master preferred provider list in Table 1. I tried something super simple which, as you likely know, didn't work. IF(Size(GetFieldValues(GetRecords("_DBID_ASC_PREFERRED_PROVIDERS.PROV_ID = _DBID_MEMBER_ASSIGNMENTS_BY_PROVIDER.PROV_ID")) 0,"Yes","No")Identifying identical values between two table
Hi Everyone, It's been a while since using QB and my brain appears to have lost QB memory! I have two tables: Preferred Providers eligible for auto-assignment All Providers that have been auto-assigned (regardless of preferred designation) I am looking for a formula that will look for a TIN match between both tables and pull into table 2 under new field "Preferred Provider" Ultimately, I need to identify the auto assigned preferred providers without scrubbing a list of thousands of TINs. Any help is greatly appreciated!
Creating a formula field to capture time intervals
I have a field that captures the time an event occurred, it is a Time of Day field type. These are examples of the output 8:13 am, 12:28 pm. I would like to create a field that lumps times into 1/2 hour intervals. For example, If the Time of Day is =>8:00 am and =<8:29 am then the interval would be captured as 8:00 - 8:30. If Time of Day is =>8:30 am and =<8:59am then the interval would be 8:30 - 9:00, etc. Nothing I've tried seems to work as I repeatedly run into sytax errors. Does anyone have suggestions? Thanks Sheila
Need help with a query formula for ranking dates.
I have a table of Training cycle start dates. Each record is a Training Start Date (FID 6 - Date Field) for a Training Team (FID 25 - Text lookup field from related Table) with a Start Date Status (FID 7 - Text). I have created a Formula Numeric field titled Rank. I need to rank the upcoming Training Start Dates for the Traditional team only, and exclude any dates that are past or do not have a Status of "Available." The one coming up soonest on/after today ranked 1, next ranked 2, and so on. We've tried several formulas without success. var text QUERY = "{6.AF.'" & Today() &"';}AND{25.EX.'Traditional'}AND{7.EX.'Available'}"; If([Start Date Status]<>"Available", null, Size(GetRecords($QUERY))+1) Attempt 1 above failed. Returned rank 11 for all available start dates, past and present. var number position = Size(GetRecords("{6.AF.'"& Today() &"'}AND{25.EX.'Traditional'}AND{7.EX.'Available'}")), $position+1 Attempt 2 above failed. Returned rank 10 for all available start dates, past and present. var text baseQ = "{25.EX.'Traditional'}AND{7.EX.'Available'}"; var text QAfter = $baseQ; var number position = Size(GetRecords("{6.AF.[Training Start Date]}AND{25.EX.'Available'}")); $position+1 Attempt 3 above failed. Returned rank 1 for all dates.
Formula Rich Text Button: Report Link
Hey! Mainly for aesthetic reasons, I am attempting to create a Formula Rich Text Button that just points to the Report Link (Built through a relationship). "<table style='width:100%;border: 0px Solid White'>" & "<tbody>" & "<tr>" & "<td style='vertical-align:top;text-align:Left;'>" & "<div " & $stylebtn & ">" & "<a href='" & [Report Link] & "' style='color: inherit; text-decoration: none;'>Tittle</a>" & "</div>" & "</td>" & "</tr>" & "</tbody>" & "</table>" The Formula returns no errors, but when pressed it just returns back to the Original record. I am trying to get it to open a report of the Child Records using the Embedded for Report. If I can't do it that way (which seems simplest), I think I would have to do a whole query within a URL. Writting a query isn't the problem so much as I am not sure how to direct a URL to use a Particular report with a Report ID. Any suggestions on either how to modify the formula to get it to actually use the Report Link, or on how to generate a Formula URL that goes to the Report but filters using the Query?
Multi-Upload Button
Hi there, We had a form button (URL formula field) that allowed us to upload multiple documents at one time. Here is the formula: var text URL = URLRoot() & "db/"&Dbid() & "?a=dbpage&pageID=4&refFid=7&rid=" & [Project #] & "&fileattachment=36" & "&attachmenttableurl=" & URLRoot() & "db/" & [Project Name]; If(IsNull([Project Manager]), $URL) After the last QB update (last month?) it stopped working. Can anyone assist with this issue? Much Appreciated, Veronica
