InfoQube IM - Community

Submitted by Armando on 2009/09/27 22:25

(This is actually both a suggestion and a question.)

In other words : inheritance or sum would "propagate" only when certain fields are ticked.

That would be great as it would prevent propagation of hierarchical sums/inheritance where it's not needed or not even... not wanted at all.

Comments

Not impossible, but I would need more info on how it would work...

basically, inheritance and hierarchy equations work on many items (unlike row equations), so to use the value of another field, one must ask, the field-value of which Item(s)?

I guess something like (using your own "max" function) :

function ShowMax(ThisFieldUsingFunction, OtherFieldImposingCondition)

    if OtherFieldImposingCondition = -1 then
        ShowMax = Max(ThisFieldUsingFunction) ' calculates the max of the array x
    else ShowMax = ""

end Function

I guess users would have to be able to include custom functions in the "hierarchy equations" section

OR, there could be an additional condition check box + a textbox for simple field condition (Ok = -1)

This was pretty much something I asked for a long while back, which is that hierarchy functions be able to take a collection of flelds, rather than just the current one, then pretty much whatever logic required could be built into the VBA, which would be fantastic.

As it is, it's quite limited in the real world. Taking multiple fields would allow weighted sums, conditional calcs, all sorts of use.

I have done some work hitting the raw db, trying to do this, but don't want to mess with the app too much. It's fairly complicated how to join & interpret all the relevent objects to achieve the calculations.

Well it's not easy, but I've done it - implemented fully conditional, hierarchical multi-field calculations & updates in InfoQube. And its a credit to Pierre's choice of data store.

Essentially, what i have done is setup an Access DB, which is attached to the datastore tables of an IQ SNDB, and programatically imported all the ¯q queries that convert the raw tables into joinable, usable queries. I've then written up the sql work to implement the business rules logic that I want for my application, which frankly is unlimited in cabability, thanks to IQ's complete use of standard Jet DB.

In my case, I have a conventional system of Goals, Issues, ToDo's, and progress updates, and have a system of hierarchical updates between these, based on defined fields. Only negative of this solution is that I have to run an external update process to perform the updates, which obviously, won't respect, and therefore has to repeat any of the systematic updates that IQ does for us.

Anyway, its a workable solution, but not one I would like to try & explain to anyone who hadn't done a LOT of db development.

Just for the record though, I still think the app would be greatly enhanced by allowing standard hierarchical calcs to take a collection of fields as parameters, then all this could be done from within the application. What I'm doing is great for me, but risky in that if I get anything wrong in an SQL statement, I'm going to screw my db.

This is good news, lucky_phil.

I haven't done any DB development... yet. So I won't ask for the details.

>Just for the record though, I still think the app would be greatly enhanced by allowing standard hierarchical calcs to take a collection of fields as parameters.

