Sharepoint list, Power Automate, and formula's (calculated column)

(0) Share

Report

Posted on by SamHarland97

Hi,

Thank you in advance for any help.

I know this might not be the right place, but I am unsure where else to ask.

I am working on building a flow which automatically populates a SharePoint list when a colleague makes a suggestion for improvement. We have the automation from that side working, however we are stuck on 2 columns. We'd like to calculate the number of suggestions each colleague makes (comes from the form we record their name in another column), and also to determine if it's their first suggestion, and which suggestion is their first.

Thanks again for any help.

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

Expiscornovus 33,189 Most Valuable Professional on at

Like (0)

Report

Hi @SamHarland97,

It depends a bit on what you want to do with that information? Is it to use it later in the flow for additional steps. Or is it just to get this as an overview?

For an overview you could create a custom view on the SharePoint list and group by the person column and sort the records on ID ascending (the lowest ID will be the first response for the person) to just get an overview.

Hope this helps a bit?

Was this reply helpful? Yes No
SamHarland97 45 on at

Like (0)

Report

Hi @Expiscornovus,

I hope you are well, and had a nice weekend.

Thank you for your response.

So we were looking to have the count field as its own column which is constantly there throughout. It would be incorporated into a flow, but that'll be for reporting using Power BI.

So for example if my name appears in the column 'suggester' 3 times, we'd like a column that reflects that, also stating a 'yes or no' for if it's their first suggestion.

Thanks again for all of your help.

Was this reply helpful? Yes No
JPMHuls 103 on at

Like (0)

Report

If you would use a list lookup column to a list with the names (e.g. NamesListLookup), you could add a list lookup column to the latter to the list using the lookup into NameslistLookup. You now will see an extra column with "(count related)" added to the column name. This column will now keep track of the number of times a name is selected in the given list. if you use the NamesListLookup in multiple list, you could create a "(count related)" column for each list.
For example: I have a list CitiesAndCountries. In my list "LookupTest" I have a lookup column "MyTown" to the City column. In the CitiesAndCountries I now can add the column "MyTown (count related)":
This will now show how often a City is selected in LookupTest.

Hopefully this helps.

Was this reply helpful? Yes No
SamHarland97 45 on at

Like (0)

Report

Hi @JPMHuls

Thank you for your reply.

I had tried this unfortunately the column we would like to count isn't an option as it is set as a 'Person or Group' column.

Thanks,
Sam

Was this reply helpful? Yes No
JPMHuls 103 on at

Like (0)

Report

You could consider using the Title (or another single line of text field) to show the user's display name also used in the dropdown and have the flow retrieve the Peron column value for the selected name... It doesn't earn a beauty prize but may work for you.

If not, why not do a Get items with filter on suggestion type and user and use expression function length() on the returned items?

Was this reply helpful? Yes No
SamHarland97 45 on at

Like (0)

Report

Hi @JPMHuls

Thank you again for your help.

I have followed this in a slightly different way, I have used the 'Responders' Email' section from the form, this has worked from the flow side of things.

It works and inputs the new info, however the (Count related) field doesn't come up for me.

Thanks,
Sam

Was this reply helpful? Yes No
JPMHuls 103 on at

Like (0)

Report

It only works with List Look columns. And the Count Related is availble in the lookup list itself after adding the lookup column in the other list/library... Not sure if it works in Modern sites though.

Was this reply helpful? Yes No
JPMHuls 103 on at

Like (0)

Report

Below a screenshot. LookupTest's column MyTown does a lookup into CountriesAndCities, while the latter's column MyTownUsage does a lookup to the MyTown column in LookupTest.

Was this reply helpful? Yes No