IF AND Formula
I am trying to write a report formula that is triggered off a checkbox and two different dates that I would typically write an IF AND formula. Anyone know how to make this work? It is giving me an 'expecting ,' error. The Cybersecurity field is a checkbox. The Engagement Begins is a date field. The Cybersecurity Discovery Engagement Ends is a date field. The logic should read if the Cybersecurity checkbox is checked AND the Engagement Begins field is blank, then the field should read "Upcoming", but if the Cybersecurity checkbox is checked AND the Engagement Begins field is not blank, and the Cybersecurity Discovery Engagement Ends field is blank, then the field should read "Assigned", otherwise show nothing.
Count instances of value in table prior to date
Hi everyone, I'm trying to find a way to create a field that counts the # of times an email address exists within a table based on a date field within that record. Example: Record is created with an email address and a date of interaction. Goal - return the # of times that email address already exists in other records on or before the date of that interaction. My formula query currently is: Size(GetRecords("{145.OBF.'" & [Date of Interaction] & "'}")) Which DOES work, but it's counting huge numbers, and I don't understand why. The results are in the thousands, for an email address that if I search for it, only exists 21 times. I *think* it's because my field 145 is also a formula query which reads - Trim(If([NEW Contact Email Address]="",[Contacts Table Field - Email Address],[NEW Contact Email Address])) I'm wondering if it's not counting the results in the field, but query; however, when I switched the field to the field 'NEW Contact Email Address] I returned tens of thousands of records rather than the very few times a staff member manually entered it as a 'NEW' contact rather than looking up the email from our contacts table. Ideas? and thank you!
Get Record IDs from Report Link
I have three tables Templates, Template_Events & Template_SubEvents. Templates has a relationship with Template_Events, Template_Events has a relationship with Template_SubEvents, and Template_SubEvents has two self-identifying relationships (this allows for different Template_SubEvents to be selected in two places on the form). What I'm needing to do is prevent a circular dependency in the Template_SubEvents table. What I've come up with is using a Text field (Hierarchy List) to store the Child record(s) ID and the Child records of its Parent. For example: If I have Record IDs of 1, 2, 3 & 4. 2 is a Child of 1. 3 is a Child 1. 1 is a Child of 4. In the Hierarchy List for ID# 4 would be "1,2,3". In the Hierarchy List for ID# 1 would be "2,3". So ID# 4 wouldn't be able to be the Child of the records 1, 2 or 3. A formula field would compare the contents of Hierarchy List field to the selection made in the Parent_SubEvent ID field and then a Dynamic Form Rule would display a message if the formula equaled 'Y'. I'm attempting to get the Child Records from a Report Link but that is proving difficult. I have the current formula ToText(GetFieldValues(GetRecords("{228.GT.0}", "bt6eizk88"), 178)) but it's displaying the same values for ALL records. fID '228' is a summary field that counts the number of Child records. fID '178' is one of the Report Link fields. Am I on the right track for something like this or is there a better way to prevent a circular dependency? I thought about creating another table and storing Child, Parent and Grand-Parent ID but got feedback to try and make the Hierarchy List field work.
Formula Help
Hello, I need help combining text fields into a formula(s). Here are my formulas: [Finance Disposition]=""&[(Text) PipeLine Value Requested]="<$500,000"),"YELLOW") I'm getting the operator "=" can't be applied on types bool, text error message [Current Status]="Submitted",[Finance Disposition]="",[(Text) PipeLine Value Requested]="<$500,000","Red") I'm getting expecting text, but found bool error message
Formula Query (Size) - Question
I have created the following Number Formula query in Table A: My goal is to calculate the total records in 5 non-related Tables. The [Table Alais] field represents the Table Alais for the 5 different tables -- and the [AlaisTableFieldID] is my query field. This formula is not working.....any advice is appreciated. Size(GetRecords("{"&[AlaisTableFieldID]&".EX.'"&[Record ID#]&"'}","&[Table Alais]&"))
Sum Overlapping Time/Remove Timeframe Gaps
Hi all, is there a way to do this formula queries? I have a table of people who have child residence & employment records, with start & end dates for each, as well as a numeric field that summarizes each timeframe in months. I need to query for all child residence & employment records related to each person, find any potential overlapping timeframes between the 2 tables based on start & end dates, and sum the total # of months from the Duration (months) field from applicable records. Essentially I need a final number in months of time each person provided to us across 2 tables, so any overlapping time would basically get deduped out. I also need it to be smart enough to recognize some of these timeframes are not contiguous and contain gaps, so we might not have any date for particular person from 2010-2015, but we do from 2005-10 and 15-18, so any gaps would need to be excluded from the final count.
