Filtering dropdown options by an indirect reference

[nickellis74]

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

[CNT]

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

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]

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

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

[CNT]

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

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]

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

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

[nickellis74]

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

[CNT]

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