InfoQube IM - Community

Submitted by supersql on 2010/07/14 16:38

I'm trying to make a field in IQ that simply counts the number of children under that item. And not just in the level directly underneath, but all nested levels underneath. I tried using the "Total" field that is already there, but that is for when you have a number in the columns, and it sums those numbers up. i want it to COUNT the number of items. Please let me know if you have suggestions. Thanks.

Comments

There are system fields called NbChildren and NbParents. NbChildren should do what you're after.

I'll update 4.01 in the manual, to add some text, to make it easier to search for something like this.

[quote=KeithB]

There are system fields called NbChildren and NbParents. NbChildren should do what you're after.

I'll update 4.01 in the manual, to add some text, to make it easier to search for something like this.

[/quote]

This would be good to build upon but it won't work as supersql wants a the total sum of all sub items, including all levels. And even with a sum (children) column equation, it's not that simple as it's not only a sum of all nbchildren of each item. : since the first subitems have no children, the sum would = 0 and this would propagate till the TLI.

Maybe with 1) a field based nbchildren (a Row equation) 2) + a conditional column equation that it will add up the nbchildren field only when nbchildren isn't null (and otherwise happily apply the row equation).

I haven't tried... Going to bed.

But I don't think that would work at the moment as at least one thing doesn't work well when both row and column equations are set for a single field.

Thanks Armando, I'll see if I can figure out how to do that.

Here's what you need to do :

SumNbChildren number field (you need to create it) :

HIerarchy equation :

Parents = 1+ Sum ((children))

Item field (or any-other field you'll modify or add something to) add this autoassign equation:

equation section : Auto assign : AM:SumNbChildren=1

You could just use A: SumNbChildren=1, but this would work only when you add content to an item the first time.

AM:... is only convenient if you want to select all your items and then effectuate a "Touch" (Edit menu -> Touch) to activate the equation.

Then change the AM: back to A: so that it's not activated everytime you modify the item field.

This should work. Just do a touch to all your selected items, + a recalc at the end of your hierarchy.

After the first time everything should update automatically.

Note that you need to substract 1 to the last parent (TLI) as the hierarchy equation adds 1 to the total.

Armando's proposal does work, it however includes an auto-assign on the Item field, which will have a performance impact and will affect all items in the IQBase. I don't think you want this sub-item count for each and every item in your database, this is likely specific to a grid or to an few items. Here is a similar solution without this drawback:

New Field: SumNbChildren: type: number, hierarchy equation: parent = 1+sum(children) ( same as Armando's)

New Field: DoSumNbChildren: type: yes/no, auto-assign: A:SumNbChildren=1

Then 2 possibilities:

Grid specific feature: Items added to a specific grid will be counted: In grid properties, add DoSumNbChildren to the list of Auto-assign fields
Item specific feature: Can be used for any items, anywhere: Set Inheritance for field doSumNbChildren

n.b. It is possible to do the Grid specific one without the use of the DoSumNbChildren field. Simply set the Default value for the SumNbField to 1 and add SumNbField to the grid auto-assign field

HTH

Yes, this is better ! As you said, it's better to avoid equations in relation to the item field : too much overhead.

OK, i'm trying to digest the methods written here. I'm trying to understand everything instead of just blindly copying everything in, so please bear with me.

There are some things I don't understand. I don't understand why there needs to be two new fields to do this. I don't understand the syntax of the auto assignment stuff (A, AM:, ?). I don't understand what "Touch" means.

I'm trying to do it using only one new field. i tried what Pierre wrote about doing it this way and I can't seem to get it to work. Here's what I've done so far:

--created a new field, sumNbChildren (number, default value=1, parents=1+sum((children)))

--added sumNbChildren to the auto-assign list in the grid properties

All the values are blank right now. Is it possible to do this using only one field and not touching the Item field? I only need it for one grid.

There is a bug in the current version (now fixed in the next one), so the single field method does not work. Also, it works for new items only, if you've already got items / subs, it won't work directly. Best is to use the dual field, with field inheritance. When ready, check the DoSumNbChildren field and all subs will be checked (through inheritance) and the sum will appear.

[quote=supersql]

[/quote]

Here's an excerpt of the manual concerning A:, M:, etc. :

[quote=Field Management Dialog - Equations]

Auto-Assign Rules

Auto-Assign Rules are used to assign values to other fields when values in this field is changed. They are also called Push-Equations The syntax is Flags:Field = equation/value.
The flags indicate when the rule should be applied (they can be combined):

A: flag: Action will be triggered when a value is added (initially no value in the field)
M: flag: Action will be triggered when a value is modified (initially there is a value in the field)
E: flag: Action will be triggered when a value is deleted You can have as many auto-assign rules as you wish separated by vertical bars, they'll be executed in the sequence that they are entered.

[/quote]

Touch just activates the equations related to a field.

See : -- link to nonexistent node ID 90 -- and now : 2. Edit Menu

As for the other aspects, do as Pierre suggested and if you look at my screenshots, they should help you as how the equations should be entered, especially the 1+sum one.