ClearCollect with nested IF Not working as expected

[slagbr2]

I have an issue when working with nested IFs in side ClearCollect:

When I use this alone, I get the expected returned columns of ID and filed_0...

ClearCollect(
    collTest,
    If(
        Sum(
            collSelectedMGMAPercentile,
            Value
        ) = 30,
        ShowColumns(
            collMGMAEncounterReferenceDetail,
            "ID",
            "field_0"
        )
    )
)

BUT when I additional nested IF statements, it only returns the ID column, and none of the other columns will display.

ClearCollect(
    collTest,
    If(
        Sum(
            collSelectedMGMAPercentile,
            Value
        ) = 30,
        ShowColumns(
            collMGMAEncounterReferenceDetail,
            "ID",
            "field_0"
        ),
        If(
            Sum(
                collSelectedMGMAPercentile,
                Value
            ) = 10,
            ShowColumns(
                collMGMAEncounterReferenceDetail,
                "ID",
                "field_2"
            )
        )
    )
)

Additional Info: 

collMGMAEncounterReferenceDetail is coming from a SharePoint List.

collSelectedMGMAPercentile is a selected value from a combobox.

Any help is appreciated.

[SpongYe]

Hi @slagbr2 

Please try this:

ClearCollect(
    collTest,
    If(
        Sum(
            collSelectedMGMAPercentile,
            Value
        ) = 30,
        ShowColumns(
            collMGMAEncounterReferenceDetail,
            "ID",
            "field_0"
        ),
        Sum(
            collSelectedMGMAPercentile,
            Value
        ) = 10,
       ShowColumns(
           collMGMAEncounterReferenceDetail,
           "ID",
           "field_2"
        )
    )
)

------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[slagbr2]

Hey there, thanks for the suggestion. I tried that as well and got the same result. Only displaying the ID column.

[slagbr2]

Hey there, thanks for the suggestion. I tried that as well and got the same result. Only displaying the ID column.

[SpongYe]

Maybe try it with the Switch() function:

ClearCollect(
    collTest,
Switch(
    Sum(collSelectedMGMAPercentile, Value),
    30,
    ShowColumns(
        collMGMAEncounterReferenceDetail,
        "ID",
        "field_0"
    ),
    10,
    ShowColumns(
        collMGMAEncounterReferenceDetail,
        "ID",
        "field_2"
    )
)
)

------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[slagbr2]

No change, unfortunately.

[SpongYe]

Can you give me some more information so I can reproduce the problem? e.g. screenshots, formulas

[slagbr2]

collMGMAEncounterReferenceDetail reference table

collSelectedMGMAPercentile  reference table

Result:

[SpongYe]

Hi @slagbr2 

I got it reproduce and I have a conclusion why it is not working.

The ShowColumns function includes columns of a table and drops all other columns. You can use ShowColumns to create a single-column table from a multi-column table. ShowColumns includes columns.

In our code above we are adding "field_0" to the first check and in the second check "field_2".That has a conflict with each other. If you include bot field in both checks it worked fine. 

What I would suggest to split in 2 collection as following:

If(
    Sum(collSelectedMGMAPercentile, Value) = 30,
    ClearCollect(testcol_0,
        ShowColumns(
            collMGMAEncounterReferenceDetail,
            "ID",
            "field_0"
        )
    ),   
    Sum(collSelectedMGMAPercentile, Value) = 10,
    ClearCollect(testcol_1,
        ShowColumns(
            collMGMAEncounterReferenceDetail,
            "ID",
            "field_2"
        )
    )
)

------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[slagbr2]

The issues is I need the results in a single collection regardless of the column… so this would not work…

the odd thing is that, as mention in the original post, when there’s just a single IF both columns appear. 

it is also worth mentioning that if I take out the ID column, and just have the “field_x”, the table returns visually empty, BUT the collection count shows the total number or rows.