0

I wanted to compare two sharepoint lists. one is forecasted expense against each resource for a particular month and other sharepoint list is the actual expenses that has come. The two sharepoint lists have same fields and column types.here, I want to check if there is any expense that has come which was not forecasted for the resource for that month. Also, want to show the Actual, forecast and the difference in Powerapp. Does anyone has any idea how can this be done? any kind of assistance is appreciated.

Is this how the flow would look like to update the 3rd list? Do I need to add any filter Query in get items?enter image description here

Improve this question
6
  • Looks like you need a third list to store the Actual, Forcast, and the Difference and then PowerApps can show data from this third list. Using MS FLow/Power Automate, you can compare values in two list and update then add/update the 3rd list. What is the common data element linking the forecast and actual expense lists?
    – Matiur Rahman
    Dec 8, 2020 at 16:12
  • @MatiurRahman Thank you for your response. :) Resource Name, Month and Year should be the common fields between the two lists.Is this how the flow would look like? Attached the flow screenshot in the mail question.
    – HRY
    Dec 8, 2020 at 19:26
  • You're getting closer. In the [Update], which ID you're using. Also, I don't see any logic to determine the difference between the forecast and actual before updating the third list. If you can wait, I can create an example later today
    – Matiur Rahman
    Dec 8, 2020 at 20:15
  • Thank you @MatiurRahman. Yes, there is one more thing - a resource can have more than one expense line item for the same month. So, I wanted to have the total actual expense of the resource for the month compared to the total forecasted expense of the resource for the month.
    – HRY
    Dec 9, 2020 at 5:28
  • I think, that can be handled. But first, let see whether the sample I just posted will work for you. It it works then we will look into the additional condition. It's very late in the night here, so I'm logging off for now. Will check back in the morning.
    – Matiur Rahman
    Dec 9, 2020 at 5:49

1 Answer 1

Reset to default
0

Since the two lists are pretty similar, for my test example, I'm adding a Number column called [ForcastMerged] and a Calculated column called [Difference] in ExpenseActual List.

The formula(which can be adjusted)for [Difference] is:

=IF(AND(ActualAmount<>0,ForecastMerged<>0),ActualAmount-ForecastMerged,0)

See the MS Flow screenshots and explanation below

enter image description here

Expression for Set Variables

Extracted from the current item of the [Apply to each]
varYear:
int(items('Apply_to_each')?['Year'])

varMonth:
int(items('Apply_to_each')?['Month'])

varForecastAmount:
items('Apply_to_each')?['ForecastAmount']

ID being extracted from [Get Items - Actual Expense].
varActualExpenseID:
if(equals(length(outputs('Get_items_-_Actual_Expense')?['body/value']), 0), 0,first(outputs('Get_items_-_Actual_Expense')?['body/value'])['ID'])

Detail 1

enter image description here

Detail 2

enter image description here

ExpenseForcast and ExpenseActual Lists

enter image description here

enter image description here

Should you have any questions, let me know.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.