Solved: Filtering dropdown options by an indirect referenc...

nickellis74 · ‎06-17-2021

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

nickellis74 · ‎06-18-2021

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

CNT · ‎06-17-2021

@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 · ‎06-18-2021

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

CNT · ‎06-18-2021

@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 · ‎06-18-2021

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

CNT · ‎06-18-2021

@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 · ‎06-18-2021

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!

CNT · ‎06-18-2021

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

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

nickellis74 · ‎06-18-2021

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

CNT · ‎06-18-2021

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