harfmt
Design Canvas Artiste

365 form - cascading lookup with filter to text column

Hi All, hopefully you can help me with this. 

 

For some reason i can't get the cascading lookup to work for this scenario. 

 

Following is an example of the issue:

I have an existing list (list1). In list1 there is a 'city' text field (plus a 'city ID' text field). A form lookup control populates these two fields.

 

There is also a free text 'suburb' field in list1. I feel like this field could be the issue, not sure .

 

I have a second list2 (and form) where I want to lookup the suburbs filtered on the city from list1.

So i have two lookup controls on a form connected to list2:

   I can bring back the cities into a form lookup dropdown - text field and ID field for City. 

   I can bring back the suburbs into a form lookup dropdown - text field and ID field for Suburb. 

However when i attempt to filter the suburb on the city control, the drop down is blank. 

 

A note given when setting the 'Filtered by control' field in the List Lookup control of form2 is "The control must be of a type that can post back upon selection and return a value that will filter correctly."

What types can't post back? I'm only using text fields.

I really can't pinpoint where the issue is occurring in list one - the suburb field?

Or list2 with the lookups?

 

Any ideas as to where I'm going wrong?

Thanks

 

 

0 Kudos
Reply
2 Replies
DAGiessenburg
Forms Fledgling

Re: 365 form - cascading lookup with filter to text column

If i understand you correct you have the following:

 

List 1:

City (Title)

CityID (text field)

suburb (text field)

 

Entries can be:

(Item)ID, City, CityID, Suburb (neighborhood/district)

1, Amsterdam, 020, Jordaan

2, Amsterdam, 020, Grachtengordel

3, Amsterdam, 020, Waterland

4, Rotterdam, 010, Feijenoord

5, Rotterdam, 010, IJsselmonde

6, Rotterdam, 010,Delfshaven

7, Eindhoven, 040, Strijp

8, Eindhoven, 040, Barrier

9, Eindhoven, 040, Gildebuurt

 

List 2:

...

 

On your Form in list 2 you want to get all the Suburbs. If you select the correct city in an dropdown, correct?

 

The problem is that you probably are using the dropdown field value. This is an collection.

it has the value like: 6;#Rotterdam.

You will need to split this or do a look up.

 

I alway do the following.

1. Create a cal value field or variable and call it CityName

2. Put in the value: lookup("Cities", "ID", [Dropdown field with cities], "Title")

3. Create an second dropdown for Suburb with the filter by control value:

Where field Title

Filtered by control CityName

 

If you select an City, then the cal field/ variable has Title. If i select Rotterdam from the dropdown. 

Then Suburb field, get the values: Feijenoord, IJsselmonde, Delfshaven

 

 

 

0 Kudos
Reply
harfmt
Design Canvas Artiste

Re: 365 form - cascading lookup with filter to text column

@DAGiessenburg 

Actual test example:

 

No choice columns. Only text columns and lookup form controls. I'm obviously missing something as to why the second drop down lookup control is coming back empty. If I remove the filter, it brings back all Dam Names no problem. 

 

Let me know if I'm missing anything from the screenshots below.

 

- Create List 1 (TestCascadingLookup)
Screenshot1.png

 

 

Screenshot2.pngScreenshot3.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Screenshot4.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

- Create List 2 (TestCascadingLookup2) (sharepoint is suddenly truncating list names)Workflow Screenshot 6.png

 

 

 

Screenshot5.png

 

 

 

 

 

 

 

 

The DamName in this form produces an empty drop down. 

Screenshot6.png

 

 

 

 

 

 

 

 

 

Looking up the same reference list as Form1 above:

Screenshot7.png

 

 

 

 

 

 

 

 

 

 

 

Lookup up List 1(TestCascadingLookup)Screenshot8.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Dam drop down lookup list is emptyScreenshot9.png

 

 

 

0 Kudos
Reply