Related items with external lists having foregin key associations

[bbeach]

Hello,

My application is entirely based on external content types and lists i.e. no native SharePoint lists. Several of the external content types have foreign key associations to enable 1-M relationships. For example Clients > Contacts.

On my Client display form, I have added a related list for Client Contacts. In setting up the data source using fd, I have the following settings:

List: Client Contacts
View: Contacts Read List
Filter by form field
Form field: client_id

List Field is where I have the problem. The Contacts Read List view has a column client_id, however, client_id is not available in the List Field drop-down. Instead, the foreign key association 'Client' (which is the external content lookup field) is listed, and doesn't appear to work as a filter field.

How can I filter a related list when using external content with foreign key associations?

Thanks,

Bob

[AlexZver]

Hi Bob,

Please check what exact value is passed to the Related Items control by expanding filtration options (by clicking the column header of the Related items in the form):

Screenshot_28.png (18.62 KiB) Viewed 2022 times

Also, you could try some other approaches in filtering: https://spform.com/documentation/filter ... ield-types

[bbeach]

I've worked around the issue. For the benefit of others, here are the relevant details.

I have 2 external content types with data on SQL tables: Client and Contact. I have configured a 1-M foreign key association in SPD between these ECTs. i.e. a Client can have 1-M Contacts.

The Client table has primary key column client_id, and the Contact table has client_id foreign key column.

For my Contact ECT read list operation, I use a SQL view. The view returns client_id in addition to all other columns in the Contact table, as well as other joined tables.

Finally, I have created external lists for each ECT in SharePoint, named Clients and Client Contacts.

One of the benefits of using ECT association operations is that you get a nice external content picker control on new/edit forms in SharePoint. For example, when creating a new Contact in the Client Contacts external list, instead of just typing in the client_id, the picker pops up a list of all the records in the Client ECT for a much improved user experience.

When editing the forms using fd for the Client Contacts list, client_id (the foreign key column that is in the view and returned by the read list operation) is NOT available as a field to place on the form. Instead, a 'Client' field is present, which is the external content picker control mentioned above. The value when this control is set is an object, as opposed to just text. The internal name, in may case, is "bdil_Contact_x0020_Client_x0020_Association_Client" and it's Type is "Business Data", as opposed to "Text" or other data types.

This is where the problem comes in. When adding Client Contacts as a related list on a Client form, the Form Field 'client_id' is in the drop-down, but the List Field drop-down only contains the 'Client" external data picker field. Obviously, the client_id form field of type text will not be comparable to the Client form field of type Business Data.

To get around this problem, I simply added another client_id column to the underlying SQL view, but named cid. Now, I am able to filter related items where the Form Field client_id = the related list field cid.

The next problem to solve was prepopulating (and hiding) the Client external data picker control with the client_id when creating a New client contact from the related list. The following javascript accomplishes this:

Code: Select all

var cid = window.top.fd.field('client_id').value();
if (cid) {
	var cobj = {text:cid, dictionaryEntries: [{key:'', text:cid, client_id:cid}]};
  	fd.field('bdil_Contact_x0020_Client_x0020_Association_Client').value(cobj);
	$("div[fd_name='bdil_Contact_x0020_Client_x0020_Association_Client']").hide();
}

The first step is to get the client_id from the parent form. Then the trick is to construct an object that the external picker understands and set the value of the picker to this object. Lastly, hide the picker control on the form.

[AlexZver]

Hi Bob,

Nice work! I'm sure it will be helpful for the community.