InfoQube IM - Community

Submitted by reesd on 2010/11/03 11:22

I seem to be missing something about when row equations are run. Let me walk through some things I tried, and perhaps someone can tell me why they didn’t work. Then I can update the row equations wiki page that I helped write in the first place :).

I created a new date field called ItemDate. I added the following to ItemDate and clicked recalculate. This did nothing. Perhaps because none of the items had this field (and therefore its row equation isn’t run)?
ItemDate = now()

So next I decided to add the Row Equation to another field I knew the items had, a text field I have called BatchArea. I hit recalculate. This did nothing either.
ItemDate = now()

So maybe I just am missing something with dates, so I tried this simple row equation in BatchArea. Hitting recalculate did nothing. Editing the batchArea field on an individual item did nothing either.
TaskID = 22

Finally, I tried an auto-assign on BatchArea to see if I could just get something to take a value. This works when I edit BatchArea, so I am missing something about when row equations are run.
AM:TaskID = 22

Thanks,
dave

Comments

1- All items "have all fields" accessible. Only they might not have data in them.

2- Row equations are run as soon as a parameter involved in the equation is touched/changed. So if you only have "=now()", nothing will happen unless you you select the field/cell and perform a recalc. Another option is to have the field to "auto-update" meaning that the row calculation will be re performed once a day, at midnight. If you want the field to =now() you need some trigger. Either put this in another field as A:itemdate=now(), or add some other parameter to your equation.

3- Are you selecting the itemdate field/cell, and then press shift-F9 ? It's that particular field that you want to recalculate, not the "item" in general

that last one (recalculate all potential fields for that item) would mean : "for that item, perform calculations on all fields having row equations in this DB". You could end up with an item having all kinds of values that has nothing to do with its nature (e.g. : a "percentage done").

Of course, Pierre could limit the calculation to only those fields shown in the grid, but I don't know if that's possible -- probably. Pierre ?

[quote=Armando]

1- All items "have all fields" accessible. Only they might not have data in them.

3- Are you selecting the itemdate field/cell, and then press shift-F9 ? It's that particular field that you want to recalculate, not the "item" in general

Of course, Pierre could limit the calculation to only those fields shown in the grid, but I don't know if that's possible -- probably. Pierre ?

[/quote]

So are you saying that any time a field is changed or at midnight with auto-update, all fields (including ones the item does have yet) have their row equations run? I don't seem to be seeing that.

Also, what exactly does Re-Calculate Shift-F9 do? Are you saying its the same as clicking the row equation "re-calculate" button in the field editor for every field? Or for every field that has auto-run enabled? Or something else?

[quote=reesd]

So are you saying that any time a field is changed or at midnight with auto-update, all fields (including ones the item does have yet) have their row equations run? I don't seem to be seeing that.

[/quote]

- Yes, any time a field that's involved in a row equation is changed, the result of the row equation changes. I.E : in the "Actual$" field from the sample DB, the row equation is : = ZN(- [Amount] * [paid] ) . If you change the "amount" or "paid" values, Actual$ will change accordingly.

- Yes, if you set the equation on auto-update, it will be recalculated for all fields with values. But this is ONLY for row equations. Column equations auto-recalc has nothing to do with auto-update every 24h. It just means that it'll auto recalc itself in the same way a row equation does it.

[quote=reesd]

[/quote]

Shift F9 (recalc) while focusing on a specific cell can be used when the result isn't automatically recaculated : in the case of column equations where auto-recalc isn't ON. This allow for better control in cases where you don't want some parts of column equations results to be changed, but only a few (maybe lower in the hierarchy...).

I don't know if that's clear.

That said, I think some of these functions should be more self-explicative. That's one of the biggest challenge in IQ.

Autoupdate should say : Auto-update every 24h

Autorecalc : ... No idea!

Note that there are a couple "bugs" concerning Column equations auto update : Id you manually delete a value, it isn't detected, and the autorecalc isn't performed.

[quote=Armando]

[quote=reesd]

So are you saying that any time a field is changed or at midnight with auto-update, all fields (including ones the item does have yet) have their row equations run? I don't seem to be seeing that.

[/quote]

