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 - 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 touserlist error
I'm sure this is a simple mistake, but I'm getting a "expecting user/userlist but found textlist" error. Isn't this the function of touserlist? Field 29 is a user field. //list all installers scheduled on this date //table ID: _DBID_SCHEDULE, FID 29 = installer, FID 6 = date var text myquery= "{6.EX.'"&[Date]&"'}"; touserlist( GetFieldValues(GetRecords($myquery,[_DBID_SCHEDULE]),29)) ------------------------------ Anne Martin ------------------------------
Slowly Changing Dimensions
Hello Community, I need to be able to have different versions of parameters in a dimension table, depending on a given time period. I wonder how to solve this in Quickbase. Simplified example: T_PRODUCT_PRICES ID, NAME, PRICE, VALID_FROM, VALID_TO 1, Milk, 2.00$, 2022-01-01, 2022-03-31 1, Milk, 2.50$, 2022-04-01, 2022-09-30 1, Milk, 3.00$, 2022-10-01, <<null>> T_ORDER PRODUCT_ID, PRICE 1, 2.50$ => How can I get the currently valid price and use it in a relationship with a facts table? Are there any best practices in Quickbase? Kind regards, Martin ------------------------------ Martin Suske ------------------------------Formula Query with filters
Good Afternoon, I am looking to come up with a formula query, in a formula numbers field, that can calculate a score but also react to filters when placed on a KPI (gauge) report or dashboard. I created a simple query that gives me the correct value but if I add field references and in this case they refer to each other, waiting for input from the user the calculations are off. Is it possible to add references to the query below that would allow a user to edit it on a report? I am basically looking to recreate a version of SUMPRODUCT from Excel. (I know that I could have done this differently but with the order of operations in QB this is what worked from an expected value standpoint) var number INVYES = Size(GetRecords("{31.EX.'Yes'}")); var number INVNO = Size(GetRecords("{31.EX.'No'}")); var number POLYYES = Size(GetRecords("{32.EX.'Yes'}")); var number POLYNO = Size(GetRecords("{32.EX.'No'}")); var number RORYES = Size(GetRecords("{82.EX.'Yes'}")); var number RORNO = Size(GetRecords("{82.EX.'No'}")); var number TotalYES = $INVYES+$POLYYES+$RORYES; var number TotalNO = $INVNO+$POLYNO+$RORNO; var number TotalMinus = $TotalYes-$TotalNo; var number TotalPlus = $TotalYes+$TotalNo; $totalminus/$totalplus ------------------------------ John Arniotis ------------------------------How to do year-to date calculation and show on a chart
Hi Everyone, I'm trying to create the below chart in QuickBase. I selected line & bar chart type so that count of "Approved" proposals per month can be shown on bar and area (Approved YTD field with light green in the below chart) can be shown in the form of line chart. As I don't see bar-area chart type option in QB I'm not sure how to calculate count of approved YTD (year to date value with light green in the above chart) values here? Furthermore, can I replace bar by stacked bar chart? There are 2 subcategories within "Approved" field so if possible would like to show that as well. I'm not sure if there is a way to combine different types of charts in 1 chart or we can select chart only from this list. Thank you in advance! ------------------------------ Mrunali Kadam ------------------------------
How to find a date from the previous record of a user in the same table
I have two tables, Coach (parent) and Meetings (child). The goal is to figure out if the next meeting is scheduled within 7 days of the last meeting the coach scheduled. So I need to somehow find the previous meeting date record for that coach and bring in that previous date to the current record so I can calculate the duration between the two. For instance, on the Meeting table, Coach 1 has a meeting scheduled on June 29 (record for that coach). The meeting date record before that date for that coach is June 22. How can I pull in the meeting date for the previous meeting record on the Meeting table for that coach (June 22) to the record for the June 29 meeting? All records need to have the previous scheduled meeting on them. Is there a way to do this? ------------------------------ Dawn Rene ------------------------------Formula Queries
Has anyone implemented a lot of formula queries (100+) in their app? I have only implemented about 20, and I love them, but am afraid of performance issues. I am wondering if anyone has used a lot - if so how many, are they on large tables, and are you having any performance issues? I could make 1000 to help me if I wasn't worried about performance, but I don't have any concept of what is 'a lot' of formula queries. Is 50 a lot? is 500 not very many? Are they expecting users to have 1000 formula queries or 20? ------------------------------ Mike Tamoush ------------------------------Type mismatch
This is to calculate a bonus for our technicians based on how many hours they bill. Some technicians get an additional bonus for hours worked on refrigeration equipment and some don't. I'm getting a type mismatch from my IF statement, so I added a tonumber but am still getting the same error. Any ideas? I've tested the parts of my formula separately and they work as far as I can tell, it's just the IF statement that's giving me grief. SumValues(GetRecords($myquery,[_DBID_QSALESHISTORY]),23)+[Hours Adjustment]+ If([Refrigeration Rate]="", (tonumber(SumValues(GetRecords($aquery,[_DBID_QSALESHISTORY]),23)+[Refrigeration Hours Adjustment]))) ------------------------------ Anne Martin ------------------------------