Yes... this would be fantastic. Even for very basic usage (e.g. ; limiting hierarchical sums to certain items in order to avoid having totals showing all the way up where they don't belong or, in order to exclude certain items from the operations, etc.)

Yes, that's exactly the sort of thing we need it for, i.e. only taking the past progress update below an item, or only aggregating data where the child record is of the same type as the parent, etc.

Would be great.

Would you be willing to post a sample of your IQ database?

My DB is rather large, and has quite a bit of commercial info in it, so can't really do that, but what I can do is give you the code that executes it (in Access). As an example, I have consructed the following query (called Last Status):

SELECT ¯Links.ID1, ¯qItem.Item, ProgressDate.ProgressDate, ParentDone.Done, ParentDone.FieldID, ProgressStatus.Status, ProgressStatus.FieldID, ProgressStatus.Modified, ProgressStatus.IDUser
FROM (¯qDone AS ParentDone RIGHT JOIN ((¯qProgressDate AS ParentProgressDate

INNER JOIN (((¯Links LEFT JOIN ¯qStatus AS ParentStatus ON ¯Links.ID1 = ParentStatus.ItemID)

INNER JOIN ¯qStatus AS ProgressStatus ON ¯Links.ID2 = ProgressStatus.ItemID)

INNER JOIN ¯qProgress ON ¯Links.ID2 = ¯qProgress.ItemID) ON ParentProgressDate.ItemID = ¯Links.ID1)

INNER JOIN ¯qProgressDate AS ProgressDate ON (ParentProgressDate.ProgressDate = ProgressDate.ProgressDate) AND (¯Links.ID2 = ProgressDate.ItemID)) ON ParentDone.ItemID = ¯Links.ID1)

LEFT JOIN ¯qItem ON ¯Links.ID1 = ¯qItem.ItemID;

then have the following Access code to perform the updates, which are done one at at time:

Public Function RollupLastProgress()
Dim sql As String

' update parent status that don't match last progress update
sql = "update LastProgress left join ¯qStatus on LastProgress.ID1 = ¯qStatus.ItemID " + _
"set ¯qStatus.Status = LastProgress.Status, ¯qStatus.ItemID = LastProgress.ID1, ¯qStatus.FieldID = [ProgressStatus.FieldID], ¯qStatus.Modified = LastProgress.Modified, ¯qStatus.IDUser = LastProgress.IDUser " + _
"where ¯qStatus.Status is null or ¯qStatus.Status <> LastProgress.Status;"
DBEngine(0)(0).Execute (sql)

' update parent done dates that don't match last progress update
sql = "update LastProgress left join ¯qDone on LastProgress.ID1 = ¯qDone.ItemID " + _
"set ¯qDone.Done = LastProgress.ProgressDate, ¯qDone.ItemID = LastProgress.ID1, ¯qDone.FieldID = 81, ¯qDone.Modified = LastProgress.Modified, ¯qDone.IDUser = LastProgress.IDUser " + _
"where LastProgress.Status = ""Done"" and ¯qDone.Done is Null"
DBEngine(0)(0).Execute (sql)

End Function

Since many users are interested in this, and it is quite simple to implement, I'll work on this tomorrow. Expect it in the next release

Yeah! :)

Since my post above, I've been doing some GREAT work with this conditional hierarchical calcs. I think advanced users are going to love it when Pierre releases this new functionality.

I'm no longer doing it in external attached db's, but rather using that to construct the complex hierarchical calcs, then cut & pasting this into the <source> of new IQ field definitions & using standard IQ to assign values.

It's still a bit manual, but less dangerous than doing external updates on the SNDB file. I now have a project management app tracking hierarchical Issues, Risks, Activities & progress updates in a nicely integrated solution. Only IQ's flexible outline db structure would be capable of this.

Thanks for the update, lucky_phil. All that sounds promising !

Progress report:

I've confirmed that the basic concept is feasible and got a prototype is running.

I just need to generalize it and add some equation parsing.

WKYP

Awesome, I can't wait.

