Item fields and Grid fields

[CodeMonkey831]

Is a field defined in a grid usable as part of a computed Item field?  Every time I try, I get the warning "The column <name> is undefined".  Note <name> is the field ID I try to use as part of the calculation.

[jpiggee]

Hi Jason,

When working with fields in the Grid, if a field is already being used as a computed field, you are unable to reuse this field in a different computed field. You will need to either roll it up into one mass computed field or unload this field into the item workspace then pull it back by computed work.

If this field is not already being used as a computed field then the way to access this item is by using “the absolute item name with would be “ITEM” or the field ID not the field name.

[CodeMonkey831]

Thank you for the reply, Joe.

Not sure if I explained clearly enough.

In a Grid, I have a field named "Hours", the ID is "HOURS".  This is not a computed field, but it is aggregated as a SUM.

In the Item Details, I have a computed field named "Status", the ID is "STATUS", which I would want use a formula that includes aggregated sum of "Hours" field in the Grid.

Is this possible only using the computed fields, or would a script be able to do this?

Additionally, are there better workarounds for the inability to use other computed fields inside another computed field?  Re-using the same formula repeatedly seems redundant and inefficient.

[jpiggee]

Hi Jason,

Still not completely clear on what your trying, so hopefully one of these scenarios hits it if not let me know.

Are you trying to use the aggraded SUM of HOURS + the aggregated SUM of STATUS, and place the total of that on the Item details? If so you would need JavaScript for that.

If you are trying to do it one line at a time then that would be a simple computed field value of HOURS*STATUS in a third column.

The reason for not having computed fields available for other computed fields is that the value of the field is unknown until the item is saved. Given that all scripts, be it JavaScript or SQL, run at the same time there is no way to insure that the computed field of the dependent item will know the value for a given item if that other item is a computed field as well.

[CodeMonkey831]

Here's a simplistic scenario of what we are trying to accomplish:

Grid Tab

Row ID      Month       Hours

1                Jan           150

2                Feb           118

3                Mar           147

                          Total 415 <-aggregated "HOURS" field

Item Details

Q1 Status    "Ok" <-computed field "STATUS"

Status' computation

CASE
    WHEN (CAST(HOURS as FLOAT) > 420)
        THEN "Good"
    WHEN (HOURS <= 420 AND ACTUAL > 330)
        THEN "Ok"
    WHEN (HOURS <= 330 AND ACTUAL > 235)
        THEN "Warning"
    ELSE
        THEN "Bad"
END

[jpiggee · Accepted solution]

Jason'

Based on that scenario you have to create an addition area to script to gather the sum of the Hours as a variable. Then compare that to the case.

Given that the grid is now completely accessible by script, this would need to be completely done by javascript.

i.e.

for (var index in item.grid){

    sumTotal1 += item.grid[index].HOURS;}