How to set default input box with last SharePoint entry that matches ID column?

[Anonymous]

I want the default value in my EOY Forecast input box to be the most recent EOY Forecast value in my SharePoint list. If there is no EOY Forecast for that ID column in the SharePoint list, then I want the input box to be blank. What formula for the input box default property will achieve this outcome? I would really appreciate any help!

My input box default property formula

LookUp('Variance SharePoint', Index = ThisItem.Index, 'EOY Forecast')

This returns the EOY Forecast for that index column but the indices are going to change each month which is why i am using ID column of concatenated text that does not change each month. 

[WarrenBelz · Accepted solution]

Hi @Anonymous ,

If you want the latest matching item

LookUp(
   Sort(
      'Variance SharePoint', 
      ID,
      SortOrder.Descending
   ),
   Index = ThisItem.Index
).'EOY Forecast'

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Anonymous]

Your solution worked @WarrenBelz , but it replaces the last index and month with new index and current month. Is it possible to create a new SharePoint item instead of replacing the most recent item? That way I can view past months but keep the same EOY forecast value. 

[mmollet]

Are you using this in a patch statement? If so can you share the statement code please?

Just guessing here but if this is being used as the 2nd parameter of the Patch function then this is why it is updating rather than creating a new row. If you pass the Patch Function an object it will try to find that object in the SP List and if it does it will edit the fields listed in the Patch Statement's 3rd parameter inside the {}. In order to avoid this you can just do the following:

UpdateContext({lastItem: **Warren's Code Here**}) -> this gives you an object called lastItem with all the properties/fields.

Now you can just use Patch(SPList, {** make your item here **}) -> you can use Name: lastItem.Name for example

This will create a new row rather than editing an existing row while also giving you access to all the values from the previously submitted entry in that list.

[Anonymous]

Enter Button OnSelect Property

Select(Parent);
If(IsBlank(LookUp('Variance SharePoint', 'Test ID' = ThisItem.'Test ID')),
Patch('Variance SharePoint', Defaults('Variance SharePoint'),
{'EOY Forecast': Value('EOY Forecast Input'.Text),
Label: ThisItem.Label,
Index: ThisItem.Index,
'Test ID': ThisItem.'Test ID'}),
Patch('Variance SharePoint', LookUp('Variance SharePoint', 'Test ID' = ThisItem.'Test ID'),
{'EOY Forecast': Value('EOY Forecast Input'.Text),
Label: ThisItem.Label,
Index: ThisItem.Index,
'Test ID': ThisItem.'Test ID'}); 

EOY Forecast Input Box Default Property

LookUp(
   Sort(
      'Variance SharePoint', 
      ID,
      SortOrder.Descending
   ),
   Index = ThisItem.Index
).'EOY Forecast'

How can I edit my Enter Button onSelect formula to include updateContext bc the EOY Forecast Input Box formula does not have updateContext function?