- Yes, if you set the equation on auto-update, it will be recalculated for all fields with values. But this is ONLY for row equations. Column equations auto-recalc has nothing to do with auto-update every 24h. It just means that it'll auto recalc itself in the same way a row equation does it.

[/quote]

Sorry, want to get this right. Do you mean all fields that have a row equation or all fields that have a value (which I think of as the item having that field)?

I think you mean the former, but that is not what I am seeing. For example in the sample DB I add the row equation '= "test"' to Project and click the recalc button, it show in the status bar that it is running equations, but project is still blank for all items.

Thanks,

[quote=reesd]

Sorry, want to get this right. Do you mean all fields that have a row equation or all fields that have a value (which I think of as the item having that field)?

[/quote]

If a field has row equation, this row equation will produce a result only if its operands or parameters are modified/changed. Otherwise, nothing will happen.

Try :

= IIf( project <> "" , "Test" , "" )

= Item

Something will happen.

Maybe Pierre has a better explanation or example.

[quote=Armando]

[quote=reesd]

Sorry, want to get this right. Do you mean all fields that have a row equation or all fields that have a value (which I think of as the item having that field)?

[/quote]

If a field has row equation, this row equation will produce a result only if its operands or parameters are modified/changed. Otherwise, nothing will happen.

Try :

= IIf( project <> "" , "Test" , "" )

= Item

[/quote]

Ok, I think I have finally figured it out. Your second example worked for me, your first didn't, but this did:

"= IIf( Item <> "" , "Test" , "" )".

More importantly, the following only updated Project fields IF they had a Category field:

"=iif(Category <> "xxxxx" ,"test7","")"

and the following only updates Project fields IF they have a Category field OR a InvoiceID field:

=iif(Category + InvoiceID <> "xxxxx" ,"test8","")

So here are the rules for as I understand them:

field modified - If you edit a field, any row equations that have that field as an argument are fired on the item

This has always made sense to me, IQ needs to detect a change in the dependent field.

recalculate - When a field is recalculated (^F9 or auto-update at midnight) row equations are only fired they have a field argument that has a value

This is what I have not been getting. This means a Row Equation with no field arguments will never fire. It also means that if none of its field arguments have a value then it also will not fire.

I can understand this now, but I'm still not sure its 100% the right behavior. I would suggest if RowEquation has no field arguments it should fire also, but I that would be a feature request.

A couple other facts/quirks that are worth pointing out (and documenting once we agree I have it right):

RowEquations should not start with the field name you are assigning. They always update the field they are on.

It's also different from auto-assignment rules, so its an easy mistake to make. If you put a field name at the front it treats it as a comparison. I'll add you don't need the "=" either, just the equation itself. The current wiki is misleading on this one, it shows the following as an example Row Equation.

EndDate = StartDate + Duration

A manually updated field can not have its value updated by its own row equation.

So if you have the following Row Equation and enter "hello" the field value will stay "hello".

Project + " world"

This the same for auto-assign rules also, I actually don't like this behavior. I would love to be able to run a row equation or auto-assign on manually entered input to clean it up or change it. For example, reformat a phone number. I would like to add this as a feature request as well.

However, a field can update itself when recalculated

So if you have the same Row Equation and the Project field value is "hello", it will be changed to "hello world" when you recalculate the field (^F9 or auto-update). So depending on how you look at it, its a little inconsistent with what happens when the field is manually updated.

All those rules sound right Armando? If so I am happy to take a cut at updating the wiki, most the current page was pulled together by me from various threads and I think I didn't quite get it right the first time (a year ago).

[quote=reesd]

recalculate - When a field is recalculated (^F9 or auto-update at midnight) row equations are only fired they have a field argument that has a value

This is what I have not been getting. This means a Row Equation with no field arguments will never fire. It also means that if none of its field arguments have a value then it also will not fire.

I can understand this now, but I'm still not sure its 100% the right behavior. I would suggest if RowEquation has no field arguments it should fire also, but I that would be a feature request.

[/quote]

I don't know how may items you have in your DB, but I have 37566 at the moment. That's a lot of calculation to be performed, especially if it's on auto. So this is the logic behind it : if all equations are performed on all items, regardless the absence of values in their parameters, you'd end up with horrendous performance and weird values in all kinds of places where you'd not want them,

[quote=reesd]

