Collect duplicating items

[notj]

I'm trying to dynamically build a menu based off the status of an item, but my Collect is duplicating items. I've got a collection of menu items, and then I Collect a filtered subset of those items based off the status. 

Here's my collection of menu items:

ClearCollect(
AllActionsMenuItems,
{id: 1,order: 3,image: "",action: 1,altText: "Close Denial", actionvar:"actCloseDenial", visibility:"IsSubmitted"},
{id: 2,order: 1,image: "",action: 2,altText: "Save Draft", actionvar:"actSaveDraft", visibility:"IsDraft"},
{id: 3,order: 5,image: "",action: 3,altText: "Delete Denial", actionvar:"actDeleteDenial", visibility:"IsSubmitted IsClosed"},
{id: 4,order: 6,image: "",action: 4,altText: "UnDelete Denial", actionvar:"actUnDeleteDenial", visibility:"IsDeleted"},
{id: 5,order: 4,image: "",action: 5,altText: "UnClose Denial", actionvar:"actUnCloseDenial", visibility:"IsClosed"},
{id: 7,order: 4,image: "",action: 5,altText: "Unlock Denial", actionvar:"actUnlockDenial", visibility:"IsLocked"},
{id: 6,order: 2,image: "",action: 6,altText: "Submit Denial", actionvar:"actSubmitDenial", visibility:"IsDraft"});

Here's where I do a filtered collect based off the status of the item:

If(First(CurrentItemSelected).Status="Deleted",  Collect(CurrentItemActionsMenu, Filter(AllActionsMenuItems, "IsDeleted" in visibility)));
If(First(CurrentItemSelected).Status="Closed",  Collect(CurrentItemActionsMenu, Filter(AllActionsMenuItems, "IsClosed" in visibility)));
If(First(CurrentItemSelected).Status="Submitted",  Collect(CurrentItemActionsMenu, Filter(AllActionsMenuItems, "IsSubmitted" in visibility)));
If(First(CurrentItemSelected).Locked="yes",  Collect(CurrentItemActionsMenu, Filter(AllActionsMenuItems, "IsLocked" in visibility)));
If("Draft" in First(CurrentItemSelected).Status,  Collect(CurrentItemActionsMenu, Filter(AllActionsMenuItems, "IsDraft" in visibility)));

So you would think when my item only has one status(Submitted in the below case), I would only return options Close, Delete, and Unlock because the item is also Locked(different column, same idea). Except here's what happens:

[RandyHayes]

@notj 

Your issue is that you are collecting these items in your formula on top of the existing items.

Collect adds new rows of records.  What you want to do is to ClearCollect the items.

Please consider changing your Formula to the following:

ClearCollect(CurrentItemActionsMenu, 
    Filter(AllActionsMenuItems, 
        visibility =
            Switch(First(CurrentItemSelected).Status,
                "Deleted", "IsDeleted",
                "Closed",  "IsClosed",
                "Submitted",  "IsSubmitted",
                "yes", "IsLocked",
                "Draft", "IsDraft"
            )
    )
)    

I hope this is helpful for you. 

[notj]

I purposely have multiple collect(not ClearCollect) formulas. That doesn't explain why it's duplicating.

CloseDenial has one visibility value, IsSubmitted. I only have one Collect formula that uses IsSubmitted. CloseDenial should only be collected once.

Your formula only collects one item(whichever comes first in the list).

[notj]

I changed the name of the collection and it's working now with the formula I posted originally. This makes no sense.

[RandyHayes]

@notj 

Collect adds new rows to a collection.  If you already have "Closed Denial" in your collection, then your original formula will add a new one.

I did just notice you do have multiple items in your visibility, so I offer a revised formula:

ClearCollect(CurrentItemActionsMenu, 
    Filter(AllActionsMenuItems, 
        Switch(First(CurrentItemSelected).Status,
            "Deleted", "IsDeleted",
            "Closed",  "IsClosed",
            "Submitted",  "IsSubmitted",
            "yes", "IsLocked",
            "Draft", "IsDraft"
        ) in visibility
    )
)  

Quite honestly, I would do this without any collections.

It appears you have a gallery of buttons...I would set the Items property to:

With({_items: Table(
    {id: 1,order: 3,image: "",action: 1,altText: "Close Denial", actionvar:"actCloseDenial", visibility:"IsSubmitted"},
    {id: 2,order: 1,image: "",action: 2,altText: "Save Draft", actionvar:"actSaveDraft", visibility:"IsDraft"},
    {id: 3,order: 5,image: "",action: 3,altText: "Delete Denial", actionvar:"actDeleteDenial", visibility:"IsSubmitted IsClosed"},
    {id: 4,order: 6,image: "",action: 4,altText: "UnDelete Denial", actionvar:"actUnDeleteDenial", visibility:"IsDeleted"},
    {id: 5,order: 4,image: "",action: 5,altText: "UnClose Denial", actionvar:"actUnCloseDenial", visibility:"IsClosed"},
    {id: 7,order: 4,image: "",action: 5,altText: "Unlock Denial", actionvar:"actUnlockDenial", visibility:"IsLocked"},
    {id: 6,order: 2,image: "",action: 6,altText: "Submit Denial", actionvar:"actSubmitDenial", visibility:"IsDraft"}
   )},

    Filter(_items, 
        visibility =
            Switch(First(CurrentItemSelected).Status,
                "Deleted", "IsDeleted",
                "Closed",  "IsClosed",
                "Submitted",  "IsSubmitted",
                "yes", "IsLocked",
                "Draft", "IsDraft"
            )
    )
)    

)

