cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Validating an ID number against 20,000 records in data source

I need to create a app that will allow users to enter a 9-digit ID number, with the app confirming that the number exists in a table of 20,000 records, and returning a few additional fields from the table. There will be only one record in the datasource associated with any 9-digit ID. Is this possible with PowerApps?

7 REPLIES 7
Highlighted
Continued Contributor
Continued Contributor

Re: Validating an ID number against 20,000 records in data source

Hi  @geraldmpiper

 

This shouldn't be a problem for PowerApps. You will just need to connect to your data source and use the Lookup Function

 

Something along the lines of 

LookUp( DataSourceConnector, IDDataSourceColumnName =  IDNumberTextBox , ReturnColumn1,ReturnColumn2)

Thanks,
Nicky

Highlighted
Helper I
Helper I

Re: Validating an ID number against 20,000 records in data source

Thanks, Nicky!

 

And no problem with the size of the datasource? I know there is a 500-record limitation for sharepoint lists.

Highlighted
Continued Contributor
Continued Contributor

Re: Validating an ID number against 20,000 records in data source

Hi @geraldmpiper,

 

The 500 Limit is just the default app limit for a non delegable query, you can increase this to 2000 in the app settings but its not a good idea if you can help it.

 

A Lookup only returns one value so the size of your data source shouldn't matter but different sources have different limitations

 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list provides a full list of the available commands for different data sources.

 

Thanks,
Nicky

Highlighted
Memorable Member
Memorable Member

Re: Validating an ID number against 20,000 records in data source

Hi @geraldmpiper

 

@OneThing is perfectly right, the lookup function doest exactly what you need. 

 

I just want to clarify some behavior related to the delegation.


@OneThing wrote:

Lookup only returns one value so the size of your data source shouldn't matter but different sources have different limitations

The delegation happens when querying your datasource, it doesn't matter if the function return one or several items.

Lookup is delegable in SharePoint, so you can use it to parse more than 500 items to find your item.

Last() function for example returns only one item, but it is not delegable. So you'll face issues operating a Last function against a SharePoint list containing more than 500 items.

 

I just wanted to make things clear, delegation has nothing to do with how much items are retrieved by the function, but only how much items your datasource is containing.

 

But again, in your case, @OneThing is right, the LookUp function is 100% what you need.

 

Théo

 

 

Highlighted
Helper I
Helper I

Re: Validating an ID number against 20,000 records in data source

Thanks again, Nicky!

 

geraldmpiper

Highlighted
Community Support
Community Support

Re: Validating an ID number against 20,000 records in data source

Hi @geraldmpiper,

Have you solved your problem?

I also agree with @OneThing's thought almost. I think the LookUp function could achieve your needs.

Please take a try with the following formula:

IsBlank(LookUp('YourSPList', DigitNumber = TextInput1.Text)) /* <-- DigitNumber column represents the column name within your SP list, which used to store the 9-digit ID number*/

Above formula would return true if the 9-digit ID number the end user typed is not existed within your SP list.

More details about the LookUp function, please check the following article:

LookUp function

Note: There is something wrong with the syntax of the LookUp formula that @OneThing provided.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Validating an ID number against 20,000 records in data source

Thanks very much, Kris!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,222)