Since I did the coding for this, I found IQ taken to a whole new level as I can automate business rules up hierarchies of tasks, etc, and include progress updating against all, which employs different hierachical logic (i.e. use last entry, not max or min, etc) for the same fields (i.e. depending on the type of child record.

It's taken my use of IQ from an incredible notepad, to a management application.

As you are working on it, one request you have probably already included (hopefully), the call to the rollup function should accomodate a variable number of parameters, i.e. you might be using 2, 3, more fields in the calculation.

Will be watching the releases post closely...

Hierarchy calculations (in Fields Properties) can be used to calculate sum of all children (for project hours, billing, etc)

This has been enhanced to pass parameters

e.g. If the InvoiceTotal field Hierarchy equation is set to:
  - Parents = mySum(Billable (Children)

And the mySum is defined (in the VB Editor) as:

' The following functions can be used in Hierarchy calculations. Typically: parent = mysum (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

Then, the InvoiceTotal field-values will be include in the total if Billing = true

The sample database "Invoices" grid was updated to show this feature.

Fantastic. Does it just handle two parameters, or is the parameter list variable?

any number of parameters you wish to use.

Cool!

So... How do I enter all that in the Hierarchy equation textbox ?

This is my function :

Function myMin(param1,param2,x) ' calculates the min of the array x depending on the value of 2 parameters
dim d, i
d=ubound(x)
for i=0 to d
    if not isnull(x(i)) then
      if (param1(i)=true OR param2(i)=true) and (i=0 or x(i)<myMin) then myMin=x(i)
    end if
next
end Function

When I try the equation, I get the error :

I tried different combinations (like "myMin(projet,Task(children)" , inspired by your example...) but can't get it to work...

I might have an error in my function... But it doesn't look like it.

Try the function: myMIN(Project,task

(check the sample database InvoiceTotal field. The syntax is a bit bizarre, I'll work on this)

Ok... It finally worked but I had to put the code in "This database" section instead of "User Code".

(I guess that could/should be fixed ?)

I'll now try more other possibilities to see what can be done... ;)

It should make no difference where you put the code, only remember that the following priorities is used

this database code
user code
system code

#1 is the highest priority

See Field Management Dialog - Equations for more details

Yes, I know... Thanks... it didn't work and it now does ! I must have done something weird but I really really don't know what !!?!

There is a weird thing happening (Appart from the problems with updates in calcultations mentioned in another thread) :

If an item is excluded from the equation of a parent = sum(condition,x) -- by making it not meeting the required conditions -- and it is the first children (and any disabled children going down the hierarchy has the same effect) --> Nothing happens when modifying values which are below children not meeting the condition.

Can someone confirm this ?

There might be other ocurences in other types of equations like children = (....) but I don,t have enough time now to test (I'm in the process of moving... very exhausting !)

hmmmm... this problem only appears with certain functions, like the "min" function using one or more function. ~~with 2 conditions (using the OR operator).~~

Everything seems to work fine (except for the recalculations not happening automatically in the contexts you already know...).

I cannot make any true generalization at this point, but if somebody can try with equations/functions using min, max, etc.

[Edit : tired and wrong post !]

Edited previous post...

Sorry, I know I should wait before posting some more, but... I tried several functions and -- at this point -- it only happens with the "min" function

Here it is :

Function myMin(param1,x) ' calculates the min of the array x
dim d, i
d=ubound(x)
for i=0 to d
    if not isnull(x(i)) then
      if param1(i)=true and (i=0 or x(i)<myMin) then myMin=x(i)
    end if
next
end Function

Anybody sees something weird here that would prevent it to work when the first item of an array (parent = sum(param1,children) doesn't meet the condition ?

I see at least one issue here, due to the current implementation (which may or may not be the final one):

When computing the function, only items that have a value for the field are passed to the function

So if we have for field1:

Then, the array passed to the function has 3 values, not 4. So the "isnull(x(i))" is never true.

The reason for this design choice (not passing nulls), is that non-properly handled nulls can mess up equation calculations (see null propagation). So passing nulls makes user code more messy for users.

One way around this, if of course to have an option in the field properties...

Thanks for your answer.

I'm experiencing some weird issues...Like Y/N fields that are *not* read only but that I *can't* check... I'm trying to find out what's happening, but still can't...

Maybe related to hierarchy calculations, since these fields are involved in equations/functions. Maybe not, but never experienced that before.

[quote]

I see at least one issue here, due to the current implementation (which may or may not be the final one):

When computing the function, only items that have a value for the field are passed to the function

So if we have for field1:

[/quote]

But this doesn't address the problem concerning the Min ou MyMin function. (Finding the min, but with a condition)

I don't have any problem when the field value is null. Especially that null=o for the field I'm using.

With this function and the next release), this works fine:

Function myMin(param1,x) ' calculates the min of the array x
dim d, i
d=ubound(x)
myMin=null
for i=0 to d
    if not isnull(x(i)) then
      if param1(i)=true then
        if isnull(myMin) then
            myMin=x(i)
        else
            if x(i)<myMin then myMin=x(i)
        end if
    end if
    end if
next
end Function

So is this in H3 or post H3?

I am now having no end of problems with the hierachical calcs failing if some of the passed parameters have blanks/null. The function fails with an error message, and do not seem to be able to work around no matter what.

Yes it is.

Any chance you can send the file over (with some instructions of how to create the problem) ?

Pierre,

Here you go. I've stripped out the content of my db. Please open the Goals grid, & update the status of the project entries under Goal 1.1. You will see all works fine, However, clear the type of one of the progress entries i.e. delete the value in the Type cell (=Progress), and it just keeps on failing.

This is one example of what is happening a lot. From what I have been able to work out. if the passed parameters have nulls, it just always fails. Can't seem to find a code solution that works.

Thanks,

I was able to reproduce the problem and fix it.

The issue was as follows: If a field-value is empty, a value is still required to send it to the hierarchy equation. This was only done for numbers. FYI, these are the values send to the hierarchy equation for the 4 data types:

Yes/No, Number and Date: 0
Text: Empty string

Thanks again. Next release is coming real soon.

Interesting, I had been trying checking for IsNull, IsEmpty, = "", but to no avail.

Will wait for next update (hopefully soon), as the constant error messaging is getting annoying.

Have just checked H4. Looks like its working as intended.

Thanks.

Just tested the whole thing for about an hour and it seems like working well.

A few things -- rapidly as I need some sleep :

1- some recalculations not happening in certain conditions (nothing you don't know, I believe... so...)

2- when using "parent = Min(param1,param2,children)" function, and using an OR operator ( if (param1(i)=true or param2(i)=true) then ) : if I remove all sub items values in fields corresponding to param1 and 2 (so that they don't meet the condition anymore) --> function error.

If the description is unclear, please tell.

[quote=Armando]

[/quote]

Were you able to reproduce this ? It happens all the time here. I'm using the function you proposed for myMin as a model.

use this function :

' my min using 2 parameters with OR operator.
'-----------------------------------------------------------------

Function myMin2(param1,param2,x) ' calculates the min of the array x
dim d, i
d=ubound(x)
myMin2=null
for i=0 to d
    if not isnull(x(i)) then
      if (param1(i)=true or param2(i)=true) then
          if isnull(myMin2) then
            myMin2=x(i)
        else
            if x(i)<myMin2 then myMin2=x(i)
        end if
      end if
    end if
next
end Function

1- create an item and 2 sub items

2- create a number or date field called "testMyMin" with the function above used in hierarchical calculation : parents = myMin2(yourfield1,yourfield2

3- in the grid, check the "yourfield1" or "yourfield2" fields for all created items

4- enter numbers or dates in in the testMyMin field. Everything should work as expected.

5- uncheck all yourfield1 (and/ or yourfield2) fields. When the last sub item will be unchecked this error will appear :

I tried modifying the function but it doesn't change anything.

I did what you described, and it is working fine (I used numbers for testMyMin), 2 yes/no fields (Test and Projet) starting with the file you've sent to me a while back.

Ok, you're right. It works with number. I tried. The problem is with dates.

Ah I see, there is an issue with dates. It is now fixed.

Nice. Thank you.

Pierre,

This example is no longer working for me in 0.9.25W1. Instead, when I click on the related boolean the field is actually erased. For example, in the attached database I have a field called myMin with a child equation of myMin(Today,children).

If I check a Today, that Item's myMin field is actually cleared. And no total is computed happens.

Here is a video of it happening...

http://screencast.com/t/ziDW1dBPYleD

Armando has reproduced a different Hierarchy Equation issue in the sample DB. See http://www.sqlnotes.net/drupal5/index.php?q=node/2070#comment-11392.

I can't download your test.sndb

How do you enter your equation in IQ's GUI (the equation section of the field management dialog) ?

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

Windows XP Home Edition, Service pack 3

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

[quote=Armando]

I can't download your test.sndb

How do you enter your equation in IQ's GUI (the equation section of the field management dialog) ?

[/quote]

I can't download it either. Maybe its the suffix, I've attached it as a zip. Let's try that.

Also, here I have attached screenshots.

I've tried both "myMin(Today" and "myMin(Today,children)" (as we are discussing in when are row equations run? ). I get the same result where the child's field is actually erased.

I can confirm that there are some more problems with column equations (maybe even row equations, or a combination of both...??). I'm not sure if this is in W1 AND W2, unfortunately, I don't have the time to test thoroughly

Pierre: Is there a way to revert to W1 ? at the present moment, I can't : when I try to revert, all my toolbars appear empty, etc.

Thanks.

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

Windows XP Home Edition, Service pack 3

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

I've been doing some more tests. Playing with the condition(s) of a a conditional column equation can effectively erase data when there's no simultaneous row equation.

(To prevent that, it's of course possible to include a row equation for the Field With A Conditional Coloumn Equation (FWCCE) going like "=FWCCE"... But...)

This need to be fixed ASAP.

Pierre ?

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

Windows XP Home Edition, Service pack 3

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

Pierre,

You are a freaking genius!!!!!

I've just done my first hierachical calc, rolling up progress updates from my project management DB, using 3 fields to calculate 1 rollup, and even with the quirky syntax identified earlier, worked perfectly! This is going to be sooooooo useful.

Please let us know if you revised the calling syntax required as this will likely break our DBs using the new functionality.