And even with that, I would consider referencing the CurrentItemSelected by the control that it is in.  For example, if it is in a Gallery, then instead of First(Current...  I would simply use yourGallery.Selected.Status.

[notj]

I have the Actions Menu on 6 screens. I'm doing my best not to have to make changes in 6 locations every time I add a new option to the Actions. That's why I'm using a collection.

Additionally, I have 5 galleries where the user might select an item. Like above, doing my best to not have to duplicate changes over and over every time I modify something.

Your modified doesn't return any items but I'm not sure why.

Formula:

ClearCollect(CurrentItemActionsMenu, 
    Filter(AllActionsMenuItems, 
        Switch(First(CurrentItemSelected).Status,
            "Deleted", "IsDeleted",
            "Closed",  "IsClosed",
            "Submitted",  "IsSubmitted",
            "Draft", "IsDraft"
        ) in visibility
    )
)  ;

Status value: 

Results:

[RandyHayes]

@notj 

So, based on that description, I would put the following in your OnStart of the App (so it is in one place and easy to get to if you need to change):

Set(glbActionsMenu, Table(
    {id: 1,order: 3,image: "",action: 1,altText: "Close Denial", actionvar:"actCloseDenial", visibility:"IsSubmitted"},
    {id: 2,order: 1,image: "",action: 2,altText: "Save Draft", actionvar:"actSaveDraft", visibility:"IsDraft"},
    {id: 3,order: 5,image: "",action: 3,altText: "Delete Denial", actionvar:"actDeleteDenial", visibility:"IsSubmitted IsClosed"},
    {id: 4,order: 6,image: "",action: 4,altText: "UnDelete Denial", actionvar:"actUnDeleteDenial", visibility:"IsDeleted"},
    {id: 5,order: 4,image: "",action: 5,altText: "UnClose Denial", actionvar:"actUnCloseDenial", visibility:"IsClosed"},
    {id: 7,order: 4,image: "",action: 5,altText: "Unlock Denial", actionvar:"actUnlockDenial", visibility:"IsLocked"},
    {id: 6,order: 2,image: "",action: 6,altText: "Submit Denial", actionvar:"actSubmitDenial", visibility:"IsDraft"}
   )
)

You don't need the overhead of a collection for static data.

Then in your menu gallery Items property:

Filter(glbActionsMenu, 
    visibility =
    Switch(First(CurrentItemSelected).Status,
        "Deleted", "IsDeleted",
        "Closed",  "IsClosed",
        "Submitted",  "IsSubmitted",
        "yes", "IsLocked",
        "Draft", "IsDraft"
     )
)

[notj]

This is still only pulling the first item. Also, IsLocked comes from a separate field(not Status) named Locked. How would I expand this to evaluate that without resulting in duplicate results?

Could you explain the overhead of the collection versus using the table like in your most recent example? I understood them to be basically the same thing.

[RandyHayes · Accepted solution]

@notj 

Sorry I missed that distinction in the original formula.  I was also looking at the last If statement of your original formula and noticed that you were looking a "Draft" in status.  This made me wonder...does your Status contain multiple values?  Perhaps this would be more flexible for you:

With({_stat: 
    Switch(First(CurrentItemSelected).Status,
        "Deleted", "IsDeleted",
        "Closed",  "IsClosed",
        "Submitted",  "IsSubmitted",
        "yes", "IsLocked",
        "Draft", "IsDraft"
    )
    },

    Filter(glbActionMenu, 
        _stat in visibility ||
       ((First(CurrentItemSelected).Locked = "yes") && "IsLocked" in visibility)
    )
)

As for collections - they are in memory databases.  They are tables.  They have only one ability over a table...the ability to add or remove rows.  In contrast, a variable can be a table also.  It just doesn't have the ability to add or remove rows.  If you are working with static data such as you are, the I would prefer a variable over a collection as the collection has the additional overhead (uses a different and larger set of behind-the-scenes functionality) that is not needed if you are never going to add or remove rows from it.

Collect(someCol, "A Value")  - turns into an in memory database that has a single column table with a single value.

Set(someVar, ["A Value"]) - turns into a variable that is a single column table with a single value.

Set(someVar, Table({Value:"A Value"})) - is equivalent to the above.

If the above is static data and not going to need new rows, then a variable is "lighter-weight" for the overall app.

To add on the above, Collect(someCol, "B Value") would add an additional row to the original table with the value specified.

With a Variable, you would not have the ability (easily) to add a new row.

Now, in your case, the real end goal is that you want the menu items to be equal across all screens and you want the menu to change based on a selected item (and again, across all screens) - at least that is what I have understood from your post.  So, the variable will give you that and the Filter formula on the Items should also give you that ability.  I have another trick (which I call dynamic variables) that would reduce the formula down to just one formula, but I've left that out of these responses as to not confuse things.

[notj]

Status is just a text value and will only ever have one value to it. That's why the Locked is a separate column(named Locked). It can be locked regardless of the Status so it's broken out to a separate column. Locked won't be in the Status. With that said, I don't believe the above works 100% as it leaves out the Locked.

Understood on the table vs collection thing. Thanks for clearing that up.