A couple other facts/quirks that are worth pointing out (and documenting once we agree I have it right):

RowEquations should not start with the field name you are assigning. They always update the field they are on.

EndDate = StartDate + Duration

[/quote]

Right. This should be corrected and should be added to Mantis (in terms of making the rules more transparent).

[quote=reesd]

A manually updated field can not have its value updated by its own row equation.

So if you have the following Row Equation and enter "hello" the field value will stay "hello".

Project + " world"

[/quote]

In some way I think I agree... but I'd have to think a bit more about the implications. Maybe Pierre could chime in and say why it's as it is at the moment.

Maybe their could be an option to automatically use the value as a parameter in the equation. (MANTIS ?)

[quote=reesd]

However, a field can update itself when recalculated

[/quote]

Yup... This allows for more flexibility... :-)

[quote=reesd]

[/quote]

Yes, you did a great job at nailing them down. I never really did... I know what they are, but sometimes I have to double check as they are a bit counter intuitive.

Ok, I heavily reworked the wiki page at http://www.sqlnotes.net/drupal5/index.php?q=node/128. Please look it over for any mistakes.

[quote=reesd]

Ok, I heavily reworked the wiki page at http://www.sqlnotes.net/drupal5/index.php?q=node/128. Please look it over for any mistakes.

[/quote]

I seems pretty clear to me. Nice job!

I didn't notice any inaccuracies, but you might want to accompany this one (for example) :

Parent = SUM (children)

With a screenshot showing the exact syntax. ("Sum" is the only string that needs to be entered).

Another thing that could be discussed is conditional column equations. Did you know about these? I use them quite a lot.

It was first discussed there : Could inheritance or hierarchical sum (parent/children...) be conditional ?

Or more precisely : http://www.sqlnotes.net/drupal5/index.php?q=node/1084#comment-4355

The syntax is e.g. (this is what you'd right in the text box):

mySum(field

So this uses one of the functions provided by default. What it does is calculate the minimum value of a column/array only if field is true.

The function is

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

Thanks Armando :)

I do think Hierarchy Equations needs more work. But I haven't spent the time to understand them, so I didn't want to tweak that section too much until I do understand it. I think I understand your example (though it seems it was chopped a bit). It seems like Hierarchy Equation can use the field arguments (from the current item).

Once again though I don't understand it well enough to be sure I would put it in right, so You should feel free to add it if you like. That way nothing is lost in translation :). I did add some more bullets at the bottom to point out what I think is missing so far with some links. You should feel free to update it if you like since it sounds like you do use them.

Another thing that is missing is the null=0 functionality, I missed it because its hidden unless an equation has a value. Something that I think should change, I think the option should always be visible.

I am seeing some strangeness in the way arguments are passed for the column equations. Using the mySum above, I get the same result for either of these. Almost like its always passing children rather than ItemID:

Parents = mySum(Journal, children)

Parents = mySum(Journal, ItemID)

AFAICT, there's a bug with conditional column equations at the moment. I could be wrong, but I don't think it was fixed.

E.g., in a case where the condition is a Boolean field: "check/uncheck" the "condition" nothing happens.

In a sample DB:

1- Invoice grid

2- Check/uncheck billable

3- Nothing happens in the TOTAL column (which depends on "billable" to calculate its value).

I don't know if that could be the problem in your case. Maybe...

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

Windows XP Home Edition, Service pack 3

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

It might be related, but its not the same problem. Since the first one I listed works. It seems more than its passing the current field rather than the one I am listing as the parameter. I'm guess I'm going to stay away from column equations until Pierre gets to fixing them up a bit more.

[quote=Armando]

AFAICT, there's a bug with conditional column equations at the moment. I could be wrong, but I don't think it was fixed.

E.g., in a case where the condition is a Boolean field: "check/uncheck" the "condition" nothing happens.

In a sample DB:

1- Invoice grid

2- Check/uncheck billable

3- Nothing happens in the TOTAL column (which depends on "billable" to calculate its value).

I don't know if that could be the problem in your case. Maybe...

[/quote]

I'm seeing what I think is a related problem also. I built a small example based on what Pierre suggested. I have a sample DB and video showing the problem in the original thread - http://www.sqlnotes.net/drupal5/index.php?q=node/1084#comment-11390.

I am seeing that the sample DB doesn't work either, but I am confused on how to write Hierarchy Equations. I see that InvoiceTotal is just "mysum(Billable". Is the ",children) added automatically added as the last parameter? Is that what "((Children))" is supposed to mean?

>I see that InvoiceTotal is just "mysum(Billable". Is the ",children) added automatically added as the last parameter?

Yes, it's basically the "x" in the previously mentioned equation -- although not exactly as "x" corresponds to an array which has probably been calculated using both the field and the nb of children.

Only Pierre knows for sure. ;)

