[Ben H]

Hi,

I have a spreadsheet with the following formula

=IF(K13=1,1,0)+IF(K15=1,1,0)+IF(K17=1,1,0)+IF(K18= 1,1,0)+K14+K16

K13, K15, K17, K18, are all yes or no selection fields and K14and K16 are numerical values

The formula basically adds a value of 1 each time a yes is selected to the combined values of all the fields.

I want to replicate this with a custom field and a formula in NetSuite, so far I have the below, it doesn’t error but it also doesn’t produce any result, field relationship to excel formula below.

K13(custrecord_acsc_aaweb_jaws_req)

K15(custrecord_acsc_aaweb_nvda_req)

K17(custrecord_acsc_aaweb_vo_req)

K18(custrecord_acsc_aaweb_sn_req)

K14(custrecord_acsc_aaweb_add_jaws_ver)

K16(custrecord_acsc_aaweb_add_nvda_ver)

Code:
(CASE WHEN {custrecord_acsc_aaweb_jaws_req} = ‘Yes’ then {custrecord_acsc_aaweb_add_jaws_ver}+1 END)+
(CASE WHEN {custrecord_acsc_aaweb_jaws_req} = ‘No’ then +0 END)+
(CASE WHEN {custrecord_acsc_aaweb_nvda_req} = ‘Yes’ then {custrecord_acsc_aaweb_add_nvda_ver}+1 END)+
(CASE WHEN {custrecord_acsc_aaweb_nvda_req} = ‘No’ then +0 END) +
(CASE WHEN {custrecord_acsc_aaweb_vo_req} = ‘Yes’ then +1 END)+
(CASE WHEN {custrecord_acsc_aaweb_vo_req} = ‘No’ then +0 END) +
(CASE WHEN {custrecord_acsc_aaweb_sn_req} = ‘Yes’ then +1 END) +
(CASE WHEN {custrecord_acsc_aaweb_sn_req} = ‘No’ then +0 END)
Is this possible or do I need to come at this from a different route?
This is a cached copy. Click here to see the original post.

[Olivier Gagnon NC]

That basically looks ok to me. The 2 points I would mostly verify are:

1. What do the fields really return? ‘Yes’ ‘No’ or something else? NS is notorious for this kind of tricky stuff.

2. Try casting your field into numbers, as in […] then to_number({custrecord_acsc_aaweb_add_jaws_ver})+1 […]

[k_dunc]

Hi Ben H,

A couple suggestions:

1. Just an FYI, in Excel, you can use a SUMPRODUCT formula to tidy up that Excel formula a bit.

2. Your NetSuite code could perhaps be simplified by removing the rows where you’re checking for “No”. I don’t think these are necessarily doing anything for you?

3. Your NetSuite code does not actually reflect your Excel formula. For example, the Excel formula basically states that if any of those 4 fields are equal to “1”, then add 1, and then after checking those 4 fields, then add the values in K14 and K16. Your NetSuite formula is basically saying, if ‘K13’ = ‘Yes’, then add K14 and 1 together before continuing. Now maybe this is fine: i.e., there’s only ever a value in ‘K14’ when ‘K13’ = ‘Yes’? Similarly for ‘K16’ and ‘K15’.

4. If you really need the ‘No’ rows, then perhaps you’re better off using ELSE rather than another CASE WHEN?

5. My only other suggestion at this time Ben, would be to simply try a couple lines of your formula at a time. E.g., cut and paste your current formula to Notepad for instance, then copy and paste rows 1 and 3 back into NetSuite and see if just those two lines give you the desired outcome. If not, then at least you’ve isolated the issue. If so, then add rows 5 and 7 and see if it all works.

Are you storing the value in your Custom Field? Is this formula set as the default value?

Hope some of this helps Ben. Let us know how you go.

Cheers,

Kirk.

[errol]

Are the fields you are checking for ‘Yes’ select fields of Yes or No? or are they checkboxes? I would recommend something like this:

Code:
DECODE({custrecord_acsc_aaweb_jaws_req}, ‘Yes’, 1, 0) +
DECODE({custrecord_acsc_aaweb_nvda_req}, ‘Yes’, 1, 0) +
DECODE({custrecord_acsc_aaweb_vo_req}, ‘Yes’, 1, 0) +
DECODE({custrecord_acsc_aaweb_sn_req}, ‘Yes’, 1, 0) +
{custrecord_acsc_aaweb_add_jaws_ver} +
{custrecord_acsc_aaweb_add_nvda_ver}
However, if they are checkboxes, you probably need to look for T or F. Also, I usually refer to the ID’s. For example (assuming they are select fields and Yes is ID 1):

Code:
DECODE({custrecord_acsc_aaweb_jaws_req.id}, ‘1’, 1, 0) +
DECODE({custrecord_acsc_aaweb_nvda_req.id}, ‘1’, 1, 0) +
DECODE({custrecord_acsc_aaweb_vo_req.id}, ‘1’, 1, 0) +
DECODE({custrecord_acsc_aaweb_sn_req.id}, ‘1’, 1, 0) +
{custrecord_acsc_aaweb_add_jaws_ver} +
{custrecord_acsc_aaweb_add_nvda_ver}
Goodluck,

Errol

[Author]

Posts