Updating a record

[Anonymous]

Hello everyone,

right now I am using this formula: 

Patch('Country Rollout Overview',LookUp('Country Rollout Overview', Title = CountryName.Title), {'Last Update': First(Filter('Update History',Title = CountryName.Title && ID = Max(ID),'Update Content')).Title});

The thought was to update a record by clicking a button. The issue here is that I need to filter the update history not only by the highest ID, but also by the name of the country. Therefore I looked up various ways to achieve that. The one I found was using this First(Filter(...)).Field structure and it appeared to be working fine for who ever was using it. But now when I try this, it does not work at all.... I don´t even get anything updated at all. Any Idea why that is?

Thanks in advance!

[Prabhakar_S]

Hi @Anonymous ,

Please try this formula,

If(
IsBlank(Filter('Update History', Title = CountryName.Title && ID = Max('Update History'.ID))),
Notify("No matching record found", NotificationType.Error),
Patch('Country Rollout Overview', LookUp('Country Rollout Overview', Title = CountryName.Title), {'Last Update': First(Filter('Update History', Title = CountryName.Title && ID = Max('Update History'.ID))).Title})
)

Thanks!!!

Please consider marking my response as the accepted solution if it successfully resolves your concern. If you found the information beneficial in other aspects, kindly express your appreciation by giving it a thumbs-up.

[Anonymous]

@Prabhakar_S thank you for your quick response!

Sadly I get an error with this formula. It tells me that at the highlighted part the formula expects a number:

If(
IsBlank(Filter('Update History', Title = CountryName.Title && ID = Max('Update History'.ID))),
Notify("No matching record found", NotificationType.Error),
Patch('Country Rollout Overview', LookUp('Country Rollout Overview', Title = CountryName.Title), {'Last Update': First(Filter('Update History', Title = CountryName.Title && ID = Max('Update History'.ID))).Title})
)

Does Max(ID) not work because it does not know what ID I´m talking about?  It looks like ID does not produce a number but an ID (don´t know if an ID is seen as a number or something different in Powerapps), since the error says a number is expected.

[Prabhakar_S]

Hi @Anonymous ,

The error you're encountering in PowerApps is likely due to the 'ID' field in your 'Update History' table being treated as a non-numeric type. In PowerApps, the 'ID' field is often an auto-generated unique identifier, which may not be recognized as a numeric value by default.

To resolve this issue, you can explicitly convert the 'ID' field to a numeric type using the Value function before using it in the Max function. I have a couple of approaches to resolve this please give a try on the modified formula,

If(
IsBlank(Filter('Update History', Title = CountryName.Title && Value(ID) = Max('Update History', Value(ID)).ID)),
Notify("No matching record found", NotificationType.Error),
Patch('Country Rollout Overview', LookUp('Country Rollout Overview', Title = CountryName.Title), {'Last Update': First(Filter('Update History', Title = CountryName.Title && Value(ID) = Max('Update History', Value(ID)).ID)).Title})
)

or

If(
IsBlank(Filter('Update History', Title = CountryName.Title && Value(ID) = Max(Filter('Update History', Title = CountryName.Title, ID)).ID)),
Notify("No matching record found", NotificationType.Error),
Patch('Country Rollout Overview', LookUp('Country Rollout Overview', Title = CountryName.Title), {'Last Update': First(Filter('Update History', Title = CountryName.Title && Value(ID) = Max(Filter('Update History', Title = CountryName.Title, ID)).ID)).Title})
)

Thanks!!!

Please consider marking my response as the accepted solution if it successfully resolves your concern. If you found the information beneficial in other aspects, kindly express your appreciation by giving it a thumbs-up.

[Anonymous]

@Prabhakar_S  Yes I already thought that might be a reason... but with the first formula you sent me I run into another problem:

If(
IsBlank(Filter('Update History', Title = CountryName.Title && Value(ID) = Max('Update History', Value(ID)).ID)),
Notify("No matching record found", NotificationType.Error),
Patch('Country Rollout Overview', LookUp('Country Rollout Overview', Title = CountryName.Title), {'Last Update': First(Filter('Update History', Title = CountryName.Title && Value(ID) = Max('Update History', Value(ID)).ID)).Title})
)
First the = returns an error code that says incompatible types for comparison, these types can´t be compared: Number, Error. Apparently the first one is a number, but the second an error? 