The syntax seems a bit weird and makes it hard to understand. Maybe should the UI dynamically adapt to what's written in the text box.

E.g. : when a conditional equation is added, dynamically complete it with :

children)

And when it's a simple calculation, with only one param :

(children)

I don't really get the double parentheses there : "((children))"

---------

As for the other issue (column conditional calculations) I'll wait for Pierre's take on it.

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

Windows XP Home Edition, Service pack 3

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

Screenshots to illustrate this idea (this isn't what IQ does at the moment -- it's a mock-up / suggestion) :

When what's written in the text box contains another field :

When a simple function (without any param except for the children array) is used :

Or something along those lines...

The first option presented would be "b", and as soon as the user enters a "(" in the text box, the UI adapts.

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

Windows XP Home Edition, Service pack 3

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

[quote=Armando]

The syntax seems a bit weird and makes it hard to understand. Maybe should the UI dynamically adapt to what's written in the text box.

E.g. : when a conditional equation is added, dynamically complete it with :

children)

And when it's a simple calculation, with only one param :

(children)

I don't really get the double parentheses there : "((children))"

[/quote]

Thank you for the screenshots, very helpful. I will look at adding those to the doc once its working again (as we are discussing at http://www.sqlnotes.net/drupal5/index.php?q=node/1084#comment-11398).

I'll add what I had assumed was there was a special variable called "children" and that IQ just substitutes a children array of the current field for that value. So you would do "Sum(children)" like this - Z:tempmwsnapsnap-000136-Field Properties_ myMin.png.

That said, now that it takes multiple hierarchy parameters, perhaps the right way is that it just takes normal field names, and those fields are converted to child arrays.

So for example, if you are working on a field called "FieldA" you would do this "Sum(FieldA)" rather than "Sum(". And in fact, you could just as easily do "Sum(FieldB)" and summarize a different field of your children. That would make it consistent with row equations, with just the change that children arrays are passed instead.

Actually! We could perhaps combine row and hierarchy equations in a way that would make things simpler and more powerful. Instead of a having a separate 'hierarchy equation", we could just introduce the idea of children parameters into row equations. For example, a normal row equation might be the following, where the current field is computed based on the current item's FieldC and FieldD values.

FieldC + FieldD

We could then introduce a children operator that means children parameter like %CHILDREN[FieldA]. So you could do something like this:

FieldC + FieldD + Sum(%CHILDREN[FieldA])

When IQ sees the %CHILDREN[] operator it passes an array of the item's children's field values, just like it does for hierarchy equations now - but you can pick and choose which parameters using the %CHILDREN operator.

While we can probably come up with something better than %CHILDREN for the operator, I do think this would be a clear yet very powerful approach. It would allow you to build combined row/hierarchy equations like I describe in use parent's fields in column equation and also answer the question of row equation and hierarchy equation order.

It also would allow for extension to other operators like PARENT, SIBLING (though that last one has recursion considerations), and maybe even PREVIOUS for the previous value (which could be very useful as well).

What do you think?

I think those are interesting ideas and they do seem more flexible, albeit probably not as user friendly for the average user.

That said... Pierre is into calendar development right now and I'd be very surprised if he'd have the time to look into that. So we should probably wait until calendar is finished before discussing these ideas more deeply.

BTW : maybe my last post wasn't clear, but the screenshots are mock-ups, suggestions, and don't actually reflect the current GUI.

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

Windows XP Home Edition, Service pack 3

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

[quote=Armando]

I think those are interesting ideas and they do seem more flexible, albeit probably not as user friendly for the average user.

BTW : maybe my last post wasn't clear, but the screenshots are mock-ups, suggestions, and don't actually reflect the current GUI.

[/quote]

I think its actually simpler, and more consistent with other macro/formula environments. Certainly more than a "magic" children field. But maybe that is because I am an Excel guy :).

