Counting Instances of String across records for use in a Gauge Chart
Hi all, I want to use a gauge chart the displays the number of records that contain the letter 'R' as part of a string of in a 'Job Number' field, out of the total number of records in the table. Typically a job number field value would be in the format of 00000_00, or 00000_A0, or 00000_R00. With R denoting revised. I've tried using a query report formula, but whilst it's a valid formula attempt, it displays 0. The total number of records for the gauge var text QUERY = "{6.EX." & Contains([Job Number], "R") & "}"; Size( GetRecords($QUERY)) My logic to the above is that its querying the data table, for job number field (ID 6) to see if it contains a string with an 'R' in it, and if so count the total number of true records. Any ideas? It would be awesome if the gauge can be coloured to show the revised element in red, but the remainder of the gauge in green. But I really think that's beyond the limits of what is possible. However, if anyone has an idea how this could be accomplished, that would be absolutely appreciated!
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?
