Re: Expanding table: Calculating difference to value from previous row

[Mathilda_XF]

Hi there,

I am a complete newbie to LiveCycle Designer (ES2) and have created below document with an expanding table.

Shared Files - Acrobat.com

My aim is to make the form user friendly by adding a calculation in the field "Twist" which calculates the difference from the current row to the previous row.

I have no idea about Java Script, I am afraid, and also don't know if this is possible at all so it's probably a bad idea after all.

The reason for setting it up as an expanding table is so that the form can be used for all 32 instances the form would be used for (they would all require different amount of rows altogether).

The calculation would start in row 2 (Checklist.Table1), take the twist value entered in row 2 and calculate the difference to the twist value entered in row 1.

Then in row 3, it would calculate the difference between twist value in row 3 to row 2 etc.

So subtract value of field in rowⁿ with value in row^n-1 or something in this direction.

Is there anyone at all who could help me with this at all?

I'd be very grateful for any advice you could give me.

Thanks a lot!

Mathilda

[_Bruce_Robertson]

Hi Mathilda,

I'm guessing you want the value in the 'Observation' column, (or TextField4).  If so try the following JavaScript code in the calculate event of that field.

if (Row1.index > 0)

 var prevTwist = Table1.resolveNode("Row1[" + (Row1.index - 1) + "].NumericField4[1]").rawValue

 var result = Row1.resolveNode("NumericField4[1]").rawValue - prevTwist;

 if (result <= 0)

 this.rawValue = "";

 else

 this.rawValue = result;

 this.rawValue = "";

Here is my version of your form to test this code https://sites.google.com/site/livecycledesignercookbooks/home/Twist%20Measurements.pdf?attredirects=...

Regards

Bruce

[Mathilda_XF]

Hi Bruce,

You are awesome, thank you so much! Wowzers!

I amended the formula slightly to suit as the Twist column should actually show the results from Measured Cant values. I didn't explain that very well.

The thing is that the "Measured Cant" values can be positive and negative but all I need is the absolute change for them. I tried adding "Math.abs()" in various areas of the script but it doesn't help me much.

Example:     Measured cant          Twist

Row 1          +4                              ---

Row 2          -1                              600 (=3000/(-1-4))

Row 3          0                               3000 (=3000/0-(-1))

Would you be able to help me fine tune it?

Thank you ever so much!

Regards

Mathilda

************

topmostSubform.Main.Checklist.Table1.Row1.NumericField4[1]::calculate - (JavaScript, client)

if (Row1.index > 0)

{

  var prevCant = Table1.resolveNode("Row1[" + (Row1.index - 1) + "].NumericField4[0]").rawValue

  var result = 3000/(Row1.resolveNode("NumericField4[0]").rawValue - prevCant);

  if (result <= 0)

  {

  this.rawValue = "-";

  }

  else

  {

  this.rawValue = result;

  }

}

else

{

  this.rawValue = "";

}

[_Bruce_Robertson · Accepted solution]

Hi Mathilda,

Try this code;

if (Row1.index > 0 && !Row1.resolveNode("NumericField4[0]").isNull)

 var prevTwist = Table1.resolveNode("Row1[" + (Row1.index - 1) + "].NumericField4[0]").rawValue

 var result = Math.abs(3000 / (Row1.resolveNode("NumericField4[0]").rawValue - prevTwist));

 this.rawValue = result;

 this.rawValue = "";

Regards

Bruce

[_Bruce_Robertson]

Not sure what happened with the formatting there, try this;

if (Row1.index > 0 && !Row1.resolveNode("NumericField4[0]").isNull)

{

    var prevTwist = Table1.resolveNode("Row1[" + (Row1.index - 1) + "].NumericField4[0]").rawValue

    var result = Math.abs(3000 / (Row1.resolveNode("NumericField4[0]").rawValue - prevTwist));

   this.rawValue = result;

}

else

{

    this.rawValue = "";

}

[Mathilda_XF]

Bruce, you are a star!! Thank you so much!!! I really, really appreciate it!

Regards

Mathilda

[Mathilda_XF]

Bruce, you will probably tell me to go away.. However, as one last action I was asked to implement into the form, I'm trying to get the conditional formatting to change for the Twist field.

If the result greater or equal to 600, the background of the field should be green.

If less than 600 but greater or equal to 300, then amber.

If less than 300, then red.

I feel really bad for asking again but just in case you didn't mind, could you kindly advise where in the formula I could enter the conditional formatting bit?

Via Action doesn't work because - I reckon - there's already a Java Script in place for this field.

THANKS A LOT!!!

Mathilda

[_Bruce_Robertson]

Hi Mathilda,

Try this code;

if (Row1.index > 0 && !Row1.resolveNode("NumericField4[0]").isNull)

{

 var prevTwist = Table1.resolveNode("Row1[" + (Row1.index - 1) + "].NumericField4[0]").rawValue

 var result = Math.abs(3000 / (Row1.resolveNode("NumericField4[0]").rawValue - prevTwist));   

 switch (true)

 {

 case (result == Number.NEGATIVE_INFINITY || result == Number.POSITIVE_INFINITY || isNaN(result)):

 this.border.nodes.remove(this.border.fill);

 break;

 case (result >= 600):

 this.border.fill.color.value = "0,128,0";

 break;

 case (result >= 300):

 this.border.fill.color.value = "255,165,0";

 break;          

 case (result > 0):

 this.border.fill.color.value = "255,0,0";

 break;

    }

 this.rawValue = result;

}

else

{

 this.rawValue = "";

}

Regards

Bruce

[Mathilda_XF]

Bruce, you are amazing. Thank you SO much!! Mathilda over and out!!

[chris_l_j930896]

I am having a similar issue to this. I can get the calculation to run, but it is always based off the first instance of the row. I am calculating in the same column in each row, but I need the calculation to be made between the current row, and the most recent instance prior to it. Can anyone help me with this one? Thanks in advance.I'm sorry, I haven't figured out how to upload a PDF yet. Here is a copy of the FormCalc Script I am using to accomplish this:

if (FeedBtlVol>0) then

((Row1[*].FeedBtlVol-FeedBtlVol)/((Time2Num(Time.formattedValue, "h:MM A")-Time2Num(Row1[*].Time.formattedValue, "h:MM A"))/60000))

endif

[JainSa]

Dear Experts,

How to Unique the repeated text in a row.

Regards,

Jain