Discussion on answer by Mohamed El-Qassas MVP: JSON Sharepoint Calculated Columns

Feeds

00:05

0

Replace the current IF condition in the Background attribute with the below one: = if(@currentField < 0 ,'#CB4335',if((@currentField <= ([$cSDP_x0020_Hardware_x0020_Budget] * 0.15)) && (@currentField > 0),'#FFC7CE',if((@currentField <= ([$cSDP_x0020_Hardware_x0020_Budget] * 0.3)) && (@currentField

user94309

Thanks. Its not row 5 that is the issue. I actually need that one to be red as it was. Its rows 6 and on in the example photos. Where budget - hardware costs or actual hardware costs are blank, it seems JSON will not apply the code when using the If statements unless these fields have a value? Instead it returns the white formatted cell.?

Mohamed El-Qassas MVP

@DavidLane As you have mentioned and as per my understanding, The issue for blank value or 0 Right? If it's not correct. please, share the conditions that you would like to apply and I will try it on my side!

user94309

Thanks @melqassas Yes when the variance field is blank (as a result of the budget or actual fields being blank) i can not get it to be green. i just added another screenshot in my original post that should help illustrate what i am seeing better and that is using my original code. thanks for the help

Mohamed El-Qassas MVP

How you add variance value, is it calculated automatically?

or you add it manually

user94309

variance is a calculated column/ field with the formula =[cSDP Budget - Hardware Costs]-[cSDP Actual - Hardware Costs]

Mohamed El-Qassas MVP

00:05

Okay, I got it, I will test it on my side with static values based on your last image

Are you here?

user94309

hi yes

sorry didnt see it get moved here til now :) wasnt sure it it would work with me beign a new user too :)

Mohamed El-Qassas MVP

no prblem

problem*

I have updated the fromat

could you please check it and feedback me

user94309

cool

where is the updated code posted?

Mohamed El-Qassas MVP

= if((@currentField == 0) || (@currentField == ''),'#dff6dd',if(@currentField < 0 ,'#CB4335',if((@currentField <= ([$cSDP_x0020_Hardware_x0020_Budget] * 0.15)) && (@currentField > 0),'#FFC7CE',if((@currentField <= ([$cSDP_x0020_Hardware_x0020_Budget] * 0.3)) && (@currentField > 0),'#FFEB9C',''))))

user94309

:) nvm

Mohamed El-Qassas MVP

00:14

this is the output

just replace the old if condition with the new one

and tell me :)

user94309

chercking now

jsut tryign to figure that out

Mohamed El-Qassas MVP

share with me your result in an image

you cna use the upload button to upload a screenshoot

user94309

i dotn see an upload sorry?

Mohamed El-Qassas MVP

I am pretty sure it should work now, I have added the same values as your side

beside the chat textbox

you can find send and upload button

can you find it?

user94309

i only hve send no upload

ah help says i need a 100 point to upload in here

thats why

Mohamed El-Qassas MVP

00:20

No issue, just upload a new image in a new quest and don;t submit it and share with me the image url

Got it?

user94309

dm2302files.storage.live.com/…

so we are matched but its all of the empty rows that need the variance column so show green

Mohamed El-Qassas MVP

I opened your image, what's the issue now :)

Okay,

which column you would like to check if it's empty other than variance

user94309

dm2302files.storage.live.com/…

here is what it looked like in excel

im guessing we need to check the budge column since that is what i am using for the lookup in the code?

Mohamed El-Qassas MVP

got it, let me check :)

Mohamed El-Qassas MVP

00:52

i

Hi

are u still here?

user94309

i sure am :)

any luck?

Mohamed El-Qassas MVP

Yes, I am working on it, one moment please!

user94309

no problem and no rush

your help is very much appreaciated!

Mohamed El-Qassas MVP

Could you please, try this on your side to just test

if(([$cSDP_x0020_Hardware_x0020_Budget.length] == 0) || (@currentField == 0) || (@currentField.length == 0) ,'#dff6dd',if(@currentField < 0 ,'#CB4335',''))

I have noted that when add additonal condition depends on the budget it's not worked as expected