I realized they are mockup-ups for the text after the input box, but the text in the input box is what you enter now - right?

I agree it needs more discussion and that we have to wait for calendar to be done (can't wait for that to be done so we can get back to the "important" stuff :). In the context of the new column equation bug you found though, maybe he will be digging at least a little into this area soon and can chime in a bit. Then we can discuss while he focuses on the calendar.

[quote]I think its actually simpler, and more consistent with other macro/formula environments. Certainly more than a "magic" children field. But maybe that is because I am an Excel guy :).[/quote]

I see what you mean. But I'm still not so sure about what is gained with that method.

Could you provide an example of what can't be done now but could be done using a unified row/column equation ? (Apart from what you were describing in another thread, about excluding/including parents from/in the equation, since this is a limitation that would exist whatever the method)

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

Windows XP Home Edition, Service pack 3

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

[quote=Armando]

[quote]I think its actually simpler, and more consistent with other macro/formula environments. Certainly more than a "magic" children field. But maybe that is because I am an Excel guy :).[/quote]

I see what you mean. But I'm still not so sure about what is gained with that method.

[/quote]

My main example would be if the parent has a value, don't use the children's value:

MyValue = IIF(MyValue, MyValue, Sum(%CHILDREN[MyValue]))

Or you could have a discount rate on your children's total:

Total = Sum(%CHILDREN[Total]) * [DiscountRate]

Or compute it differently based on the parent value:

Total = IIF( [MyMethod] = "SUM", Sum(%CHILDREN[Total]) , Avg(%CHILDREN[Total]))

I sure think all of those read easier than having top create multiple fields, a column equation, and a row equation to work across them.

Yes, but AFAICS, this is all achievable if Pierre included the parent in the array. You could then include a condition in you function so that when i=0 (i.e.: the parent) then check "field" value, and if it equals "whatever", do this or that.

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

Windows XP Home Edition, Service pack 3

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

[quote=Armando]

[/quote]

Yes, as long as you have a way of passing the current item's values (which you are calling the parent) and its children's value I think you have equivalent functionality. Including the parent in the actual array would break all the sum/max/min methods we have now though and MHO is a little confusing. I would much rather like to see a way of listing explicitly when I am passing an array of child values for a field or the current item's field value.

Sure, some equations would be broken, but it would be super easy to fix (just change where the loop starts in the function).

Equations would be equally -- if not much more -- broken if suddenly column and row equations were mixed in one... Don't you think so ?

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

Windows XP Home Edition, Service pack 3

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

I'll tweak my notation for adding columns to row equations. Instead of %CHILDREN, how about just using curly brackets like Excel does for array formulas (which are used for essentially the same thing as column equations in IQ).

So you could do this to show that Total is pulling the children's value.

Total = Sum( {Total} ) * [DiscountRate]

If you added this to row equations and left column equations around for a while then actually nothing would break. It would just be extending row equations.

There may be a performance consideration in alternative of adding the parent. IQ will have to pull all the children values even for values that you may only need the parent value. Also, when things break it will not be obvious. The functions already expect arrays, so there will be no error - things will just be computed wrong - which can be very hard to catch. I also can't think of an approach like that from my 20 years of coding, so it will not be intuitive for developers (and easy for them to miss since there is no runtime error).

[quote=reesd]

I created a new date field called ItemDate. I added the following to ItemDate and clicked recalculate. This did nothing. Perhaps because none of the items had this field (and therefore its row equation isn’t run)?
ItemDate = now()

[/quote]

If you want to assign values to a field, for series of items, the best way is to view the items in a grid, select them all and use the Properties Pane set a value

Hit: Int(Now()) will give you the date (without the time)

[quote=Pierre_Admin]If you want to assign values to a field, for series of items, the best way is to view the items in a grid, select them all and use the Properties Pane set a value

Hit: Int(Now()) will give you the date (without the time)[/quote]

Thanks Pierre, I hadn't seen that cool bit of functionality.

I'm still confused on the part of this thread though, hopefully Armando can walk me through it and once I finally fully understand it I will update the doc/wiki.