Skip to main content

Avoiding circular reference error in hierarchical table

  • April 15, 2019
  • 3 replies
  • 29 views
D
Forum|alt.badge.img

I am trying to create a table that can represent an arbitrary depth hierarchy of objects, with a field that in each entry that rolls up corresponding values from its children. Here is a simplified version of what I want:

Each entry has a “type” field that is a selection of “leaf” or “parent”, a numeric “enteredSize” field (used only for leaf nodes), a “children” field with links to other entries, a rollupSize field that is the sum of the size fields of the linked children, and a “size” field. I want the size to be a formula: IF(type=leaf, enteredSize, rollupSize).

This is disallowed because of a circular reference. I understand that I could create a circular reference with this, but my data is a strictly a tree. Is there any way to support this? Basically, I want to check circularity between records, not between fields.

    3 replies

    D
    Forum|alt.badge.img+14
    • David_Skinner
    • Inspiring
    • 160 replies
    • April 16, 2019

    Yeah that won’t work. There might be a better way to structure your Base, but I can’t say without knowing exactly what you are doing.

    Is there a limited depth to the hierarchy of your objects? If so, then you could have several roll up fields, 1 for each level.

    enteredSize
    rollupSize1 - this rolls up enteredSize
    rollupSize2 - this rolls up rollupSize1
    rollupSize3 - this rolls up rollupSize2
    etc.

    Size would then simply be enteredSize + rollupSize1 + rollupSize2 etc.

    D
    Forum|alt.badge.img
    • David_Hathaway
    • Author
    • New Participant
    • 1 reply
    • April 16, 2019

    Yeah that won’t work. There might be a better way to structure your Base, but I can’t say without knowing exactly what you are doing.

    Is there a limited depth to the hierarchy of your objects? If so, then you could have several roll up fields, 1 for each level.

    enteredSize
    rollupSize1 - this rolls up enteredSize
    rollupSize2 - this rolls up rollupSize1
    rollupSize3 - this rolls up rollupSize2
    etc.

    Size would then simply be enteredSize + rollupSize1 + rollupSize2 etc.


    Thanks, I’d also thought of that, but hoped for a cleaner alternative.

    My leaf level roll up are actually different structures, so I really need polymorphic links in my rollup. To achieve this in my implementation I’ve introduces a sort of “inherited data” table that has links to either a leaf OR a rollup, uses a lookup to import the needed values, and a formula to select the value associated with the non-empty link for access in an upper level rollup.

    In my case the roll up data is relatively static, with relatively few roll ups at levels 2 and higher.values, so I am going to change the inherited data table value from a rollup into a numeric field I will manually copy from the child.

    This is all somewhat of a prototype, and this limitation may drive me to a different platform for a production system.

    Y
    Forum|alt.badge.img+2
    • Yachal_Upson1
    • New Participant
    • 2 replies
    • June 26, 2022

    The circular reference thing remains a total product-killer for me.
    Not asking much, just some basic BOM (Bill of Materials) functionality - same as you’re seeking by the sound of it. Such a shame


    Terms & ConditionsAccessibility statement