Formula no longer producing results

[jbakerstull]

Good evening all, 

The formula below is designed to return a value when a value is selected from [Entry Exit Provider Name]. At first, the formula was working correctly, but now it does not. I'm not certain why. Can the formula be written a different way?

RangeSum(
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'LSS - Women''s on Grant/Nancy''s Place(45)') > 0,38),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'LSS - FM Faith on 8th(52)') > 0,95),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'LSS - Men''s on Grant(43)') > 0, 89),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'LSS - FM Faith on 8th(52)' And 'LSS - Women''s on Grant/Nancy''s Place(45)' And 'LSS - Men''s on Grant(43)') > 0, 222),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'LSS - FM VA Emergency Housing(222)') > 0, 24),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'HH - Emergency Shelter(263)') > 0, 16),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'Maryhaven - EC Safety(105)') > 0, 32),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'Maryhaven - EC Shelter2Housing(254)') > 0, 18),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'SE - FOH Men''s Shelter(47)') > 0, 130),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'VOAGO Men''s Shelter(64)') > 0, 40),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'VOAGO VA Emergency Housing(209)') > 0, 15),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'YMCA - Van Buren Family Shelter(305)') > 0, 64),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'YMCA - Van Buren Women''s Shelter(258)') > 0, 139),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'YMCA VB Pregnant Women''s Shelter(320)') > 0, 12),
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'YWCA Family Center(69)') > 0, 50)
)

[JordyWegman]

Hi,

Did you change something in your formula? 

1. Yes? Undo the change and check where it went wrong
2. No? Delete everything excep the rangesum and the first if. Start from there and keep adding parts until you have found the one that is causing your problem.

Jordy

Climber

[jbakerstull]

I didn't change the formula. I'm only a front end user and have to rely on our vendor.  The vendor must have changed something in the data background. When I was testing the formula, I changed the ">" to "=", values will appear for even instance that was changed to "=".  The formula is acting like it's not recognizing "GetFieldSelections".

RangeSum(
if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'LSS - Women''s on Grant/Nancy''s Place(45)') = 0,38),

[jonathandienst]

Put the expression =GetFieldSelections([Entry Exit Provider Name]) into a text box and then make selections to try and see what's happening.

I think this part of the expression may be causing the problem:

if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'LSS - FM Faith on 8th(52)' And 'LSS - Women''s on Grant/Nancy''s Place(45)' And 'LSS - Men''s on Grant(43)') > 0, 222),

Is the intention for all 3 (And) or Any (Or). You will need something like:

if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'LSS - FM Faith on 8th(52)') > 0  
	And SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'LSS - Women''s on Grant/Nancy''s Place(45)') > 0
	And SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'LSS - Men''s on Grant(43)') > 0, 222),

[jbakerstull]

I inserted the formula into a new KPI, it didn't return any results when I selected the three providers. 

[jbakerstull]

If i input GetFieldSelections([Entry Exit Provider Name]), it will return the provider name that is selected. 

[jbakerstull]

If i try 

=if(SubStringCount(GetFieldSelections([Entry Exit Provider Name]),'LSS - FM Faith on 8th(52)') > 0,38) 

the result is "-"

[jbakerstull]

If I try 

=If(GetFieldSelections([Entry Exit Provider Name])='LSS - FM Faith on 8th(52)',38,0)

result is 0