Two Drop downs filtering and pulling from sharepoint data corrupted.

[SkidFx]

Hi Experts, Thanks to help me out here.

I have dropdowns sorting & filtering a SharePoint list, when I select tool name in dropdown 1 (top), I then select the size of the selected tool Dropdown 2, and the form should update the correct items from the SP List. Though what happens is the Power app confuses the R tools data for the S tool data as they both have the same size tools. How to correct this? 

SP Data = PTData

Dropdown1 = NameDd

Dropdown2 = SizeDd

Drop Down 1 Items- Sort(Distinct(PTData_1, Title), Result) 

Onchange/Select=False

Drop Down 2 Items - Distinct(Filter(PTData_1, Title=NameDd.SelectedText.Result).Size, Size)

On Change = Set(varRecord, LookUp(PTData_1, Size = SizeDd.SelectedText.Result))

On Select = Set(varRecord, First(Sort(Filter(PTData_1,Size =SizeDd.SelectedText.Result),ID))) 

Here is a snip i selected Drop Down 1 = R (tool)

Drop Down 2 = 2.0" (Size)

Result is actually showing a 2" S tool data. Not the R.

Pulled S Tool data not R tool

Here is a snip of the SP list 

[v-qiaqi-msft · Accepted solution]

Hi @SkidFx，

Do you want to display the correct tool and corresponding size in the Label?

Could you pleas tell me that how you set the Text property of the Tool Label?

Here is what I assume your scenario:

You set the Tool Label Text property as below:

varRecord.Tool

Whatever the LookUp() or the First(), both of them return a records with the R tool instead of the S tool because you sort the records by ID column. And from your SP list, you could find that the R tool related record is just in front of the S tool.

So if you want to populate the Label with correct values, you should set as below:

NameDd.Selected.Result // Tool Label Text property

SizeDd.Selected.Result// Size Label Text property

LookUp(PTData_1, Size = SizeDd.Selected.Result && Title=NameDd.Selected.Result).Latches // Latches Label Text property​

LookUp(PTData_1, Size = SizeDd.Selected.Result && Title=NameDd.Selected.Result).Reach// Reach Label Text property​

So as the following labels, you just need to replace the column name outside the LookUp() formula.

[SkidFx]

Hi, its not a label its in a form. 

[SkidFx]

Do i need to change any of the properties of the fields in the form?

[StalinPonnusamy]

Hi @SkidFx

Set Drop Down 2 Items property

With(
    {_Item: 'PTData_1'},
    Distinct(
        Filter(
            _Item,
            Title = NameDd.Selected.Value
        ),
        Size
    )
)

MS article for dependent dropdown: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/dependent-drop-down-lists

[v-qiaqi-msft]

Hi @SkidFx,

Please tell me the answer directly instead of just denying something.

Is it a text input control within the Form?

Could you please check my solution one more time?

If it is a Text input control, please set the Default property with the formula I provided.

[SkidFx]

Hey Experts, Apologies for the lag here-- My Colleague told found the error for me- If your answer was the same let me know ill mark it as o. 

First thing I notice is you are setting varRecord onChange and onSelect. This is likely to cause problems unless it’s always the same. You need to make sure it’s the same record. I think it isn’t based on the different functions you have. When you are looking up the record by size only, you will have the confusion you mention.

Try using 

OnChange= Set(varRecord,LookUp(PTData_1,Size=SizeDd.SelectedText.Result&&Title=NameDd.SelectedText.Result))

This worked for me.