Help with GetFieldValue(GetRecords....to lookup value in another table
I want to pull a value from one table based on a field in another table. I have a summary table with raw material prices, lets call this table B. each entry is for a different month/year, so this field is text. It is called Date and the field id is 10 on my raw materials table. The other fields on this table are numeric and have various RM costs. For this example I'll use: RM 1, field id 25. I have another table with customer product information, lets call this table A. I use this table to calculate product margins at a certain time. This has a field called Date as well and it is a multi select text field, id 120. There is another field on this table called RM A Cost, this is where I am entering the formula. When field 16 [Date] on table B = field 120 [Date] on table A, return the value from field 25 [RM1] Here is the formula I used: GetFieldValues(GetRecords("{16.EX.'"&[Date]&"'}","tableB"),25) It is not returning a value. It is underlined in red and says expecting value but found text list. Is this because the Date field on table A is multi text? Or because I am using both text and numeric fields? I am very new to programming in quickbase, so please explain in very simple terms. Thank you ------------------------------ Angela Anderson ------------------------------
Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table
Hi QB Community, I'm excited about the new Formula Query and am trying to use it to find the minimum and max fields in a seperate table. I start with this: GetFieldValues(GetRecords("{249.EX.'"& [StateID] &"'}", "DBID"), 212) In the query, I'm returning a text list that shows all the 'prices' (fid212), where the 'stateID' (fid249) matches the 'StateID' on the record. This part works. Now I'm trying to figure out for to derive the min and/or max from the resulting text list. I've used SearchAndReplace to modify the text list in a few different ways to that I can pass it into the Max function, but no luck yet. Do you konw how to do this?? Thanks! ------------------------------ Jake ------------------------------Formula Query expecting number but found text
I'm new to writing formula queries and keep running into type mismatches. I'm figuring bonuses for our technicians, and have the bonus rate stored on another table. var text myquery= "{8.EX.'"&[User to text]&"'}"; Getfieldvalues(getrecord($myquery,[_DBID_TECH_Info]),7) The formula above says it's expecting a number but found text. Do I need a different function to look up a text field? ------------------------------ Anne Martin ------------------------------
Removing duplicates from a textlist
Hello! Was hoping I can get help with this problem. I'm using a query formula to get a list of emails from another unrelated table and it works just fine, however, it gives me duplicates which i'd like to get rid of somehow. Has anyone dealt with this issue before? example textlist: brucewayne@email.com ; theflash@email.com ; brucewayne@email. com ; brucewayne@email. com ; theflash@email.com; theflash@email.com ; superman@email.com desired outcome: brucewayne@email.com ; theflash@email.com ; superman@email.com I can either make a new field to get the desired outcome or perhaps there's a way for me to stop duplicates on the query formula?
Formula Query Sorting
Does slist and option=sortorder work within formula queries? In my formula query my items return the correct values but the sort does not work. Also does num-# work with the amount of records you want returned. var text Query ="{27.EX." & [Order Type] & "} &slist=3&options=sortorder-D"; ------------------------------ Tyrone Grey ------------------------------
Formula queries and Quickbase performance
Background When we took on the challenge of boosting our formula language, we knew we had to juggle three different and competing goals: Make things simple for builders trying to solve difficult business problems. Ensure end users have a delightful experience consuming what their builders create. Protect the platform – as we are a SaaS (Software-as-a-Service), we need to balance the traffic on the highway to ensure everyone has a great experience. To recap 2021, Quickbase formulas were taken to the next level with two major features: Formula queries: simplifying and expanding the class of problems Quickbase can solve. Summary report formulas: allowing you to perform advanced calculations on aggregate data. These are investments we made in our core engine, a special part of Quickbase that offers unparalleled flexibility and speed. As you know, all platforms have limits. Some more than others. We usually try and favor allowing creativity, with modest limits compared to other platforms. The balancing act there is build-time vs runtime. Platforms can take educated guesses at what end users are going to do, and then they have to choose between being too restrictive or too lax. We also have a responsibility to help and educate builders to create the best possible applications they can with good design choices. The challenges and opportunities are endless, especially when Quickbase applications are growing faster than ever, with even greater levels of complexity. Fun fact: One customer's application hit 650 tables this year, in production (don't try this at home). Our Limits In our case, one of our runtime limits is a prediction engine on reports and queries. Rather than letting an operation run on for too long, we sample a subset of the records to determine how long it will take and extrapolate that out over the size of the report to determine if it needs to time-out. That is when you see the "Uh-Oh!" error, shown in the following image. The important thing here is that in 100% of cases, customers are able to create a report serving their needs by optimizing. So, a little oil change and tire rotation helps builders create better applications, that their end users enjoy more, while protecting the platform for all users. NOTE: Let's take a minute to look under the hood, to explore the performance implications of the error message above: Even performing this calculation takes time. Meaning, this check and balance costs us a fraction of a millisecond on every report. That is a worthwhile trade-off, and one all software platforms take. But there are diminishing returns. The more toll booths and weigh stations added to the highway slow down the flow of traffic. Think about how Quickbase permissions allow reports to calculate different results for different people – all in real-time. What if I gave you the option of never having your end users see the "Uh-Oh" error shown above, but the trade-off was that we checked it for safety during report creation? In order to meet that goal, we would need to run your report for someone in every role in the application when you click "save". Now the millions of report changes every month have overhead which they did not have before. That trade-off isn't worth it, especially since we don't know how often that report will even get used. When creating formula queries, we needed to strike the right balance of allowing creativity while also continuing to have safety nets on the platform. Build-time protections Let's start with a relatively simple formula query: SumValues(GetRecords("{6.EX.'In Progress'}"), 4). When saving this formula, we don't know if it will be used on a report with 1 record, 1 million records, or used for permissions (which is the most expensive operation possible performance-wise). We have to take a guess. On one hand, we could assume the absolute worst. That would be seeing how long it takes to save the formula for one record and then multiplying it by the number of records on the table and seeing if it exceeds a threshold. This is the most restrictive, because it isn't likely that all operations leveraging the formula query will require all records. This would offer the most protection, but stifle innovation. On the other hand, we could just multiply it by some low value like 10, which would let almost every formula save (preventing only the absolute most erroneous from saving) but then that could be problematic at runtime. And this is a constant balancing act. Think of this like tuning a car's engine. Some cars optimize for acceleration, and others optimize for torque. It isn't right or wrong – just choices that need to be made. We have settled somewhere in the middle, that is dynamic to each table. And as we assess performance and the data we have, we will continue to refine the algorithms further. One additional nuance is that it is conceivable a formula gets through the builder checks on day 1, but something changes on day 2. Maybe a million records were imported, or an underlying field got more complex. In that case, you might see us stop the formula from saving on day 2. Runtime protections We can't count on protections just at build-time. What if the formula is saved when there are no records in the application because it is still being built? Or what if a certain role has very complex permissions? Build-time protections will pass, but it could be dangerous. When running a complex report, we would have allowed a non-optimized formula through the front door that may be extremely slow or introduce stability issues. The game changes. And because we run our own purpose-built in-memory database, we are actually considering these things down to the physical blocks of memory on the servers. This is exponentially more difficult when you factor in that a formula can be used inside of a permission, or inside of a summary field – it is infinite. Using any expensive formula to sort, for example, can cause us to do millions of additional calculations that aren't perceivable to the builder or user. This is more likely with the introduction of formula queries. And what if the formula query is inside of a field with a dependency diagram like the one shown below (it isn't even fully expanded!)? That's why a table with 5,000 records may run slower than one with 10,000,000 records – the underlying complexity of the "questions" you are asking Quickbase. And so to account for this, we added some logic special for formulas to that prediction engine I mentioned at the beginning. What you can do Enterprise software needs maintenance. Quickbase applications represent a no-code world where the amount of maintenance is much lower than traditional software, and even low-code. Within the context of Quickbase, the oil changes and tire rotations include things like optimizing reports and tuning searchable fields. Dependency Diagram In all of your complex applications, become best friends with the Dependency Diagram! It can be accessed from field usage, on any derived field (formulas, lookups and summaries). It is one of the "stickiest" builder features in Quickbase. Meaning those of you who use it, come back and use it often. It helps you visualize how your fields interact with one another and can easily uncover hidden complexity. This is a great place to start during application maintenance or troubleshooting. Performance Analyzer Next, ensure the Performance Analyzer is enabled in your application properties. This is a valuable tool for you to understand how your application performs in all ways, not just formula queries. If you've turned on "Analyze Performance" in your session (by clicking your name at the top right), saving a formula query that gets stopped by our protections will offer an additional option to quickly get more details: This is going to give you more specifics around what is being caught during the save operation. When running a report, we can even see that the sort time is being consumed by our formula query field. Under the hood: sorting by ID is listed here, even though it wasn't explicitly defined, because we need to tie-break record sorting. Searchable Fields You should regularly optimize and streamline searchable fields in all tables. We recommend selecting 10-15 fields per table which are most important for your users to search on, and mark the rest of the fields not-searchable. Pay special attention to complex derived fields, like formulas containing a query. This is one of the most important things you can do to keep your app running smoothly. Tech Support Our Tech Support team is also available to help answer questions. They also have access to more tools that can point you in the right direction. Customer Success and Solution Architecture We have a team of Customer Success Managers and Solution Architects that not only can help reactively, but on a proactive basis as well. We can help analyze your application in extreme depth, understand your usage patterns, recommend, and help implement solutions that scale. Our 20+ years of expertise combined with the tools in our toolbox can be leveraged to its fullest potential, helping you scale your Quickbase solutions to thousands of users enterprise-wide. We will update resources as we gather examples to help illustrate opportunities to improve performance in formula queries. In the meantime, please reach out to your account team to discuss additional ways we can work together. ------------------------------ Harrison Hersch ------------------------------Formula Query - How to 'Count' return values
Hi, Two questions, which are probably a very new-user questions, but I'm stumped. I'm trying to build a summary table that uses formula queries to refer to data in other tables. Question 1: I want to count the number replies in a given field - the value returned is irrelevant, I just want to count the number of non-null values in this field: var text QUERY = "{7.OAF.'" & [Month] & "'} AND {7.OBF.'" & LastDayOfMonth([Month]) & "'}"; Count(GetRecords($QUERY,"bru6bq226"),72) There should be around be a range month-to-month from 50 to 150.. with any text in the field, but the system just gives me '2' for each month. Why? Which leads to my second question: I thought perhaps maybe it was counting the number options available in that field, in this case, the response 2 would be the correct; so I tried to use it in another field where I need to actually count the number of communities represented at our events (between 20 and 40 each month), and it also only returned the value '2' - so that's clearly not what it is counting. How do I count the # of unique responses in a field? Thank you so much for any guidance you all can offer! I'm spinning my wheels and not quite understanding how to do this. Take care, Jen ------------------------------ Jennifer Juhasz ------------------------------Query for unique count?
As you can tell from my recent posts, I'm going down the formula query rabbit hole. I would like a suggestion for getting a unique count for customers who subscribe to a specific service. I have the total count of sites calculated in this post: Formula Query - Size ? I know I will need the Size function and can get the total sites. I'm just not sure how to only return the unique customers. ------------------------------ Paul Peterson ------------------------------
