Forum Discussion

StephanieDevin1's avatar
StephanieDevin1
Qrew Trainee
7 years ago

Simple Recursive Formula

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!

Ask about Quickbase functionality
Formulas and functions
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    >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
    • StephanieDevin1's avatar
      StephanieDevin1
      Qrew Trainee
      7 years ago
      Thank you Dan! How serendipitous. That is super cool--I look forward to checking it out!_
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      7 years ago
      This is a pretty advanced thing but asynchronous iterators make is extremely simple to iterate over promises. If you want to automate QuickBase this is the technology to learn.

      While you wait for my post watch this entertaining video from the Fun Fun Function guy (Mattias P Johansson):


  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Stephanie,
    I did get that concept working once for an app with Bill of materials for parts assemblies where an assembly could use a subassembly from the same table,  and there was no limit as I recall to the number of loops.  The trick is to somehow not have it be seen by Quick Base as a circular reference. 

    Unlike excel, QuickBase does not tell you that is has a circular reference, but it does behave weirdly.

    When I get a chance I will see if i can locate that app to remind myself how I solved that obstacle.
    • StephanieDevin1's avatar
      StephanieDevin1
      Qrew Trainee
      7 years ago
      Thank you! That's exactly the type of workaround I've been trying to brainstorm. If you do end up finding it again, please enlighten me. Either way, I appreciate the time very much :)
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      Stephanie, I did find that app in my Library.  I recall at the time I was interested in the concept as a theoretical puzzle.

      I will paste below the dashboard comments on my app.  Contact me off line via the gmail, email address on my website, QuickBaseCoach.com
      and we can discuss further.

      I also see now that I was so pleased with myself I posted it to the Exchange.  It is called Assembly Costing.

      This app will allow you to do Assembly Costing.  The concepts starts with a basic parts list.  You can then construct an "Assembly" ie a Bill of Materials (BOM) and typically add labor to get an "Assembly". each Assembly has a Parts cost and a Labor cost and then of course a total cost.

      But the magic with this app is then you can take that Assembly and use it in another BOM which would typically have have more parts  and more labor and more other Assembles to get another higher level Assembly.  But the delightful aspect of this app is that the Assembly table can infinitely be a child of itself, so there is not theoretical limit to how "deep" you can go in terms of nested Assemblies.  The app was originally written for a client who has Assemblies which go 5 level deep, built there is nothing in the design of the relationships which limits it to that 5 levels.  

       Each Assembly shows what its child BOM is  and also which are it's parents, ie where it itself is used in a higher level Assembly's BOM.

       In order to avoid a circular relationship, the app uses a webhooks.  if you are not familar with Webhooks, then you will need to contact me or someone for assistance.  But basically each time the record is edited, the webhook kicks in and copies the calculated value of all the Assembly calculated costs for Labor and Parts costs into a mirror field. (ie not for that 1 record being edited, but for ALL Assemblies in the table)  Then it is that mirror field which is used to roll up to the next level.  That way Quick Base does not consider it to be a circular relationship.  The reason it does that is because when say a part cost is changed or the labor rate is changed all assemblies could potentially be affected.  So the copy process in fact runs 5 times "just to be sure".  (then there is an exception report looking for any additional copying to be done with a button to kick that off.

       The StockRoom pull table as another small feature of the app just to be able to "pull" a fresh cost into a table (or it could be fed into an order line), presumably to charge a client with the current cost of that Assembly.

       

      Ideas for Development

      Ability to copy an Assembly BOM

      Identify All Sub Assemblies for an Assembly (ie all the children and grand children down the chain)

      Who will own the web hook? Note that the app will not work unless the webhook is set up.

      Subscribe to the Mismatches safety net

    • StephanieDevin1's avatar
      StephanieDevin1
      Qrew Trainee
      7 years ago
      My QB hero!!! Although the webhooks do not come with the exchange application, the description was enough to explain the solution. This worked like a charm and was a very clever of you! I ended up using 10 automations since we have that luxury now :)

      Can't thank you enough rock star.