InfoQube IM - Community

Submitted by reesd on 2011/10/27 17:24

As discussed at http://www.sqlnotes.net/drupal5/index.php?q=node/2070#comment-9119, you can do something like the following to use more than one field of your children in your column equation.

Parents = mySum(IncludeField, children)

Function mySum(IncludeIt, x) ' Calculates the sum of the array x if IncludeIt is true dim d, i

d=ubound(x) for i=0 to d if IncludeIt(i)=true then mySum = mySum+NZ(x(i)) next

end Function

So the function is passed arrays of children's field values for IncludeField and whatever field the equation is defined in. Unfortunately this isn't what I need. What I would like to do is use the Parent's field in the equation. So I want something like this:

Parents = MyValueOrSum(ParentField, children)

Function MyValueOrSum(myField, x)
   Dim d, i   
   If Not (IsNull(myField)) Then
      MyValueOrSum= myField
      Exit Function
   End If
 
   MyValueOrSum = Sum(x)
End Function

Is this possible somehow?

Comments

Looking at your function, I'm not sure I understand what you want to do.

d and i don't do anything, and there's no loop. How can you add any children/column values ?

--------------------------------------------------------------------------------

Windows XP Home Edition, Service pack 3

Dell Vostro 1500, Ram:3gb, CPU: Intel Core2Duo T7500 2.2ghz

This, for example would calculate (for a given parent's "field 1") the sum of all all childrens "field 1" + "field 2" (another field) IF its value is not null :

(x being the field in which the function is operating)

Function MyValueOrSum(myField, x)
   Dim d, i
   d=ubound(x)
   for i=0 to d
     If Not (IsNull(myField(i))) Then
       MyValueOrSum= MyValueOrSum+NZ(myField(i))+NZ(x(i))
     End if
   next
End Function

--------------------------------------------------------------------------------

Windows XP Home Edition, Service pack 3

Dell Vostro 1500, Ram:3gb, CPU: Intel Core2Duo T7500 2.2ghz

Sorry, ignore the d & i (I updated the original post to show this).

The point is that ParentField is a field on the parent, so there is only one and we don't need to loop. We first try to use that value, if its null then we call sum on the children's values.

The point is I want to use the individual parent's fields along with the children's values as well to figure out the value. A workaround to show what I am talking about might be something like this:

You have three fields called TheSum, MySum and ChildrenSum.

ChildrenSum has a column equation of Sum(TheSum)

TheSum has a row equation of IIF( MySum, MySum, ChildrenSum)

So if you enter a MySum value then TheSum uses that, otherwise it's uses the sum of it's children's TheSum.

Hopefully that gives you a better idea of what I am trying to do. I'll add that it doesn't work right now Sum(TheSum) isn't working due to the issue column equations I describe in http://www.sqlnotes.net/drupal5/index.php?q=node/2070#comment-11381.

You will still need to loop to add up the children.

In any case, I don't think you'd be able to do what you want as MyField is also an array (other wise you wouldn't be able to get its value for any and all children) and -- AFAICT -- it doesn't include the parent. Which is logical at first glance, although there might be some advantages to include it as the upper bound of the array to achieve stuff like you want to achieve, but also to be able to exclude the parent itself when one wants to display a total or not.

i.e., taking the "invoices" grid in the sample DB :

- if I check "billable" under a few children that have multiple parents, any parents of these children will display the total regardless of whether "billable" is checked or not for that parent (which, at this point is expected since checking "billable" is only a condition saying : "yes, include me in the SUM";

- what would be nice though is to be able to not show this total under certain parents by unchecking "billable" for those parents... I guess this could be achieved by including the parent as the upper bound in the array... Maybe not though.

Of course, this would also mean changing the current equations to something like

Function mySum(IncludeIt, x)  ' Calculates the sum of the array x if IncludeIt is true + shows result in parent only if parent itself satisfies condition
  dim d, i
  d=ubound(x)

  If IncludeIt(d) <> true then   'Leave parent's content intact if condition isn't met for that parent
    mySum = x(d)
  else                            'Replace parent's content by the result of the column equation.
     for i=0 to d-1
       if IncludeIt(i)=true then mySum = mySum+NZ(x(i))
     next
  end if
end Function

... or something like that...

Does it make sense ??

--------------------------------------------------------------------------------

Windows XP Home Edition, Service pack 3

Dell Vostro 1500, Ram:3gb, CPU: Intel Core2Duo T7500 2.2ghz

Yes, you are getting at what I want. Thank you for your time/thoughts here and in the other threads. I have been thinking we could somehow do it at the parameter level rather than adding them to the array. And in fact, thanks to all your feedback I just came up with what I think is a good approach that gives us some other great advantages also. See http://www.sqlnotes.net/drupal5/index.php?q=node/2070#comment-11399.