cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
EYOST
Level: Powered On

how write formula for separated/break IF()?

Hi ,

 

May I know how to write a formula inside Gallery Item property something like below?
If(IsBlank(CB_1.SelectedItems),All Records in the table,Field1 in CB_1.SelectedItems.F1) ;
If(IsBlank(CB_2.SelectedItems),All Records in the table,Field2 in CB_2.SelectedItems.F2) ;
If(IsBlank(CB_3.SelectedItems),All Records in the table,Field3 in CB_3.SelectedItems.F3) ;
If(IsBlank(CB_4.SelectedItems),All Records in the table,Field4 in CB_4.SelectedItems.F4) ;

 

Do not want to use IF(..) && IF(...) and  IF(..) || IF(...) pattern.

So that the gallery only show the results of combo boxes which is selected.

Which means, I ONLY want to filter the conditions which Combo boxes have SELECTED items, show the result in combo box/ gallery/list.

 

Thank you! 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: how write formula for separated/break IF()?

Hi @EYOST ,

Do you want to filter your Gallery Items based on the selected value within the ComboBoxes?

 

I have made a test on my side, please take a try with the following workaround:

Set the Items property of your Gallery to following formula:

Filter(
         'YourDataSource',
          If(
             IsBlank(CB_1.Selected.Value),
             true,
             Field1 in CB_1.SelectedItems.F1
          ),
If(
IsBlank(CB_2.Selected.Value),
true,
Field2 in CB_2.SelectedItems.F2
),
If(
IsBlank(CB_3.Selected.Value),
true,
Field3 in CB_3.SelectedItems.F3
),
If(
IsBlank(CB_4.Selected.Value),
true,
Field4 in CB_4.SelectedItems.F4
) )

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Super User
Super User

Re: how write formula for separated/break IF()?

A couple of questions to help you better --
In the is blank portion of your example you reference what appears to be 4 different comboboxes  (CB1, CB2, CB3, CB4)  Is this correct?  If so, are they mutually exclusive?  In other words will only 1 of these 4 be true?  

 

In the false portion of your example, you only reference one of the 4 comboboxes, but 4 different fields associated with the datasource for that combobox.  Is this correct?  

EYOST
Level: Powered On

Re: how write formula for separated/break IF()?

@LRVinNC 
Very sorry for the confusion, I made the changes on my question(in Magenta).

There are 4 combo boxes, different column for each combo box.

I would like to do a flexible search. For example there are 4 combo boxes,  users don't have to select all the combo box in order to show the result in a Gallery, they can select one or two or more or all combo boxes then the Gallery will show what they selected.

 

Thank you.

Community Support Team
Community Support Team

Re: how write formula for separated/break IF()?

Hi @EYOST ,

Do you want to filter your Gallery Items based on the selected value within the ComboBoxes?

 

I have made a test on my side, please take a try with the following workaround:

Set the Items property of your Gallery to following formula:

Filter(
         'YourDataSource',
          If(
             IsBlank(CB_1.Selected.Value),
             true,
             Field1 in CB_1.SelectedItems.F1
          ),
If(
IsBlank(CB_2.Selected.Value),
true,
Field2 in CB_2.SelectedItems.F2
),
If(
IsBlank(CB_3.Selected.Value),
true,
Field3 in CB_3.SelectedItems.F3
),
If(
IsBlank(CB_4.Selected.Value),
true,
Field4 in CB_4.SelectedItems.F4
) )

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

EYOST
Level: Powered On

Re: how write formula for separated/break IF()?

@v-xida-msft 
Hi Kris,

This is equal to IF() && IF() && IF(), right?

If yes, it will run one time to check IF conditions only.

I wish to have break IF because IF checking stop when it hit one of the conditions, then other input are not checked when there are multiple input fields(or dropdown or comboboxes, as long as user input fields).

Do you have any idea on this?

 

Thank you~~

Community Support Team
Community Support Team

Re: how write formula for separated/break IF()?

Hi @EYOST ,

Do you want to create a break If condition within your Filter formula?

Have you taken a try with the above solution I provided?

 

If you want to create a break If condition within your Filter formula, I afraid that there is no way to achieve your needs in PowerApps currently.

 

Based on the needs that you mentioned, I think the above solution I provided could achieve your needs. Within above Filter formula, it would check if there is an option has been selected for each ComboBox, if the ComboBox has not selected an option (the ComboBox is empty), the inside If function would return true, in other words, this ComboBox would not be used to filter your Gallery Items.

 

Please take a try with above Filter formula I provided, then check if the issue is solved:

Set the Items property of your Gallery to following formula:

Filter(
         'YourDataSource',
          If(
             IsBlank(CB_1.Selected.Value),                 /* <-- If the CB_1 has not selected an option (CB_1 is empty), this If function would return true, in other words, the CB_1 would not be used to filter your Gallery items */
             true,
             Field1 in CB_1.SelectedItems.F1
          ),
If(
IsBlank(CB_2.Selected.Value),
true,
Field2 in CB_2.SelectedItems.F2
),
If(
IsBlank(CB_3.Selected.Value),
true,
Field3 in CB_3.SelectedItems.F3
),
If(
IsBlank(CB_4.Selected.Value),
true,
Field4 in CB_4.SelectedItems.F4
) )

Note: The above Filter formula is not equal to IF() && IF() && IF() that you mentioned.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: how write formula for separated/break IF()?

Hi @EYOST ,

Have you taken a try with above solution I provided?

 

Based on the needs that you mentioned, I think it could achieve your needs. If you have solved your problem, please consider go ahead to click "Accept as Solution" to identify this thread as Solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
EYOST
Level: Powered On

Re: how write formula for separated/break IF()?

@v-xida-msft Thank you very much! I tried with a dummy table in Excel, it looks working fine.

 

Maybe my sql server database have too much rows and it show incorrect and weird.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,906)