InfoQube IM - Community

Submitted by william_wicker on 2010/10/25 11:28

I'm trying to set up a grid to work as an indented parts list / bill of materials. Here's a picture of where I'm at right now. Some things work fine. Others don't. Here's a picture:

I'm trying to figure out how to merge the row-level equation that sets Total Cost = Quantity Required * Cost Ea. with the column equation that sets Total Cost = sum (children)

Ideally, I would like to be able to enter a column equation for Cost Ea. that is the sum of the children's Total Cost columns. Then I can see the cost for a single sub-assembly as well as the total cost for the number of sub-assemblies required. If I do this, then Total Cost is always = Quantity Required * Cost Ea., and the Cost Ea. is either entered manually (for pieces) or calculated (for sub-assemblies).

Failing that, I'd like to at least be able to set the column equation for Total cost to be something like Total Cost = Quantity Required * sum(children)

Can this be done right now? I'm hoping there's some notation that lets me reference any column in a child item. (like maybe Cost Ea. = sum (children.TotalCost)?)

Comments

After a few false starts, I have a working solution. I wrote my own vbscript function SumColumn(column, children)

It took a while to find the vbscript editor (Under Tools->Visual Basic Editor)

Here's the code (which it seems MUST live in the 'User Code' tab of the editor:

Function SumCostTotal(CostTotal, x)
  dim d, i
  d = ubound(x)
  for i = 0 to d
    SumCostTotal = SumCostTotal + NZ(CostTotal(i))
  next
end function

The column formula is Parents = SumCostTotal(CostTotal, children)

I'm guessing I could rewrite the above to remove references to 'x' and 'children', but it works as is, so I'll move on to the next thing.

Hi William,

I may have missed what you were wanting - but as far as I can see from the screen shot InfoQube is doing what I would expect, that is, the parent items are automatically summing the children.

In this case there shouldn't be any relationship between the total and the "zero" in "Quantity" because it isn't multiplying anything in the row, it is summing the columns below that level of heading.

I'm a bit concerned because what you show in your screenshot is what I would expect - and if its not right then I haven't understood what InfoQube is doing.

regards, David

The original behavior is correct per the definition, it just wasn't what I wanted. Sorry for the confusion.

Hi William and welcome to the IQ forum !

This is REALLY neat, what you're trying to achieve. And it works ? It should. Recent changes (say 2 months ago) were made so that row equations not resulting in Null have priority over Column equations, which is exactly what you want:

Column equation is used to find the total price of a sub-assembly
Row equation is used to find the total price, based on the quantity

As a newcomer to IQ (or perhaps, you're not...) Congratulations !

p.s. The code can reside in any of the VB code tabs, and logically, it should be in the Database one

p.p.s. I'm curious... what are the "next things"

[quote=Pierre_Admin]

Recent changes (say 2 months ago) were made so that row equations not resulting in Null have priority over Column equations, which is exactly what you want:

[/quote]

Isn,t it the reverse (at least somewhat) ? Column equations not resulting in Null have priority over Row equations

[major] Problem with column/row equation -- please read-on :

1- Column equations should always have priority over row equations

2- Column equations shouldn't consider a "null" result as valid and supersede row equations results (hence emptying the field...). In these cases, Row equations should have priority and supersede the "null" result of the Column equations. (i.e. : if there are children with null values under a parent, the "null" result shouldn't supercede the row equation and erase it.)

Next is wrestling with conditional formatting.

I want to bold items that have children and italicize items that are "cloned". (NbChildren > 0 and NbParents > 1, respectively)

Setting a condition format with a criteria of "NbChildren>0" doesn't seem to work.

Also, BTW: stacking formats with a pipe '|' works as described. Adding some spaces around the pipe ' | ' confuses the parser.

Really must read documentation before posting. Adding NbChildren and NbParents to display in the grid makes everything work as desired.

Why is it important that fields referenced by conditional formats be visible? It would be nice if system fields, at least, could be used even while they were not shown.

Hi William,

I really fail to see how that solves the problem as there's nothing in there that circumvents column equations priority over row equations when nbchildren>0.

I havent tried it though. But if it works I'm not sure how... Weird.

Congrats!

I was going to suggest some other strategy involving another field, but this seems simpler, hence more elegant.

Well, I just tried your function, and it does exactly the same as this one :

Function Sum(x) ' Calculates the sum of the array x
dim d, i

d=ubound(x)
for i=0 to d
Sum=Sum+NZ(x(i))
next
end Function

I have reproduced your grid above (1 number field contains a multiplier, 2nd number field contains a value to be multiplied, 3rd field contains the product of the 2 others (Row equa. : Field 3= field 1 * field 2) and also a column equation which can be noted (in the properties pane) as :

SumCostTotal(CostTotal, children)

SumCostTotal(CostTotal

(as the children is implied)

In any case, I still don't see why would the result be different (it is not, at the moment) then just using

Sum

As David said, the multiplier multiples a column where there's nothing (null)... And the 3rd field hasn't got a conditional row calculation saying :

If field 2 is null then field 3 = field 1 * sum(field3, children)

Or are there other changes you didn't mention ?

I'd really like to know what you did as I wouldn't mind using it... Thanks !

I guess I wasn't quite clear about my solution. I'll explain more carefully:

As I mentioned above, I defined a vbscript function that sums a specific column (CostTotal). This function is then used as the CostEach column function

CostEach parents = SumCostTotal(CostTotal, children)

The CostTotal field has only a row equation: CostTotal = QuantityRequired * CostEach This equation for CostTotal works just as well for items with child values as those that don't.

The result is that I can either manually enter a value for CostEach for single parts, or let the column function provide the sum of the CostTotal of all child items as the CostEach of the assembly.

I tried using the standard sum function, but pointing it to a different field than "children". This didn't work. I have not tried to write a more generic SumOtherColumn (o) function. but I don't think it would work. If it did, so would Sum(some_other_column), which doesn't.

I think that since "children" is always implied as a function parameter (the last parameter?) the function signature has to be SumOtherColumn(other, children) and the the values of the children are ignored (for my purposes).

I hope this makes a little more sense.

Thanks for the explanations...

I didn't get that the column equation was in the CostEach field. Now that makes sense !

And it works very well !

Thanks again.