The second problem is once again the .ID, here it tells me invalid use of .?

The second formula seems to return similar problems. The = once again produces an error of incompatible types for comparison, these types can´t be compared: Number, Error. and the part after that with the Max(Filter(..)).ID Is expecting a number....

It seems to be kind of difficult but I am very thankful that you are trying to help! Let´s figure this out together. Why does your workaround not function as it should?

[Prabhakar_S]

Hi  @Anonymous , 

It seems there might be some limitations or quirks related to how the 'ID' field is treated in your specific SharePoint environment. Let's try a different approach that might work more reliably. We can create a separate variable to store the maximum ID and then use that variable in your formula. Give this a try:

Set(
MaxID,
Max(Filter('Update History', Title = CountryName.Title).ID)
);

If(
IsBlank(Filter('Update History', Title = CountryName.Title && ID = MaxID)),
Notify("No matching record found", NotificationType.Error),
Patch('Country Rollout Overview', LookUp('Country Rollout Overview', Title = CountryName.Title), {'Last Update': First(Filter('Update History', Title = CountryName.Title && ID = MaxID)).Title})
)

Thanks!!!

Please consider marking my response as the accepted solution if it successfully resolves your concern. If you found the information beneficial in other aspects, kindly express your appreciation by giving it a thumbs-up.

[Anonymous]

Set(
MaxID,
Max(Filter('Update History', Title = CountryName.Title).ID)
);

If(
IsBlank(Filter('Update History', Title = CountryName.Title && ID = MaxID)),
Notify("No matching record found", NotificationType.Error),
Patch('Country Rollout Overview', LookUp('Country Rollout Overview', Title = CountryName.Title), {'Last Update': First(Filter('Update History', Title = CountryName.Title && ID = MaxID)).Title})
)

@Prabhakar_S 

The highlighted .ID is producing an error again. It is saying that a number is expected for the max() function....

Is there a way to implement a column in my sharepoint list, that stores the ID as a number? Maybe that´s a good work around?

[LaurensM]

Hi @Anonymous,

Do I understand it correctly that you would like to add the Title of the last created record (matching a specific country) to the 'Last Update' column? The code below expects 'Last Update' to be of type Text:

With(
    {
        wLastTitle: First(
            Sort(
                Filter(
                    'Update History',
                    Title = CountryName.Title
                ),
                Created,
                SortOrder.Descending
            )
        ).Title
    },
    Patch(
        'Country Rollout Overview',
        LookUp('Country Rollout Overview', Title = CountryName.Title), 
        {
            'Last Update': wLastTitle
        }
    )
)

I am a bit confused on the 'Title = CountryName.Title' filter/lookup condition. Does CountryName refer to a variable within your application?

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[Anonymous]

@LaurensM Thanks for answering so quickly! 

CountryName.Title refers to a variable that is storing the name of the currently selected country. By using this I can display/edit information for the country the user is currently working on.

So my goal is to update one specific entry of a record. There are two lists that I am using: One to store all the updates that have been created and one where general information is stored.  Both lists store this information per country, that means that the update list is storing many different updates for more than only one country. Now I tried to get the last created entry of an update for one particular country, but I need to specify it. I need to search for the last created update for one particular country. That´s why I tried filtering for highest ID and the name of the country.

The information I then get from the updates list should be a column containing the title of the update.

Does this explain it better?

[Anonymous · Accepted solution]

@LaurensM @Prabhakar_S 

With(
{
wLastTitle: First(Sort(Filter('Update History',Title = CountryName.Title),ID,SortOrder.Descending)).'Update Content'
},

Patch(
'Country Rollout Overview',
LookUp('Country Rollout Overview', Title = CountryName.Title),
{
'Last Update': wLastTitle
}
)
);

I got it working out in the end, I think I had the sort and filter functions twisted.