SUMMING TOTALS IN NEW LIST HELP!!!

[JamieT1]

Okay i amended the filter array and im now getting my sum totals however the sum totals are the same totals for each date range.

THIS IS MY MAIN LIST

THIS IS MY SUMMARY LIST

@and(Equals(item()?['Date'], formatDateTime(items('Apply_to_each')?['START'],'dd-MM-yyyy')),Equals(item()?['Date'], formatDateTime(items('Apply_to_each')?['FINISH'],'dd-MM-yyyy')))

Any guidance will be appreciated.

Hi,

I'm creating a flow with my SharePoint list and I'm getting this error,

'The template language function 'greaterOrEquals' expects two parameter of matching types. The function was invoked with values of type 'Null' and 'String' that do not match.'.

Looking at the raw outputs it says null next to department, job title and a couple others however I don't have these fields in my list so not sure why its searching for those values.

Any ideas?

[ManishSolanki]

Hi @JamieT1 

Could you pls share the query used in Filter array action?

[JamieT1]

Hi ManishSolanki,

@And(greaterOrEquals(item()?['Date'], formatDateTime(items('Apply_to_each')?['START'],'yyyy-MM-dd')), lessOrEquals(item()?['Date'], formatDateTime(items('Apply_to_each')?['FINISH'],'yyyy-MM-dd')))

[ManishSolanki · Accepted solution]

Hi @JamieT1 

In last failed instance, pls check the field names in the input of "Filter array" action. If you find any field name to be different in the query, then correct its name:

@And(greaterOrEquals(item()?['Date'], formatDateTime(items('Apply_to_each')?['START'],'yyyy-MM-dd')), lessOrEquals(item()?['Date'], formatDateTime(items('Apply_to_each')?['FINISH'],'yyyy-MM-dd')))

You need to verify the names of the fields which are marked as bold in red color.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[wyattdave]

Have you tried using XPATH

I've written more about it here (section 3) https://dev.to/wyattdave/top-5-ways-to-optimize-your-flows-3adf

In a nutshell you select the field you want, use a compose to add a root node to the json and then you can use one expression to convert it to XML and sum up all of that field

[JamieT1]

Thanks, I found that the Date needed to be START and FINISH however this is only giving me a total sum of all the dates in the PRE-PRODUCTION list. What i want is if a START (Column) date in PRE-PRODUCTION list is the same START (Column) date in the PRE-PRODUCTION TOTALS list  then only sum those items with that date.

Hopefully i am explaining this right.

[wyattdave]

I see what you mean, you can use the stringfy trick. Basically we are going to convert the compare list to a string so we can then compare it once against each item in a filter.

To make the string smaller and to stop accidental matches with other columns we use a select to get the one column (in your case the PRE-PRODUCTION TOTALS date column) to compare.

Next we use a filter to filter your PRE-PRODUCTION list, with the condition matching any date that is contained by the string of the PRE-PRODUCTION TOTALS Select.

string(body('Select_Pre_prod_sum')) contains item()['Date'] 

Then you can use the XPATH trick to sum the filtered array.

Another way to do this is an office script, I could spin one up for you but I would recommend trying above approach as that keeps it all in Power Automate so a lot easier to maintain.

[JamieT1]

Thanks i will give this a try, Do you mean get rid of my original filter array and replace with this 1?

On the get pre prod sum map example what is the 'd' for?

After the filter array do i do another select, Json and Xpath then an update item?

Sorry for all the questions just this has really had me stuck!

[wyattdave]

That's ok, my explanations probably aren't the best lol.

The d is just a temp value and can be anything, i just used d for date and to keep it short to speed up flow.

That's right replace your filter with this.

The idea is instead of looping over list 1 and the looping over lest 2, we turn list 2 into one big string and compare it with list 1.

So if we had 2 lists with values

1,2,3,4

and

3,4,5,6

Instead of

Does 1=3

Does 1=4

Does 1=5

Does 1=6

Does 2=3

Does 2=4

Etc

We do

Does "3456" contain 1

Does "3456" contain 2

Does "3456" contain 3

That way instead of doing 4*4 iterations we do 4. And replacing the loop with filter means it's even less .

If you want I could make you a demo to show you 

[JamieT1]

Hi davedidisco,

If you have time I would really appreciate a demo as I've been stuck on this for well over a week now.

Why can't things in life just be easy... lol

[JamieT1]

Just like to check what values I need in the select cost. The flow is working however still giving me a total of all items on each date.  I don't know how to use the select command against the filtered value instead of the get items value,

Thanks Jamie

[wyattdave]

I see the issue, the select cost input should be the filter output not the list output 

[JamieT1]

Hi davedidisco,

Any chance you can do a screen shot from the select item action?

[wyattdave]

Here you go, should look something like this:

[JamieT1]

Hi Davedidisco,

Sorry me again,

With your select cost step i see under map you have the filter array cost $ however i do not have this option?

[wyattdave · Accepted solution]

Hi @JamieT1 ,

No worries. You can change the cost $ to the field/column that you want to sum up.

All the fields probably need to be swapped out with yours as I had to guess the names