ContributionsMost RecentMost LikesSolutionsPipeline Query Trigger On Change From Specific Value I'm trying to set up a pipeline that will trigger when a Status field changes from "Inactive" to any other value. All I've been able to find is this thread from several years ago, that I can't respond to at this point: Pipelines - how to look at original value and new value of field? I have attempted two versions, {a.$prev_Status}='Inactive' and {a.$prev.Status}='Inactive'. Neither triggered when I updated a record. I have included a screenshot of my trigger setup, any idea what I'm doing wrong or another way to get the result I'm after? ------------------------------ Amy Christensen ------------------------------ Re: Create retrospective summary report of record counts over time Oh my goodness, the quotes were the problem! I actually started with a formula copied and pasted from the Formula queries Help page specifically so I didn't have to worry about getting all the punctuation right. It may need to be corrected: Your other solution for the blank end date is excellent, that will be helpful for many other situations as well. Thank you much! ------------------------------ Amy Christensen ------------------------------ Re: Create retrospective summary report of record counts over time I had to go back a few iterations to reconstruct that attempt, but this is what I have: Size( GetRecords("{99.OAF.'"&[Date]&"'}AND{77.OBF.'"&[Date]&"'}", "[_DBID_TASKS]") ) I confirmed that all three fields (Date in this table, and fields 77 and 99 in the table being queried) are the same type. 77 is the assignment start date and 99 is the end date (which remains blank until the assignment is completed). The formula is accepted, but returns zero records. ------------------------------ Amy Christensen ------------------------------ Create retrospective summary report of record counts over time Hello all, I'm having some trouble conceptualizing the best way to achieve a report that would provide a retrospective look over time at the number of active assignments on any given date, to allow us to track changes in various related metrics over time. Basically I have a table of technician assignments where each assignment has a start date and an end date, and the same for vehicle assignments (and there are probably other things we may want to apply this concept to in the future). What I would like to end up with is a report that can query and display the number of active assignments as of, say, each Friday this year. To do this, I need some way to test whether each Friday date falls after the start date and before the end date of each assignment, then count the assignments where this is true. I've tried coming at this via a summary report, considered a pipeline, and attempted a record query formula field, but these each seem to fit well for one piece of the equation then get stuck on another. I know I could just set up a pipeline to record the counts on a daily basis, but it's not unusual that adjustments are made after the fact so I'd prefer something more dynamic. The idea I hit on was to create a "Daily Records" table with a record for each day of the year and a formula field to count the assignment records that encompass the date assigned to each record. The issue that I'm hitting on is that I can't find a way to do a summary field that summarizes records that fall between the values in two fields (and there may not even be an end date input on many records, just to add a little more complexity). I thought a record query formula field would do the trick, but I didn't have any luck with the size or count functions and I'm not sure how else I could go at it. Any ideas are welcome! I'm sure I'm not the first to want to do this, but I haven't found the magic search terms if the solution is already out there. ------------------------------ Amy Christensen ------------------------------ Re: Field value changes, record ID is constant - how to look up current field value? Thank you, this is precisely what I was looking for. I was getting tons of solutions for how to do a query when I just needed to go straight to a record. I just needed the right syntax. Much appreciated! ------------------------------ Amy Christensen ------------------------------ Re: Field value changes, record ID is constant - how to look up current field value? This is a great tip, and one I can see using going forward. You also made me realize that for unrelated reasons, shutting down access to add records on this table is something I absolutely needed to do regardless. It would have occurred to me eventually, but better sooner than later! ------------------------------ Amy Christensen ------------------------------ Field value changes, record ID is constant - how to look up current field value? I know I have to be making this too hard. I need to have a field pull the current numeric value in field 31 from record #1 of an unrelated table. There is no need for any other kind of relationship here, I just need the field in the table I'm working from to know what value is currently in that other specific, constant. table/record/field. I've tried every search term combination I can think of and I'm coming up empty. ------------------------------ Amy Christensen ------------------------------ Re: Pipeline formula to extract last 5 characters of text string This is the solution I tried first, but without the colon - which is probably why it only returned a single character! Mark's solution was more straightforward for this use case, but I'll keep this in my hip pocket for the future. Thanks! ------------------------------ Amy Christensen ------------------------------ Re: Pipeline formula to extract last 5 characters of text string Thank you, I had been searching and never found the RIGHT function. Easy enough! ------------------------------ Amy Christensen ------------------------------ Formula Query Execution took too long - List All tableWe have a table with a bit over 9,000 records and 184 fields. Table size is 4.8 MB, with 150 MB of attached files, and the statistics screen says it is less than 1% full. Recently, we have been getting the error "Formula Query Execution took too long" when attempting to load the List All report for this table. The table lists vehicle assignments, and we sometimes need to look back at assignment histories, so I would prefer not to delete any records. Why would we be getting this error, and is there anything I can do to prevent it? ------------------------------ Amy Christensen ------------------------------