OK, So I have a reference drop down list. The list will show DOB Unique IDs from the DOB table. The Unique DOBs all start with 3 letters. Those 3 letter indicate the location where the DOB was created.
In a seperate live form for “incidents”, you choose your location (also a ref) and it will then populate a text box with your location ID (The 3 Letters). A second ref will list all the DOB’s. One of them will contain the 3 letters within it’s unique ID. So…
I am trying to filter that second dropdown ref list, whether it be using sliced data or not. I simply want to filter the list by the 3 letters of the [Base ID] populated by the 1st Drop Down. For instance APE. This Base ID is populated in [Base ID] text box in the live form. So I had thought of creating a slice of the DOB table that simply would look for [Site ID] from the live form. I can’t get that to work. I also tried creating a slice with the formula:
contains(DOB Open and Read Only[DOB Unique ID],Incident Data[Base ID]) So looking for a DOB Unique ID within a slice called DOB Open and Read Only, yet containing what is in the [Base ID] text box in the live form (I thought).
I even tried:
contains([DOB Unique ID],Incident Data[Base ID])
This didn’t work either, the list is blank when it should contain the DOB Unique ID containing “APE”. Someone please help.
You need to use a FILTER() or SELECT() expression can COMPARE by Base ID and CHOOSE the row you wish. And based on the quote above, since it sounds like you only expect a single row to match, you don’t really need a second dropdown but rather can assign the Ref value straight away. Something like:
ANY(FILTER( "DOB Table", STARTSWITH([DOB Unique ID], [Base ID])
FILTER() finds ALL the Row Keys (i.e. ref values) for the rows where the [DOB Unique ID] columns “starts with” the value from [Base ID] column. It sounds like you only expect to have a single row. So, by using ANY(), it will extract that single Row Key from the returned LIST… and yes it is a LIST of 1 but still a List from which you need extract values.
If anything is confusing, please ask.
I hope this helps!
This did work great, there was the possibility that there could be more than 1 item with the same Base ID. So what I want to do now is to count the number of DOB’s created with the Base ID, but I want the count to occur when creating the DOB. I’ve used
count(FILTER(“DOB OPEN AND READ ONLY”, STARTSWITH([DOB Unique ID], [Location ID])))
but the number is always equal to the number of created DOBs. Any idea?
I don’t understand. Are you saying that the count equals the TOTAL created DOB’s in the source table? If so, then please show us that because that expression definitely looks to be counting the reduced list only.