Forum Discussion

ThomasHarwood's avatar
ThomasHarwood
Qrew Member
3 months ago

Assistance Needed with Managing Recursion in Nested Container Inventory System

I am developing a nested container inventory management system within QuickBase and have encountered a challenge with calculating "Total Inventory" across nested containers due to recursion issues. I'm seeking ideas or solutions on how to effectively manage or circumvent this recursion to ensure the application performs as intended.

 

Application Overview:

Purpose: The app is designed to track the location, storage type, identity (by Part Number), description, count, and all transactions related to every inventoried item.

Container Hierarchy: Our inventory system uses a hierarchical structure where one container can contain other containers (e.g., a pallet can contain bins, which in turn can contain boxes).

Current Structure:

Tables:

CONTAINERS: Tracks each container, its contents, and its parent container (if any), establishing a parent-child hierarchy.

ITEMS: Holds information about different types of items.

TRANSACTIONS: Records additions, removals, and transfers of items in containers.

LOCATIONS: Differentiates storage locations (e.g., warehouse, vehicle).

The Challenge:

The recursive relationship within the CONTAINERS table for managing nested containers is creating a loop that prevents the app from loading forms correctly, particularly when trying to calculate the total inventory that spans across multiple container levels.

 

What I've Tried:

Depth Field: I introduced a "CONTAINER TYPE" field with predetermined depths (e.g., SMALL BOX = 1, ORGANIZER = 2, etc.) to assign a fixed depth based on the container type. This was intended to help manage the recursion depth.

 

Total Inventory Formula: Attempted to adjust the "TOTAL INVENTORY" formula to account for container depth but still facing issues with accurately calculating totals without causing performance issues or loops.

 

Seeking Solutions For:

Optimizing Recursion: How to efficiently calculate total inventory across nested containers without running into recursion limits or performance bottlenecks.

Depth Management: Best practices for utilizing the "CONTAINER TYPE" field to manage recursion depth or alternative strategies that could circumvent the need for deep recursion.

Formula Adjustments: Any suggestions on adjusting the "TOTAL INVENTORY" formula to work with the depth-based approach or other innovative solutions to this problem.

I appreciate any insights, examples, or guidance you could provide on tackling these challenges. Has anyone here worked on similar recursive setups within QuickBase and found effective strategies for managing them?

 

Thank you in advance for your help!



------------------------------
Thomas Harwood
------------------------------

2 Replies

  • I'm familiar with this issue. 

    My solution was to create a saved Table to table import to import the value of the Summary total into a scalar field where they were not equal.  Then summarize the scalar field. 

    I then made a formula URL button to run the saved import as many times as I knew the possible depth of the nesting.   The saved T2T imports run very quickly.   So for example, to run a report, I made a button to run the import 5 times and land the user in a report with correct totals. 

    The premise is the due to the recursive nature, the summary total was only correct at the lowest level. 



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • ThomasHarwood's avatar
      ThomasHarwood
      Qrew Member

      Thanks Mark! This seems like a simple solution. 



      ------------------------------
      Thomas Harwood
      ------------------------------