# Continouse checking of a text field against a database

**URL:** <https://forum.inductiveautomation.com/t/continouse-checking-of-a-text-field-against-a-database/76228>\
**Category:** Ignition\
**Tags:** perspective, scripting\
**Created:** [July 11, 2023, 8:38pm UTC](https://forum.inductiveautomation.com/t/continouse-checking-of-a-text-field-against-a-database/76228 "2023-07-11T20:38:04Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Cameron\_Nickle](https://avatars.discourse-cdn.com/v4/letter/c/b3f665/32.png) [@Cameron\_Nickle](https://forum.inductiveautomation.com/u/Cameron_Nickle)\
**Post date:** [July 11, 2023, 8:38pm UTC](https://forum.inductiveautomation.com/t/continouse-checking-of-a-text-field-against-a-database/76228/1 "2023-07-11T20:38:04Z")

</div>

Hi all,

I'm currently working on creating a text field component that checks its own text-value against a database. I have the database return type as a list, but I cannot find a way or place to put the code to have it constantly checking against the users input.

the pseduo code would look something like this:

```auto
   bds = system.db.runNamedQuery("Admin_user/get_userInfo/get_allUserNames", {})
							
	myList = bds.getColumnAsList(bds.getColumnIndex('username'))
							
	if self.props.text in myList:
		#Display some type of overlay to the user that they cannot use that user name
	else:
		#do nothing as the username should be acceptable

```

Does anyone know how I would be able to achieve this and where I would need to run this script to do so?

Thank you,

Cam

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [July 11, 2023, 8:44pm UTC](https://forum.inductiveautomation.com/t/continouse-checking-of-a-text-field-against-a-database/76228/2 "2023-07-11T20:44:29Z")

</div>

Is there any reason you aren't using built-in [`system.user`](https://docs.inductiveautomation.com/display/DOC81/system.user?src=sidebar) functions or [`system.perspective.isAuthorized`](https://docs.inductiveautomation.com/display/DOC81/system.perspective.isAuthorized) function?

---

<div class="post-metadata">

**Author:** ![Usman\_Ashraf](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/usman_ashraf/32/43168_2.png) [@Usman\_Ashraf](https://forum.inductiveautomation.com/u/Usman_Ashraf)\
**Post date:** [July 11, 2023, 8:53pm UTC](https://forum.inductiveautomation.com/t/continouse-checking-of-a-text-field-against-a-database/76228/3 "2023-07-11T20:53:57Z")

</div>

Hey @Cameron_Nickle

The pseduo code seems to be okay in theory. However, it will be better that we iterate the list in a loop and before comparison, go for lower case and trim the string (strip function). That way same name, but different case will also be flagged and if there are any leading/tailing spaces, they will also get removed.

The best place to call this script will be onFocus and onBlur events of the text field. If more granularity is required, then key press event can also be captured. However, the text field updated value is only reflected once user commits the value, either by pressing enter or loosing / gaining focus.

---

<div class="post-metadata">

**Author:** ![Cameron\_Nickle](https://avatars.discourse-cdn.com/v4/letter/c/b3f665/32.png) [@Cameron\_Nickle](https://forum.inductiveautomation.com/u/Cameron_Nickle)\
**Post date:** [July 12, 2023, 3:09pm UTC](https://forum.inductiveautomation.com/t/continouse-checking-of-a-text-field-against-a-database/76228/4 "2023-07-12T15:09:17Z")

</div>

Hi Usman,

I've updated the code to look like this:

```auto
def runAction(self, event):
	#Get all the usernames
	bds = system.db.runNamedQuery("Admin_user/get_userInfo/get_allUserNames", {})
	#convedrt the dataset to a list 
	myList = bds.getColumnAsList(bds.getColumnIndex('username'))
		
	#Pre-process the data for accurate comparison 
	for indx, val in enumerate(myList):
		myList[indx] = val.strip().lower()
		
	selfVal = self.props.text
	selfVal = selfVal.strip().lower()
		
		
	if selfVal in myList:
		self.getSibling("Test1").props.text = 'error'
	else:
		self.getSibling("Test1").props.text = 'good'

```

Which is all good an well, however, I'd like to be able to place some sort of 'error' overlay on-top of the text field to indicate to the user that they cannot have that name as opposed to simply printing 'error' beside the text field. Do you know of a way to do this?

Thank you,

Cam

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [July 12, 2023, 3:30pm UTC](https://forum.inductiveautomation.com/t/continouse-checking-of-a-text-field-against-a-database/76228/5 "2023-07-12T15:30:59Z")

</div>

Depending on what you're trying to do, there might be better ways to approach this. I think we have a case of the [xy problem](https://xyproblem.info/).

But nonetheless, I'll suggest another, more general way of doing this.  
So you have a text field where a user types things, and a label that displays the result of the comparison of the user input against a data structure.  
now:

1. Add a custom property, and bind it to your query.
2. Set the input field `deferUpdate` property to false
3. Add a structure binding on the label. Bind it on both the input field and the custom property
4. Add an expression transform to that binding, and use the `lookup` expression function to find the input into the dataset.  
If your data structure is not a dataset (it should be, since it's the result of a query), use a script binding instead of an expression binding.