Is there a way to create a recursive query in QuickBase to return all items from a table with relationships that may be several layers deep?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I know there isn't a way to to do recursion natively in Quickbase. 

I have a bunch of recipes that nest 5 levels deep. I want to create a query to flatten the structure. Is there a dirty way to do this with API_DoQuery? We only have 500 API calls a day, so this can't get too dirty.

The problem is I'm not a web developer, so I'm not sure how to pull the database out into JS or whatever and do the recursivity with those languages. We can't afford to spend thousands of dollars hiring out a developer to do this, so I'm trying the best I can.

If there isn't a way to do this using API_DoQuery, does anyone know of some tutorials that might be relevant to how to fully export a table out of Quickbase and do something like what I'm describing? Or maybe there is an add-on that does something like this? 

I have to imagine this is a pretty common desire from people using QuickBase.

Thanks in advance! 
Photo of Kate Theriault

Kate Theriault

  • 362 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
A table can indeed be related to itself, so maybe there is a native solution. Can you give an example of what you are trying to do?
Photo of Kate Theriault

Kate Theriault

  • 362 Points 250 badge 2x thumb
Sure. Here is an example recipe (although this only nests 3 levels deep). 

Top recipe Bolognese consists of: 
 1 unit of Subrecipe: Sauce 
    Ingredient: Tomatoes 8 oz 
    2 units of Subrecipe: Bolognese spice blend
      Ingredient: Garlic Powder 0.01 oz
      Ingredient: Thyme 0.01 oz
  Ingredient: Pasta 8 oz
  Ingredient: Salt 0.02 oz

I currently have this functionality in my application. I have two tables "Recipes" and "Subrecipes". I have two one to many relationships from Recipes to Subrecipes. In one of those relationships, I track Master Recipes and the other is used to track Sub Recipes. Every recipe can also have ingredients at any level (linked in a many to many relationship from Recipes to Ingredients). 

What I would like is to get a report of my top recipe Bolognese with all of the subrecipes and ingredients listed out. Unfortunately subrecipes and ingredients are not the same entity, but it would be fine if they are reported in two different tables in the report. It would be even better if we could somehow merge them together (if I had everything I've ever dreamed of, haha). 

Thank you,
Kate
I have attached an image of the proposed relationship diagram.  I'm not sure how readable it will be when it uploads.  Contact me via the info in my website QuickBaseCoach.com and I will transfer a copy of the app to you.

The Report for a Recipe would be a summary report of the Ingredient Quantity Tables with 4 extra levels of grouping which would be by the 4  levels of Recipe Assignment Tables.

I'm pretty sure that this approach will work. 

In sleeping on this, it is actually a fascinating problem to tackle natively.  I think that what I have so far is a partial solution.

I think that the other part of the solution will be the concept of a User Focus to set a "Focus" on a Recipe and then "light up" the relationships below to be able to print a summary report of ingredients groups by the 4 levels of the Recipe.

It will take more thinking to completion this concept.
Photo of Kate Theriault

Kate Theriault

  • 362 Points 250 badge 2x thumb
Haha, I'm honored that my problem is so mentally stimulating.

It's actually much more complicated than I've laid out here. This system ties into inventory and cost, so there is currently a nested cost associated with each recipe. This has been implemented with multiple lookup and summary fields between Recipes and Subrecipes. (And this can go to infinite depth). 

There is a scaling functionality needed as well that I haven't tackled yet. A separate table keeps track of all the recipes I'm making on a certain day and in what quantity. So if I'm making 30 bologneses on Tuesday, I would need the kitchen to make 8 oz * 30 of pasta. 

That's part of why I'm trying to print out the subrecipes and ingredients for the kitchen, so they know how much of what ingredients to make on the days we are making food. 

I'd be happy to go over this in more detail if you're having fun thinking about the solution, but it would probably require the entire ERD and explaining our entire business, hah. I appreciate you thinking about it though because I've been taxing my brain trying to figure out how to get a flattened list in Quickbase (while still maintaining the other database functionality we need and not duplicating data entry anywhere). 
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,094 Points 20k badge 2x thumb
What you are describing is a data structure that is hierarchical or a tree. You are not going to get a workable solution without using script. The problem is very similar to a modeling a hierarchical bill of materials where each part is composed of sub-assemblies and other parts. Also, you need to "scale up" the "recipe" is very similar to walking the tree of a hierarchical bill of materials totaling cost, weight or man-hours.

However, I did like the carrot icon Mark used in his ER diagram.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
Reply in two parts:

Part 1
> We only have 500 API calls a day, so this can't get too dirty.

You can avoid calling API_DoQuery altogether by making your queries through the native action ?a=q and requesting CSV output instead of HTML using &opts=csv.

Here is a query of the Formula Function Reference table manually applied through the console:

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=657




The script above makes use of a QuickBase function CSVToArray() which is located in in /js/_packed/common.packed.js so you don't have to load a CSV parsing script yourself.

Part 2
The problem can be solved with script using one table and a script that makes multiple queries. It would require specific knowledge of your fields and current tables.
Photo of Kate Theriault

Kate Theriault

  • 362 Points 250 badge 2x thumb
Thank you both, actually you helped me. I'm going to implement the reporting structure from Mark. I'm going to proof of concept it, then write a script to do the "user focus" that assigns all the subrecipes and ingredients to a top level recipe.

Then I'll use ExactForms or something to print out my data from my flattened report in a readable form. Thank you both!