Number records in a gallery

[Deriooo]

Hey all,

Looking for some help with numbering records in a gallery if possible please. 

I have two galleries, records on the left gallery are selected and this adds the records into the second gallery along with a "priority" number (Deri #1, #2 etc as shown in below screenshot), using the collect and addcolumn function.

The OnSelect code on the first gallery is below. 

Collect(
        _assignHaulier,
        AddColumns(
            Filter(
                TransportPlanningHaulierGallery.AllItems,
                ThisItem.ID = ID
            ),
            "Haulier",
            Dropdown1.Selected.Value,
            "Priority",
                CountRows(
                    Filter(
                        _assignHaulier,
                        Dropdown1.Selected.Value in Haulier
                    )
                ) +1
    )
)

So each time I select a new record from the left gallery, it is added to the right gallery collection and adds 1 to the priority number, giving an incrementing number for each record, this is working as expected.

The issue I have is when I remove one of the records from the gallery on the right, when I select the next record from the left gallery this adds the incorrect priority number, causing a duplicate in numbers, what is shown below. 

So what I'm looking for help with, how can I alter the above code to iterate through all the priority numbers and use the lowest available number, or add 1 to the total, in the example above I would want the record to be assigned a priority of 3 not 4, causing a duplicate for priority 4.

I hope I've explained this well enough to understand my issue here, I'd appreciate any help on this and if there is any other info you need please let me know.

Thanks

Deri

[RandyHayes]

@Deriooo 

Please consider changing your Formula to the following:

Collect(
        _assignHaulier,
        AddColumns(
            Filter(
                TransportPlanningHaulierGallery.AllItems,
                ThisItem.ID = ID
            ),
            "Haulier",
            Dropdown1.Selected.Value,
            "Priority",
                Max(
                    Filter(
                        _assignHaulier,
                        Dropdown1.Selected.Value in Haulier
                    ),
                    Haulier
                ) +1
    )
)

If you base it on CountRows, then your numbers will be off.  IN the above, it is based on the Max number.

I hope this is helpful for you.

[Deriooo]

Hi Randy,

Thanks for the suggestion, I've updated the formula to use Max() instead of CountRows, but each record that is added is now being set to Priority #1 and the numbers aren't incrementing. As shown below.

Also with using the max function as suggested, wouldn't this always find the maximum value and add 1 to it, which would miss out previous numbers that haven't been used? For example if I have Priority 1, 2, 4 and 5, I would want the next record to use Priority 3 rather than 6, so no numbers are missed/wasted.

Thank you

Deri

[PowerRanger]

@Deriooo 

This is because the remove function needs to be adjusted also to re-calculate everything on delete. Use this for the remove item function:

Remove(
    _assignHaulier,
    ThisItem
);
ForAll(
    Sequence(CountRows(_assignHaulier)),
    With(
        Last(
            FirstN(
                _assignHaulier,
                ThisRecord.Value
            )
        ) As originalRecord,
        Collect(
            _assignHaulierHELPER,
            {
                Driver: originalRecord.Driver,
                Haulier: originalRecord.Haulier,
                ID: originalRecord.ID,
                Priority: CountRows(
                    Filter(
                        _assignHaulierHELPER,
                        Dropdown1.Selected.Value in Haulier
                    )
                ) + 1
            }
        )
    )
);
ClearCollect(_assignHaulier,_assignHaulierHELPER);
Clear(_assignHaulierHELPER)

For the Add function use your origional CountRows logic as you posted above.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[RandyHayes]

@PowerRanger 

Of course the one issue with that formula is that the ForAll is used incorrectly and is backward!!

So, @Deriooo if you are sticking with using a collection for this, then your formula would become more like this on the Remove action:

Remove(_assignHaulier, ThisItem);

With({_items: Sort(_assignHaulier, Priority)},

    ClearCollect(_assignHaulier, 
        ForAll(Sequence(CountRows(_items)),
           Patch(Last(FirstN(_items, Value)), {Priority: Value})
        )
    )
)

This will renumber the priorities based on the existing priorities.  

Using the ForAll backward just makes things more complex!

As for the Adding to the list that we worked on in the last reply...can you explain more about what you are doing in that? Whey are you adding a full table on each collect rather than just adding a new item/record to it?

[Deriooo]

Thanks both for the suggestions, so I've added the formula to the Remove function on the second gallery, without the ForAll. This now reorders the priority numbers when removing one of the records, I could probably use this going forward but ideally what I wanted to achieve was: 

If I had 4 records in the right hand gallery, with each record having a priority of #1, #2, #3 or #4, if I were to remove record number 3, this would leave 3 records each with a priority of #1. #2 and #4.

When I then select a new record from the left hand gallery to be added into the right priority gallery I would like this newly selected record to fall into position number 3 with a priority of #3, rather than be added to the highest priority number.

In regards to the adding to the list, for context, this is a list of records that need to be assigned a priority of delivery, a user will select records from this gallery in an order that they want the deliveries to be sent out, the right hand gallery then shows the finished list of deliveries in their correct priority order.

As you can see in the top right hand corner of the screenshots I've sent there is a "driver" dropdown, so different orders will be assigned to different drivers, so again, there will be multiple records that will have the same number priority but with a different driver assigned as shown below. 

To be honest, I found the formula from tutorials online, so I am adding a full table on each collect as that was the way that was recommended, if there is a better approach to achieve this then I am open to changing this.

Thanks for your help so far

Deri

[RandyHayes · Accepted solution]

@Deriooo 

Yes, the tables in the collection is not really providing you anything. 

If your right hand gallery is there to show the collection of items for individual drivers, then that would also be your filter on the Items property of the Gallery.  Filter(_assignHaulier, Haulier=Dropdown1.Selected.Value)

If you are looking for what you mention - to be able to remove priority 3 and then when adding a new one, setting it to 3, then it changes some formulas.

The remove X icon OnSelect would be:

RemoveIf(_assignHaulier, Haulier=ThisItem.Haulier && Priority=ThisItem.Priority)

(although using just Remove and ThisItem works in some cased, it is always BEST to be specific on what you want to delete/remove).

Your formula to add to the collection would be:

With({_nextPri:
    LookUp(
        ForAll(Sequence(Max(_assignHaulier, Priority)+1),
            {Priority: If(Value in _assignHaulier.Priority, 0, Value)}
        ),
        Priority>0
    )
   },

    Collect(_assignHaulier,
        Patch(LookUp(TransportPlanningHaulierGallery.AllItems, ID = ThisItem.ID),
            {Haulier: Dropdown1.Selected.Value,
             Priority: _nextPri
            }
        )
    )
)

The above will assign a priority to be the next non-consecutive priority.  So, if there is a priority of 1, 2 and 4, then the new one will be 3.  If there is 1, 2, 3, and 4, then the new one will be 5.

[PowerRanger]

@RandyHayes @Deriooo 

I haven't checked you last post @RandyHayes yet but wanted to share my code here also. If I got it right the Logic of the right Gallery is Like this:

TOUR,  DRIVER,   DRIVER SPECIFIC PRIO

Tour 1, Randy , Prio 1

Tour 2, Randy, Prio 2

Tour 3, Randy, Prio 3

Tour x, Randy, Prio 4

Tour 4, Lutz, Prio 1

Tour 5, Lutz, Prio 2

Deleting the second row (bold) the result should be. So Tour 3 for Randy is now Prio 2 and Tour X Prio 3...

Tour 1, Randy , Prio 1

<deleted>

Tour 3, Randy, Prio 2

Tour x, Randy, Prio 3

Tour 4, Lutz, Prio 1

Tour 5, Lutz, Prio 2

So on each delete we have to re-calculate the Priority

My idea for deleting (based on your code @RandyHayes ) is now:

Remove(
    _assignHaulier,
    ThisItem
);

ClearCollect(
    _assignHaulier,
    ForAll(
        Sequence(CountRows(_assignHaulier)),
        With(
            Last(
                FirstN(
                    _assignHaulier,
                    Value
                )
            ) As _currentRecord,
            Patch(
                _currentRecord,
                {
                    Priority: CountRows(
                        Filter(FirstN(_assignHaulier,Value),_currentRecord.Haulier in Haulier)
                    )
                }
            )
        )
    )
);

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Deriooo]

Thanks Randy, this definitely looks like what I'm trying to achieve, I've added the formula to my app but it's throwing a couple of errors, can you advise what to do here please?

Error #1

Error #2

Thank you

Deri

[RandyHayes]

@Deriooo 

The formula looks good that I provided, so if you are getting that error, then there is someplace else that this collection is getting defined (the _assignHaulier collection) so, can you provide some of the other formulas you have that collect records to that collection?