cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nickellis74
Resolver I
Resolver I

Filtering dropdown options by an indirect reference

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:

nickellis74_0-1623943505003.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
nickellis74
Resolver I
Resolver I

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!

View solution in original post

10 REPLIES 10
CNT
Community Champion
Community Champion

@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.

nickellis74
Resolver I
Resolver I

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

nickellis74_0-1624006385520.png

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

CNT
Community Champion
Community Champion

@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))

 

 

nickellis74
Resolver I
Resolver I

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

 

nickellis74_0-1624012918545.png

 

 

CNT
Community Champion
Community Champion

@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).

nickellis74
Resolver I
Resolver I

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

nickellis74_0-1624013759836.png

 

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!

CNT
Community Champion
Community Champion

@nickellis74 Try setting the Items property of ddField should be,

Choices( Filter(Fields,Farm.Farm = ddFarm.Selected.Farm))

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

CNT
Community Champion
Community Champion

@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.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza Winner Announcement

Please join us on Wednesday, July 21st at 8a PDT. We will be announcing the Winners of the Demo Extravaganza!

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,454)