How do I set a input limit for a sharepoint column for each user ?

[agent3154]

Hi, 

    I am trying to develop an app that has the following requriement:

    The app uses sharepoint as the datasource. Each user can only enter up to 1000 for a number field in the sharepoint list per year. (No matter how many times they create items in the sharepoint list, the app will first add user's previous inputs for that number field and calculate the remaining value that user is allowed to put). And each user's total input will automatically resets after a certain date.

     For example, if an user uses this app to create a sharepoint list item and entered 500 for that number field, he/she can only put 500 or less for that field the next time he/she uses this app. Otherwise the app will deny him/her from creating the new item. 

I was thinking connect powerapp with the flow, and uses flow to pass the data to an excel file. When user clicks submit form in the powerapp, it will read the data from excel file and return corresponding message. But I do not know how exactly it works and I was not sure how to reset  each user's total input after a certian date. 

any help/idea will be much appreciated!

[v-monli-msft · Accepted solution]

Hi @agent3154,

Actually, this can be achieved without Flow. But you will need to add a Person column in the SharePoint list. And in this column, always fill with the user who created the item.

After creating the Person1 column, following below steps to achieve this:

1. Create an auto-generated app using SharePoint list connection.

2. In EditScreen1, set the OnSelect property of the submit button to:

If(Value(DataCardValue14.Text)<=1000-Sum(Filter(ListName,Person1.Email=User().Email),Number), SubmitForm(EditForm1),false)

Person1: The person and group column which hold the authors.

Number: The column name of your number field.

DataCardValue14: The textinput control which hold the number

3. On the same screen, add a Label control, set its Text property to:

"You must have the number less than "& 1000-Sum(Filter(ListName,Person1.Email=User().Email),Number)

Set its Visible property to:

If(Value(DataCardValue14.Text)<=1000-Sum(Filter(ListName,Person1.Email=User().Email),Number),false,true)

Regards,

Mona

[agent3154]

 Hi, Mona,

    Thank you so much for the help! Greatly appreciated ! I totally forgot about using the built-in filter/sum function, and I can just add a date range condition to the filter and updated it every year to achieve the another requriement. 

    I have another question. Is there any way that I can include the aprroval status in the filter? It seems to me that approval status column is not shown in the powerapp. 

   e.g 

Sum(Filter(ListName,Person1.Email=User().Email,'xxxx'<created<'xxxxx',ApprovalStatus="approved"),Number)

[v-monli-msft]

Hi @agent3154,

Is it a Choice column? If so, it should be 

ApprovalStatus.Value="approved"

Notice that in your sharepoint, the option are "approved" not "Approved".

Regards,

Mona

[agent3154]

Hi, Mona,

     Thank you for you response! I was referring to the automatically generated column (Approval Status) when I use the flow action to set an item's approval status to "approved" or "rejected". (otherwise it displays as pending in the sharepoint list). This column is not displayed in the powerapp filter function, but I guess I can just create a choice column and filter the choice column instead. 

    I have gotten the powerapp function as expected, but I am still having issue with the flow for this app. I set a flow that will be triggered when a sharepoint list item is created or modified. However, whenever I set the approval status to  approved or rejected using the flow, the flow will be triggered again. (since it counts as "modify" ). Is there a way to prevent triggering the flow when setting up the approval status (In other words, only triggering the flow when the user modifies the item) ?