Running totals for charts
Hi all Want to share my solution on how to create running totals for charts. QB does not support running totals for charts, only summary reports. QB Junkie has a really good solution to create running totals using the query formula but as stated in her video it only works with small data sets and it is really memory intensive. QB has posted a solution to use the report API function to build a new running totals table from a summary report but I found this to be even slower and it does not give the user live updates. I use own table relationships that allows me to chart large datasets quickly and live. Below is an example of this application but I use this for many other data sets that do not use dates so do not get stuck on the dates table step. Create a dates table. I made the key a text field so that i can create the reference field. Record ID is also an option but a bit harder. My key was "<date>-<project no>" Create an own table relationship I use pipeline's Make Request action to create and maintain this table with its key and related field value. This is a Jinja code on Exchange I borrowed so let me know if this is of interest to anyone. It super-fast but again not a requirement. Summarise the values I want into the dates table Create a blank formula field and this will be my running total Summarise the blank formula field i created in step 6 above. I use the own table relationship for this. Go back to the blank formula field in step 6 and write a formula that adds the summarised value in step 5 to the summarised value in step 7. BUT I found this needs to be via an if statement and can't be a simple addition as it fails randomly. Maybe a bug in QB but I found my way around this and its now super reliable. My if looks like this If( Nz([Field in step 5]) + Nz([Field in step 7]) <> 0, Nz([Field in step 5]) + Nz([Field in step 7]), Nz([Field in step 5]) <> 0, Nz([Field in step 5]), Nz([Field in step 7]) <> 0, Nz([Field in step 7])) Thats it. Fast reliable running totals to draw charts! See screenshot below
Making a checkbox field editable in a report?
Hi! I was wondering if someone could help with a possible formula question? I have in an app an employee master list. In that list the previous designer has a checkbox field called "Active" - If the employee is active it is check if not unchecked etc. My question is the default report that lists all of the employees shows a column that is the active field but I have to go into each individual employee record to uncheck active. Is there a way to be able to go down the list and uncheck all who are inactive right from that report? Thank you in advance!
Secure link to a report
Secure links to reports are mentioned in the Quickbase documentation and multiple places on the Quickbase website, but I can't find a single example. I understand how secure links can be made for forms and records and know there are examples of this in the Exchange. But there is no example of how to create secure links to reports. I figure one way to do it is create a key generator field, then an ask the user report, and filter to records where the result equals the key value. But just wondering if anyone has any other, better ideas?
Report help, take 2
Figured out my first one (well, kind of). I'm building a summary report summarizing several check-box fields. Two questions: Is there a better way to do this where I want summaries of a LOT of check-box fields? It looks like the cap of line items to summarize is 24 Do I have to "Group by Rows"? I basically just want a total summary, but don't want to group it. Speaking in Excel terms, what this might look like in an ideal world is: Rows that are limitless, identifying each field to summarize Columns are only two -- 1) The title of the field being summarized, and 2) distinct count Here is what I'm looking at, and I ended up just creating a summary field generally to just have one summary row.... but really, I just want the bottom line.Report help
Hello! I'm diving into someone else's app, trying to wrap my head around their reporting needs. The previous admin created several "checkbox" fields that are in reality, types of answers to a master field. I need to tally each checkbox and ideally, I'd love to get this all on one report. I theorize the reason they did individual checkboxes vs. one multi-select field is because, in my own experience with multi-select, a report of such won't cleanly count how many times each item was selected, but will report out instances of the specific combination of what was selected. In my example, I'm tallying types of support received, where there are 10 sub-types as check-boxes, so ideally this report shows the exact count of each type of service counted. Please and thank you!
YTD Reporting-Best Practices
Hello! I am working on a monthly and yearly sales report and I have run into a hiccup. Scenario: I have a record that is a booking ("sale") that is made in Jan 2025 and then in Mar 2025 the booking is cancelled. The way I have it now is that when the booking is made, the status field changes from quote to booking and then when cancelled, the same field changes to cancelled. In my YTD report, this is causing the booking made in Jan 2025 to not show up due to the cancel in Mar 2025. I understand it is a wash, but I still need the booking to show up in Jan 2025 and then the cancel show in Mar 2025. So, this is what I am thinking for a solution: Create a date field unrelated to the status field that will populate when a cancel occurs and have the report pull of the booking date/status and also from the cancel date report and that should then show both transactions OR create a separate table that will hold the numbers at month end for each month and pull the YTD report from that table? I would just like any input/advice from someone more knowledgeable than myself. I am hoping I am thinking about this in the right way. Thank you, Kim
Creating a pie chart in a grandparent table
I’m in PR and we use QB for managing our media requests. If a reporter reaches out with an inquiry we log it in a media requests table. Media requests has a parent table of reporters (reporters can have multiple requests). Each reporter has a media outlet (think CNN or ABC News) parent (a media outlet can have many reporters. One of the things we track is the coverage of the media requests generates and score it with a multiple choice field on the media request entry (positive, negative, or neutral). So I have a pie chart that show the breakdown of the coverage with those options. I can pull that report into the reporter table on place it on the reporter form and it filters it down showing the score for that reporter across all their requests. What I want to do is pull that data into media outlets so I can see the score for that media outlet across all their reporters and the reporters requests. I want a similar pie chart on the media outlet form. What’s the best way to go about this?
Up-vote Full-width Formula URL Buttons for Reports
In reports, Formula URL buttons are truncated. You can use a Rich Text button and see the full button by default. I use Rich Text buttons now for reports where it is critical to see the whole button. Please up-vote my feedback on this: https://feedback.quickbase.com/app/#/case/474496?cpid=b2f09471-062b-455b-afda-96efb605ab38§ion=requests
