Filtering dropdown options by an indirect reference

(0) Share

Report

Posted on by nickellis74

432 Moderator

Hello all,

To be honest, I am not sure this is possible but I though I would see if anyone here has a solution. I need to filter a dropdown list by indirectly referring to another table. I'll show you what I mean:

I'm working on an app for a farm to record picking volumes. At the start of the process a supervisor fills in this form:

The Supervisor field defaults to the current user - done.

They then pick a Farm (the company has more than one).

The 'Field' dropdown then filters to only those fields in the selected farm (in this case 'Field' literally means a field where stuff grows).

The highlighted 'Product' dropdown is the problem.

There is another table in Dataverse called 'Crops' which records what is growing in each field, with start dates and end dates. I need that 'Product' dropdown to only show those products that are growing in the selected field right now. The only way to get that is to look it up on the Crops table which is connected to the app but not in use on this screen.

I tried this:

Choices(Filter(Crops,Field.Field = ddField.Field && 'Start Date' <= Today() && Or(IsBlank('End Date'), 'End Date' >= Today())).product)

and was wholly unsurprised when that didn't work. In English it's "Show the products which are used in the Product field of the Crops table where the field is the selected Field, the Start Date is Today or earlier, and the End Date is either blank or later than today".

I've tried various other combinations with very little luck. Anyone got any ideas?

Thanks in advance!

Nick

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Sort by

CNT 10,921 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@nickellis74 Could you please share the columns you have in the 'Crops' table along with their Data Types. I'm curious why you are using field.field in the Filter. Is the column field a lookup to the field table? Please give as much details as possible.

Was this reply helpful? Yes No
nickellis74 432 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @CNT thanks for responding. See the screen shot below which is the full table spec. As you say, 'Field' is a lookup - The model is roughly
'Farms' have 'Fields'
'Products' have 'Varieties'
'Crops' have 'Farms', 'Fields', 'Products', 'Varieties' and a date range
'Picking Jobs' have 'Farms', 'Fields', 'Products', 'Varieties' and some job specific data that is manually entered
The requirement is when creating a 'Picking Job' the user will enter the farm and the field. The dropdown of 'Product' should then be limited to products which are listed on current crops, to stop them accidentally recording picking fruit that doesn't exist. In the real world it seems a perfectly reasonable constraint.

I had a flash of inspiration in the night and have been working on going via a collection, setting the 'On Change' of the 'Field' dropdown to
ClearCollect( CropProducts, Filter(Products,wbc_productid in Filter( Crops, Field.Field = ddFields.Selected.Field ).Product ) );
and then setting the Items of the 'Product' dropdown to CropProducts. But that doesn't work either. Weirdly, that produces the right number of rows, but the rows themselves are entirely blank, meaning I just a bunch of nothing to choose from in the Product dd.

It's driving me nuts! If you can work out a way to achieve that I will sing your praises from the rooftops!

Nick

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@nickellis74 To make sure we get it right, we will do it in parts. Let's leave the date bit out. Try this and see if you get the correct products for the selected field,

Choices(Filter(Crops,Field.Field = ddField.Selected.Field))

Was this reply helpful? Yes No
nickellis74 432 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Entered as the 'Items' of the ddProducts control, I get this:

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@nickellis74 Could you please confirm that the Column Filed is a LookUp to the Field table and also could you confirm that the Items property of ddField is Choices(Crop.Field).

Was this reply helpful? Yes No
nickellis74 432 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

I really appreciate your help here, @CNT

The form is on the 'Picking Jobs' table, not the Crops table.
Yes, the column Field is a Lookup of the table Fields

The Items property of ddField is 'Filter(Fields,Farm.Farm = ddFarm.Selected.Farm)'

That part has all been working up until now. The breakage happened when trying to filter Product against the Crops table, filtering that against the selected field!

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@nickellis74 Try setting the Items property of ddField should be,
Choices( Filter(Fields,Farm.Farm = ddFarm.Selected.Farm))

Was this reply helpful? Yes No
nickellis74 432 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Yeah the Field part is all working as expected. It's the Product part that's causing me grief.

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@nickellis74 I do understand. I've something similar and works without any issues. In the formula, it says Incompatiable Type of Comparision which means ddnField.Selected.Field is not returning something of type Field. Could you please check that and confirm it is of type Field.

Was this reply helpful? Yes No
Verified answer

nickellis74 432 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you @CNT for all your help. Although I didn't get there in the way you were going, I have now solved and - and your input was an inspiration on how to do so. It was your point about it not returning what we were expecting, which took me on a thought process that to be honest I cannot fully recall.

Nonetheless, we did get to Filter(Crops,Field.Field = ddFields.Selected.Field), and rather than try and use that to set a dropdown Items property I used it to populate a Gallery Data Source. That's actually a better user experience since we can put an image of the crop and inherit the Product and the Variety from the Crop record in one click.

My only challenge now is that the Patch formula I'm using says one of the fields is mandatory. I agree it is, but I am passing it a value which on test does get saved - even though it throws the error. Very odd.

Thanks again!

Was this reply helpful? Yes No