Answered

Lookup Product Number based on Specific Criteria

(0) Share

Report

Posted on by Cammers79

Hi All,

I and trying to create a lookup to the Product Number based on the Size, Colour and Product Name. My Product Name is a text label, the Size and Colour are both drop down boxes. I have my spreadsheet with all of the Products with their Name, Size, Colour and the Product Number. I have my gallery with all of my products.

The issue I am having is that I may have the same Product Name where there are just different sizes and colours which means they have a slightly different Product Number and for some reason the formula I'm using is not bring back the Product Number for instances where there may be different sizes and/or colours. This is my formula:

First(Filter(ProductNumbers, ProdSize = SizeDD.Selected.Value && ProdColour = ColourDD.Selected.Value && ProdName = ProductNameLabel.Text)).ProdNumber

I would want the Product Number to change based on what is in the Size and Colour dropdowns.

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Ethan_009 4,838 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @Cammers79 ,

I hope all the reference Column name passed is correct.
Try the following code:
With( { Records: Filter(ProductNumbers, ProdSize = SizeDD.Selected.Value && ProdColour = ColourDD.Selected.Value && ProdName = ProductNameLabel.Text) }, If( CountRows(Records) = 0, "No Product Found", If( CountRows(Records) > 1, "Multiple Records Found", First(Records).ProdNumber ) ) )

This will give more insights why product number is not coming.
Hope this helps

Was this reply helpful? Yes No
Cammers79 35 on at

Like (0)

Report
Copy link

Link copied!

For any of the items where I may have more than 1 product number, because of size or colour, it is not finding the product number based on the size and/or colour. I hope that makes sense. For instance with the first item in my gallery, There is a white or red colour option. I have each of these options in separate rows within the excel spreadsheet. Both items have the same name and size but the colour is different. Please see below:

It's finding the product code for the white frame but for the Red it's not finding the product.

Was this reply helpful? Yes No
Ethan_009 4,838 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @Cammers79 ,

Do you get the same result when you use the following code?
LookUp( ProductNumbers, ProdSize = SizeDD.Selected.Value && ProdColour = ColourDD.Selected.Value && ProdName = ProductNameLabel.Text, ThisRecord.ProdNumber )

Note: Filter will work on the amount of record captured within the App only. If you have 500 records captured by the App, and if your record is sitting somewhere on 2500th row then you won't get the record.
Max row limit for app is 2000.

The yellow warning sign might be an indication of delegation, you will see when you hover on top of the sign.

Hope this helps

Was this reply helpful? Yes No
Cammers79 35 on at

Like (0)

Report
Copy link

Link copied!

Yes still get the same result when using LookUp. I actually started with the LookUp function.

Yes it's a delegation warning for using the || or && in the formula. I only have 58 rows of data so I didn't think it would be an issue.

Was this reply helpful? Yes No
Ethan_009 4,838 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @Cammers79 ,

It's not the only rows present in the excel sheet you're using.
It's total records within the App.

Can you increase the Row limit in App settings and keep it to max which is 2000 rows.
This will ensure more records flowing in the App.

Hope this helps

Was this reply helpful? Yes No
v-hanytian-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Cammers79 ,

We suggest you try the formula like this.
First(Filter(ProductNumbers, ProdSize = SizeDD.Selected.Value && ProdColour = "White" && ProdName = ProductNameLabel.Text)).ProdNumber

if it worked,may I ask the formula of the dropdown?

If you need any further assistance, please do not hesitate to reply to me.

Hope this will help you.

Best Regards,
Tina

Was this reply helpful? Yes No
Cammers79 35 on at

Like (0)

Report
Copy link

Link copied!

The formula I have in the dropdown is Split(ThisItem.Size, ","), because I am pulling this from another sheet where the sizes are all in the same row. I do have the product numbers in this sheet as well so if I am able to use that instead of the other sheet that would be good.

I am only using the other sheet because I separated them all out so that I could access the Product Numbers and also keep track of stock for each product number but I was trying to keep from having multiple items in the gallery with the same name.

Was this reply helpful? Yes No
Cammers79 35 on at

Like (0)

Report
Copy link

Link copied!

I've tried everything everyone has suggested and I'm still not getting this to work. I've tried the exact same thing in a new app with nothing in it but the gallery and the 3 tables I have and I still get the delegation warning so I know that is not the issue and I still get the same result. It simply will not find anything other than the first row where one of the conditions is correct. It's not assessing the other columns within the row to determine if it is the correct one where some of the column information may be the same.

Was this reply helpful? Yes No
Ethan_009 4,838 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @Cammers79 ,

Try to implement Paginated style pattern in your App.
This will ensure batches of records to be filtered only:
YouTube Link for Reference

This will help you

Was this reply helpful? Yes No
Verified answer

Cammers79 35 on at

Like (0)

Report
Copy link

Link copied!

I've solved my problem.

The reason the LookUp function wasn't working on anything that had multiple sizes or colours was because of the way I had my DropDown boxes set up, which was a result of how I had my data set up.

I had a Split function within them because my sizes and colours were all in the same cell, separated by a comma. Like this: Split(ThisItem.Size, ","). So it wasn't picking up anything other than the first Size or Colour no matter what I did.

I went back to my data and made every item with a unique size and colour an individual row. I then grouped my gallery by the Product Name. I then simply applied this to my dropdown box and it returned all of the sizes for that item. I did the same for the colour dropdown.:

Distinct(Filter(ProductList, ProductName = ThisItem.ProductName),Size)

I then used the following for my Product Number text label:
LookUp(
ProductList,
ProductName = ThisItem.ProductName &&
Size = SizeDD.Selected.Value &&
Color = ColourDD.Selected.Value).ProductNumber

and it worked exactly as it should

Was this reply helpful? Yes No