so the solution here is you must set budget value to the default value 0

in this case it will get green

David Lane

ok

Mohamed El-Qassas MVP

01:07

Is it considered a solution for you

?

David Lane

checking now and just understanding

Mohamed El-Qassas MVP

if yes, please mark my answer as accepted it's the true sign beside my answer

David Lane

so what you are saying is basically we check a condition that checks the character length of the fields rather than the value ?

Mohamed El-Qassas MVP

yes, but this would work if we don;yt add additonal condition related to the other columns

just check the above format to understand it

and to overcoem this issue you should set the empty budget cell to 0

because the calculation depends on it

you got it?

David Lane

i get a different response kinda i think

just chceking

Mohamed El-Qassas MVP

01:12

the above is just fro test

but the final should be

David Lane

its workign for all of the blank rows that have no values in budget and actual at the moment

Mohamed El-Qassas MVP

= if((@currentField == 0) || (@currentField.length == 0),'#dff6dd',if(@currentField < 0 ,'#CB4335',if((@currentField <= ([$cSDP_x0020_Hardware_x0020_Budget] * 0.15)) && (@currentField > 0),'#FFC7CE',if((@currentField <= ([$cSDP_x0020_Hardware_x0020_Budget] * 0.3)) && (@currentField > 0),'#FFEB9C',''))))

David Lane

yes ok let me see

Mohamed El-Qassas MVP

but it requiers to set the empty cell for budget column as 0 because all calculation depends on it

David Lane

why does it work with the test though and no 0 in teh budget column ?

Mohamed El-Qassas MVP

01:14

because the id deosn;t condition like [$cSDP_x0020_Hardware_x0020_Budget] * 0.3)

so it's working probley

there is no dependancies here so ut works

when you add a calculation that depends on empty it will not work

I hope you got it now

David Lane

so then it would be no different than the original code i did? as when i have a zero in the budget column with my original code i get the same result?

Mohamed El-Qassas MVP

No there is different,

it's your won

own

if(@currentField < 0 ,'#CB4335', if(@currentField < ([$cSDP_x0020_Hardware_x0020_Budget] * 0.15) ,'#FFC7CE', if(@currentField < ([$cSDP_x0020_Hardware_x0020_Budget] * 0.3),'#FFEB9C', '#dff6dd'))

it's what I have provided

check if variance is not empty

= if((@currentField == 0) || (@currentField.length == 0),'#dff6dd',if(@currentField < 0 ,'#CB4335',if((@currentField <= ([$cSDP_x0020_Hardware_x0020_Budget] * 0.15)) && (@currentField > 0),'#FFC7CE',if((@currentField <= ([$cSDP_x0020_Hardware_x0020_Budget] * 0.3)) && (@currentField > 0),'#FFEB9C',''))))

but the dependency column should have value and if it's empty it should be 0

BTW, you can set it to 0 if it was empty

David Lane

im a little lost, jsut wrapping my head around it

i get the code i wrote vs your is different

what i see is the same result through...... jsut a different way of evaluating it.....

but im jsut checkin g

Mohamed El-Qassas MVP

wait

I will send something for you will solve this issue as a workaround

David Lane

ya so the test code got us close jsut didnt have the other colors, but once its integrated with the other "ifs" then the green on the blanks is gone?

standing by

David Lane

01:47

bbin 5

bk

what are you thinking?

Mohamed El-Qassas MVP

02:36

I am sorry for the delayed response,
As I informed you, you should have a default value for the empty column (budget) because your format has a calculation that depends on it!
If we have excluded such calculation it would work as I provided you the small format. otherwise, the current format in my answer should work with you for the budget column with 0 values!
I tried to do my best to help so if you have found a better solution please post it, otherwise, I have explained the issue and the workaround and the format, so you should Mark the answer as accepted if it's only the current solution…

David Lane

oh ok no worries i thought you ahd another idea as your alst comment was wait your sending me something :)

appreciate the help , i guess ill have to do some data cleanup on the items that were moved over to start with

Thanks!

Transcript for

Discussion on answer by Mohamed El-Qa…