Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am having trouble with getting a list box expression to filter the way I'd like it to.
The expression I have is pasted below.
=if(Field1= 'Yes' and Field2 = 'Yes','Yes','No')
Field1 and Field2 can either have a value of Yes or No. If Yes is selected in the list box, I only want records that have a value of "Yes" for both Field1 and Field2 to display. If either or both Field1 or Field2 have a value of No, I only want the record to display when the list box has "No" selected.
This chart shows what I'm looking for.
Field1 | Field2 | Should Display when |
---|---|---|
Yes | Yes | List Box is equal to Yes |
Yes | No | List Box is equal to No |
No | Yes | List Box is equal to No |
No | No | List Box is equal to No |
Any help is much appreciated.
Well you could create a seperate field for that:
LISTBOX:
LOAD * INLINE [
listbox
Yes
No];
That way it isnt hardwired to the attributes. And then place a Select field on "listbox" on your sheet.
Don't have time to test it right now, but my guess is:
if( listbox = 'Yes',
Sum( {$<Field1={Yes},Field2={Yes}>} somenumber), // Both fields yes
Sum( {($<Field1={No}>)+($<Field2={No}>)} somenumber) // + unions selection of Field1=no and Field2=no
)
Hope this points you in the right direction,
Jeroen
Thank you. For the listbox = "Yes" part, how do I reference my listbox? Do I need to reference it by the name?
Given that NO is the controlling element of the expression, I would have thought that the formula logic should be better written thus:-
If field 1 is no then put no, if field 2 is no put no otherwise put yes.
i.e.:-
IF([field1] = 'No', 'No', if([Field2] = 'No','No', 'Yes'))
Well you could create a seperate field for that:
LISTBOX:
LOAD * INLINE [
listbox
Yes
No];
That way it isnt hardwired to the attributes. And then place a Select field on "listbox" on your sheet.
When I try using the Listbox field with the separate field, it doesn't filter the report at all, regardless of whether I pick yes or no. Do I somehow need to tie this new field to anything besides the listbox?
Thank you for your responses; I really appreciate your time.
I ended up figuring it out, so you can disregard my last question. Thank you for your direction! I used the getFieldSelections() function to determine whether Yes or No was selected, and then used that to filter my chart correctly based on the values in Field1 and Field2. Thanks again!