Simple Recursive Formula

Question

Hello!

Please see diagram attached. This is a setup where a recipe is comprised of recipe items (ingredients) and sub-recipes (nested recipes). The structure has no set limit on how many levels of nesting there may be. I am trying to figure out how to calculate cost in a formula field so the users can see a native update of cost in real-time, rather than having to wait for a script to run after they've built out the entire recipe. As I'm sure you can guess, this may save them time in going down a recipe path that becomes too costly. So the formula as it is currently set up should work (I believe) logically...and right after I save the formula it displays the correct amount. However, once the page is reloaded or I direct to a different page, it mysteriously overstates the number. I have tried converting the numbers to text and then back again, but they are still displaying incorrectly unless I've just modified the formula. Please help! I'm really hoping QuickBase has a way to make this work natively. Maybe there's a workaround you know of for simple recursion like this? Do you think a 'snapshot' of the lookup might work? Or should I give up on native functionality and use IOL somehow?

Thank you for your time & thoughts!

anomDiebolt · Answer

>Or should I give up on native functionality and use IOL somehow?

QuickBase cannot perform a recursive query so you have to use script.

By sheer coincidence I have been developing a new technique to perform recursive queries using a feature in JavaScript called "asynchronous iterators". Actually, the technique I am developing can perform any sequence of asynchronous operations such as: (1) iterate through all applications in an account; (2) iterate through all users with access; (3) iterate through all tables in an application; or (4) recursively query a table (your case).

The demo that I am developing consists of a hypothetical organization chart of the characters from Our Gang:

I am still playing with the code but you will be able to iterate through all records using simple code such as the following:

async function example(dbid, ridRoot, clist, relatedFid) {
  for await (const record of asyncGet(dbid, ridRoot, clist, relatedFid)) {
    console.log(JSON.stringify(record), null, "  ");
  }
}

The functions you want to perform on your recopies (total ingredients) is similar to processing each individual in an organization chart (eg total salary or hours) or an indented bill of materials (eg total weight or cost).

Depending on my schedule this week I may post the demo. But in any case you can contact me off-world if you would like to pursue a solution:

https://getsatisfaction.com/people/dandiebolt

Sign up

Login to the community