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!
Solved! Go to Solution.
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.
@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.
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
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!
@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 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).
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!
@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.
Check out new user group experience and if you are a leader please create your group
Please join us on Wednesday, July 21st at 8a PDT. We will be announcing the Winners of the Demo Extravaganza!
Participate in the Power Virtual Agents Community Challenge
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.