Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Avoiding circular reference error in hierarchical table

1000 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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 3

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.

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