Limit an input to only the number available in a sharepoint column

[llwhite43]

I am creating an app that allows users to submit a request to sell shares but I need to limit the number of shares they can request to a value in a SharePoint list, I was trying to figure out how to do a lookup but because I am using a form attached to a different list I am finding this difficult.  Is this possible or is there a better way to restrict this?

[WannaBePro]

Hi @llwhite43 ,

We probably need a little more info to be more precise in the response, but at a high level what you're looking for sounds possible - assuming I've understood you correctly.

I'm guessing you can't sell more shares than you have, or a percentage thereof, so in theory what's in the list is the number of shares you have?

Whatever the number is, what you're looking to do is some field validation with a lookup to your list where the value is.  The key here is user experience - don't ask them to guess what the number is, rather tell them, and don't allow the form to be submitted if they exceed it.

To test:

You should be able to get your value by doing a lookup and assigning it to a variable, which you can then reference in your form.  Whether it's a global variable or local variable depends on whether you need the information on other screens or not.  For now, assuming you're fetching the value on the same screen as your form, let's use a local variable;

Add a button somewhere - you can remove this later and decide where best to put the code, a button just makes it easier for testing while you figure this out.

Set the button's OnSelect: property to;

UpdateContext({shareValue: LookUp(yourSPlist, condition, fieldWithValue)})

Where shareValue is the variable we're going to reference later, yourSPlist is the name of your SharePoint List connection, condition is how you're finding the right row, and fieldWithValue is the field that contains the value you want to fetch.

Now, if you press that button in play mode, the variable should be updated with the value.

Now you can reference it in some code.

Add a label under the Shares text input and set its Text: property to;

"You have " & shareValue & " shares available."

or whatever suits you - the point here is that the lookup value should appear in the sentence.

In the same way, you can add some logic to the input field experience.  You can either reset the field to the max value if the user goes over it, reset it to blank, or just highlight a red border for them.  The behaviour is mostly based on the same logic which you can apply in different ways.

Let's do the border color as a simple example - set the Input controls BorderColor: property to something like this;

If(Value(Self.Text)>shareValue, Red, Silver)

Silver - or whatever your default color is.  Now, if the value entered exceeds the fetched value, it will highlight red.  You can also set the OnChange: property to reset the control to it's default value if the value is exceeded; 

If(Value(Self.Text)>shareValue, Reset(Self))

You can also set your Submit buttons DisplayMode: property to remain disabled if this value for some reason is more than allowed and so on and so on.... 

Hope this helps

WBP

[llwhite43]

Thank you for providing this answer. I have already set up a global variable for the number I am referencing. However, I still can't figure out how to restrict the input on a cell to not exceed the number from the SharePoint list. This is the code I have now set up OnChange on the text input box next to (Number of Shares) on the form page but it is not working. 

If(Value(Self.Text)>= gShares), Notify ("Please contact Lindsey L White at lindsey.l.white@cmsenergy.com for help completing your transaction"),NotificationType.Error)) 

gShares is pulling the value from my SharePoint site and is populating the Shares Available For Transaction box. 

I don't want the user to be able to enter a number larger than the number in the Shares Available For Transaction box.