Using Variables in an If Statement

[#1]

I am trying to create a button that toggles between a selection in a field and then showing all!!

Field - APInv.PayGroup (Data - GP, Local Gov, NHS, Non-NHS, Ophthalmic, Petty Cash, UPR, Utility)

Button 1 - =if(vPayGroup = 1 , 'NHS', '') This is created in Actions > Select In Field > Search String

This button works fine and toggles between showing 'NHS' and showing all

Button 2 - I want this to toggle between showing all and then all except NHS

     I am unsure how to use this in the search string!

Any help would be much appreciated.

thanks

Andrew

[Gysbert_Wassenaar]

Try:

=if(vPayGroup = 1 , '(' & concat({1<[APInv.PayGroup] -= {'NHS'}>} distinct [APInv.PayGroup] , '|' ) & ')' )

[#3]

This a good start thank you.

Unfortunately this is missing out 'Local Gov' & 'Petty Cash'!! I have checked the data to make sure that there is data to be shown and there is!!

Any suggestions?

Andrew

[Gysbert_Wassenaar · Accepted solution]

Try:

=if(vPayGroup = 1 , '(' & concat({1<[APInv.PayGroup] -= {'NHS'}>} distinct chr(34) & [APInv.PayGroup] & chr(34) , '|' ) & ')' )

[#5]

That's great, it's now working fine.

Thanks gysbert, mucha appreciated.

Andrew