cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
originalltd
Level: Powered On

Collection with condition

Hi,

 

I hope someone can push me in the right direction for this solution.

 

I want to do the following:

 

Create a collection of "Serial Numbers" (generated already created in a SQL table), based on a number that is inputted manually.

So for example, an input box of a quantity of '5', will collect 5 rows from a SQL table where the column "Used" = 'N'. 

Or another example:  an input box of a quantity of '10' will collect 10 rows from a SQL table where the column "Used" ='N'.

 

It's basically pulling a list of serial numbers that hasn't been used (based on the amount needed in the input box).

 

Thanks

 

5 REPLIES 5
originalltd
Level: Powered On

Re: Collection with condition

Never-mind, found the variable I was looking for!

"FirstN" allowed me to pull the number of records from the table, along with a filter for it's condition.

AnneZC
Level: Powered On

Re: Collection with condition

Hi @originalltd 

You'll need something along these lines:

Create a collection of the first x many (from input textbox) items from your data source that have "Used" = "N":

ClearCollect(NextSerialNumbersColl,FirstN(Filter(SerialNumbersTable,Used="N"),Value(NoOfSerialNumbers_Input1.Text)))

 You might like to add a SortByColumns function in as well if there is a column that you want to order the Serial Numbers you are getting by. 

originalltd
Level: Powered On

Re: Collection with condition

@AnneZC  Thanks for that, my condition looked very similar :).

 

The next issue I have, is that it's filtering wrong. For example, "FirstN" is collecting 4 lines - but two of those have "Used" = 'Y', and therefore it only outputs 2 lines (Where "Used" ='N').

 

I'm thinking the best way around that is a SQL view that is designed to show "Used" = 'N', unless there is an alternative condition I can use in PowerApps - but I can't think of anything on this Monday morning!!!

 

Thanks!

 

 

AnneZC
Level: Powered On

Re: Collection with condition

Hi @originalltd 

What data type is your "Used" column? Is it Text or a Boolean type field?

 

And do you have the Filter function inside the FirstN function?  It sounds like you could be doing the FirstN and then Filtering on that and you need to Filter first i.e. Filter is inside the FirstN function.

originalltd
Level: Powered On

Re: Collection with condition

It is nvarchar(1), text. I've created a SQL view with the condition and it's collecting the correct amount now.

But I am interested in your solution if PowerApps allows it.


Thanks

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,380)