Help needed with error relating to a flag field (TSQL) interacting with a script

[fhmurray]

Hi,

Background

I have a matrix of numbers which need to update both within and across workspaces.  I have been working on the Javascript to get the updates to roll up from one WS to another and this is working fine for the first set of numbers which need to update.

Separately, the matrix fields have computed formulas to add, subtract and total across the matrix, and these are working fine.  

There is then a flag field using TSQL to show the field as a colour block depending on whether the answer to a calculation of various matrix fields is >0, <0 or =0.  This was working fine last week before I activated the script for rolling up numbers.

Problems

When I activated the script to roll numbers on to the next workspace, it seemed to work fine with no error messages for the first set of fields that needed to update (but these don't trigger the flag field).  When I got to the 2nd set of numbers from which the calculations are made for this flag field, it came up with the error shown in the attachment below.

What has confused me a bit is that the script has no direct impact on the Flag field; and that previously, the Float field was able to handle colours just fine - the error message seems to suggest it is this it is having problems with.

I am guessing that it is some kind of clash between javascript and TSQL and so I need to translate the TSQL code into Javascript.  I had a go, and it got rid of the error, but didn't change the field to red as I hoped.

Attempts to correct error

Here's the original TSQL code in the computed field box of Field 'CAD_HRS_DIFF_FLAG' (Float type)

[my bold & spacing for ease of reading only]:

(CASE WHEN (((CAD_USED_HRS_TO_DATE + CAD_SCEN_WK_HRS) - (CAD_SOLD_HRS + CAD_PV_HRS - CAD_SUBCON_HRS)) > 0)

THEN '<span style="text-align:center; line-height:25px; color:red; background:red; width:25px; float:left; height:25px;">RED</span>'

WHEN (((CAD_USED_HRS_TO_DATE + CAD_SCEN_WK_HRS) - (CAD_SOLD_HRS + CAD_PV_HRS - CAD_SUBCON_HRS)) = 0)

THEN '<span style="background:orange; color:orange; width:25px; float:left; height:25px;">ORG</span>'

WHEN (((CAD_USED_HRS_TO_DATE + CAD_SCEN_WK_HRS) - (CAD_SOLD_HRS + CAD_PV_HRS - CAD_SUBCON_HRS)) < 0)

THEN '<span style="text-align:center; line-height:25px; color:green; background:green; width:25px; float:left; height:25px;">GRN</span>' END )

Attempt to convert SQL flag field code to JS

var HrsDiff = CAD_HRS_DIFF;
var Flag = CAD_HRS_DIFF_FLAG;

for (HrsDiff; HrsDiff < 0;)
{Flag (field.style.backgroundColor='red');
}

for (HrsDiff; HrsDiff = 0;)
{Flag (field.style.backgroundColor='orange');
}

for (HrsDiff; HrsDiff > 0;)
{Flag (field.style.backgroundColor='green');
}

I'm definitely past the limit of my JS knowledge here (as you can probably tell!) and would therefore welcome a bit of help please.

Many thanks,

Fiona

[fhmurray]

As you would no doubt expect, it has the same error for all 3 colours (see attachment)

[fhmurray]

Ah, it has now replaced part of the code in my original post with smileys...!

I'll try again: 

(CASE WHEN (((CAD_USED_HRS_TO_DATE + CAD_SCEN_WK_HRS) - (CAD_SOLD_HRS + CAD_PV_HRS - CAD_SUBCON_HRS)) > 0)

THEN '<span style="text-align:center; line-height:25px; color:red; background:red; width:25px; float:left; height:25px;">RED</span>' 


WHEN (((CAD_USED_HRS_TO_DATE + CAD_SCEN_WK_HRS) - (CAD_SOLD_HRS + CAD_PV_HRS - CAD_SUBCON_HRS)) = 0) 

THEN '<span style="background:orange; color:orange; width:25px; float:left; height:25px;">ORG</span>' 


WHEN (((CAD_USED_HRS_TO_DATE + CAD_SCEN_WK_HRS) - (CAD_SOLD_HRS + CAD_PV_HRS - CAD_SUBCON_HRS)) < 0) 

THEN '<span style="text-align:center; line-height:25px; color:green; background:green; width:25px; float:left; height:25px;">GRN</span>' END )

 and

//Attempt to convert TSQL flag field code to JS

var HrsDiff = CAD_HRS_DIFF;
var Flag = CAD_HRS_DIFF_FLAG;

for (HrsDiff; HrsDiff < 0;)
{Flag (field.style.backgroundColor='red');
}


for (HrsDiff; HrsDiff = 0;)
{Flag (field.style.backgroundColor='orange');
}


for (HrsDiff; HrsDiff > 0;)
{Flag (field.style.backgroundColor='green');
}

[bastien.mazeran]

Hi Fiona,

I would not recommend using JavaScript to display status by color. The initial error seems to indicate that you are trying to feed the item field with a string when the field is expecting a float value. Could you please post a simplified version of your script and help me reproduce the first error?

Regards,

[fhmurray]

Hi Bastien,

Thank you for the reply, I'll try and give you the information you need to help me.

The starting point is a 10 column matrix recording hours for 5 different worktypes (rows). For each worktype the forumlae for calulations is identical.

As a simplified version, what I am trying to achieve is:

when FIELD 9 is <0 or =0 or >0; Field 10 displays as Green, Orange or Red respectively. As I was using the computed field forumla box to make this work, I obviously can't use previously computed fields (FIELD 9) without writing out the whole calculation long hand. So it looks like this:

(CASE WHEN (((FIELD 5 + FIELD 7) - (FIELD 1 + FIELD 2 - FIELD 3)) > 0)
THEN '<span style="text-align:center; line-height:25px; color:red; background:red; width:25px; float:left; height:25px;">RED</span>'

WHEN (((FIELD 5 + FIELD 7) - (FIELD 1 + FIELD 2 - FIELD 3)) = 0)
THEN '<span style="background:orange; color:orange; width:25px; float:left; height:25px;">ORG</span>'

WHEN (((FIELD 5 + FIELD 7) - (FIELD 1 + FIELD 2 - FIELD 3)) < 0)
THEN '<span style="text-align:center; line-height:25px; color:green; background:green; width:25px; float:left; height:25px;">GRN</span>' END )

You are right, that I have Field 10 designated as a Float field in order for it to be able to handle the calulated numbers and know if they are > < or = to 0.

(I took this formula from the Flag field in Inspections, where admittedly it is a Single Line Text field, because it only has to match the text string against the numbers 1, 2 or 3 in that example). See Attachment 1 for the set-up.

---

However, I can get it working perfectly as a Float field diplaying the colour - see screenshot of the new record I have just created (Attachment 2). Here the CAD hours are blank as I have changed this to a Single Line Text field with the same formula to generate the colour (to test) but nothing happens.

So, when I create or edit the record as is, there is no problem - the Float field can handle the colour block just fine.

---

The problem seems to come when I then activate a separate 'On Edit' custom script (in Javascript). This script has been written to examine certain fields in the matrix (FIELDs 5 and 7) to update them into another workspace. As soon as I add this script to my Workspace behaviours, and try and save the record, it throws up the error, showing the details I shared previously on the custom javascript log.

However the script itself does not directly work on either Field 9 or 10! And all it is doing is reading numbers that have been inputted into the record and writing them to another workspace - any calculations are still being done through the computed field formula boxes and are working fine - until FIELD 10.

When I deactivate this On Edit behaviour, then I can successfully save the record and the colour block appears again as planned.

---

I tested this by removing the formula from the first worktype row (CAD) and completing the numbers in that row, while keeping the Flag computed formula for the other worktypes, but not completing the numbers.

As you can see in Attachment 3, it computed the hours correctly in the first row (CAD), but obviously does not generate the colour flag. For the other worktypes, the flag formula was left in, but as the 2nd set of inputted hours (FIELD 7) was not completed, this didn't trigger the calculation for the flag field, and even with the custom JS script activated, there were no problems saving it.

---

So my conclusion so far is that for some reason, having both types of coding working on this matrix causes some kind of clash when the 2nd inputted column of numbers (FIELD 7) is filled in. The help I need is to understand why and to find the solution!

Let me know if you want to see the On Edit script as well.

Thanks,

Fiona

[bastien.mazeran]

Hi Fiona,

I have logged new support case to start working on your issue. I will contact you by email shortly.

Regards,