PartyList-type lookup

[sean_dv]

Hello all

I am creating a Case Management System for complaints between members of my community-based organisation. I am using Microsoft Dataverse to build a model-driven PowerApp.

I have a table  of complaints, and a table with the member's details (e.g. membership details, contact info, etc). I need a lookup column field in the complaints table that can select multiple rows  from the member's table. (For example, John Smith lodges a complaint about Jane Doe and Joe Bloggs).

I found two Microsoft Articles that mention this feature:

1. "Partlist-type lookup" - see Use the lookup column on a row - Power Apps | Microsoft Docs

2. "Multi-table lookup" - see Use multi-table lookup columns (Microsoft Dataverse) - Power Apps | Microsoft Docs

There is a note on Article 2 which states "At this time users can create and modify custom multi-table lookups via the SDK or Web APIs. Interactive user interface support will be coming in a future release."

Does anyone have any information about when the party-list type lookup will be rolled out? I am not a developer, and I am not able to use coding such as SDK or Web APIs.

I want to be able to set up an Instant Cloud Flow, to get the data from the lookup field to populate a word document. So in the example above, they need an individual letter (one for John Smith, one for Jane Doe and one for Joe Bloggs). Will the Flow be able to get data from a "Partlist-type lookup" field?

I have tried using a subgrid but I'm not able to integrate the member details into the table views, table forms, or flows.

I am not beyond paying for technical help but I'm also unsure how to find an expert that can help with PowerApps and Dataverse. I am in Melbourne, Australia. Any suggestions?

Please help!

Thanks in advance. 

Sean

[gowrihalan04]

@sean_dv,

For your scenario, you can create a sub grid to members tables(create n:1 relation with complaint).  Currently we dont have multi select lookup field in the system.  

Party list are type of lookup which already available in the system (one example email entity To, From). This is meant for activity entity only.

[sean_dv]

@gowrihalan04 

Thanks for your reply.

I've tried adding two subgrids to the Form, (so I can differentiate between the complainant and the respondent). The system automatically generates a many-to-many relationship (N:N) and I can't override this. 

I can see the related records in the Form; however, when I go back to the Table View, I can't add Columns (e.g. the name column) from the related subgrid records. This will only work if I use a lookup field (which limits me to one person per lookup).

I also can't get the subgrid records to work with my cloud Flow to populate word documents.

Can a technical person code for a party-list look up in a standard (non-activity) table?

Thanks again,

Sean

[dpoggemann]

Hi @sean_dv ,

I understand your need here but was thinking through this more and wondering if an approach like the following would better meet your needs...

I use this type of strategy for most situations because it gives me the most flexibility.  The Complaint would have the submission user as a lookup column to the Member table and then you can add a tab or subgrid on the main form to the Complaint Member table where you can add multiple members that are related to the complaint.  Additionally you can add any number of additional fields to that table for metadata definition on this complaint member (i.e. you have an interview with that member and want to track this as an activity related to that record).

[ben-thompson]

@dpoggemann 's approach is the correct one here - you want a single intersect table which will allow you to join the compliant and members tables together alongside any other appropriate fields (complainant / respondent) and other information alongside any other information that may be useful...

[sean_dv]

@dpoggemann 

Hi Drew

Thanks for your reply.

I'm not sure I understand your suggestion. So is that three separate tables? 

1. Complaints table
2. Complainant table (person making the complaint)
3. Respondent table (person the complaint is a about)

Members can be complainants or respondents, and I don't want to have duplicates of the same person in different tables. When I go to the person I want to see all complaints made by them and about them. 

Our Complaints process is a lengthy one, several months long involving several stages, so I've added a Business Process Flow to the Complaints table to track the stages. 

Do you know of any example Model Driven Power Apps for complaints/case management systems you could recommend I look at?

[ben-thompson]

It can be 3 separate tables 

1. Complaints table
2. Complainant table (person making the complaint)
3. Respondent table (person the complaint is a about)

but it could equally be 2 tables with a role column listing their role in the particular complaint.

1) Complaint table

2) Complaint party table (with a column specifying whether they are the complainant or respondent)..

For display purposes you then just create a couple of additional views to display just the complainant records or just the respondents.

As for which solution is the appropriate one - I would think about the rest of the business logic that sites at the complainant / respondent level and depending on how much of it is identical I would decide on whether 2 or 3 tables were more appropriate...