Power Platform Community Forum Thread Details

Hi there,

I think a little more clarity on how you want your data to work is needed here, as there may be a simpler solution - you're saying that a user can be linked to multiple states and multiple cities?

Out of the box you cannot filter what you add into a subgrid, you would need to use JavaScript to inject a new fetchXML query for the subgrid to use and possibly edit the ribbon XML "Add existing" button, which can get complicated.

You can filter lookup fields based on other lookup fields however, but this works best if for example, a user only belongs to one state and one (or multiple lookup fields) or more cities.

You can use views to filter what data is shown in the subgrid, but this does not change what you see when you add items into the subgrid.

Possible solution?

However, thinking about it, a possible way you might do this, is to use a "products and product line-items" data structure and the nested subgrid control. The result depends on where you want to see your data and at what level. This assumes you want to see all your data at the top User level.

I will try to explain what I mean but it might sound complicated, here it goes...:

Say you have Users, then you have your States, then you have your Cities. I may refer to States and Cities as our "Master Data" (that I would hide in another area on the site map). You would need to create all States and all Cities in their respective tables, and link the Cities to their parent State through the lookup field N:1 relationship you already have)

But now make a table for (example names) "User-States" for states that belong to the User and "State-Cities" for cities that belong to the States.

Create a lookup field on your line item tables - on User-States to States called "State Name" (for example), and on State Cities to Cities called "City Name" (for example). Add these fields to the quick create forms for each of the new tables respectively.

(You could use a classic workflow on create to set the name of the record to the name of the value in these lookup fields to State and City)

To filter the list to pick from only a list of cities that belong to the state, you would need to add another lookup field on State Cities to State, and create a mapping through my relationship to User States to autopopulate this. You can then set the lookup field to City, to filter by related records on that State lookup field so it only shows the cities related to it through that lookup in the master data tables [Cities N:1 States].

Also create a lookup field called "Parent State" (for example) from State Cities to User States, so that a User State record can have a subgrid to show State Cities.

Create a lookup field from User States to User.

This gives you the structure that User is on top, it can have many child States, which can have many child Cities.

Now, on your user form, create a subgrid for User States, but add the nested grid view control and link it through the correct lookup field, it might allow you to pick or type logical name of your lookup field from State Cities to User States.

Now when you use this, you would come into a user record, and add a User State record into the subgrid, by picking the state from the lookup field on the quick create form. (I'm not sure if it allows you to add a record to the nested subgrid for State Cities at this level of subgrid or not). They may need to click into the User State record to do the same thing by adding a State City record using the quick create. The lookup field for the State should already be populated through the mapping and locked to prevent change, but the second lookup field to City should filter on only Cities related to that State. If you need to add multiple, hit "Add New" at the bottom.

Now from the User view, you should be able to see the states and the cities related to those states that are all related to this User.

Note - you could technically add a User lookup field to the State Cities also, and populate that through mapping so that it's also linked to the User, but I'm not sure why you'd need that.

Hope this helps! :)