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?

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?

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.

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). 

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.

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!