Select multiple lookup items and add to custom N:N entity in Dataverse tables

[beckabob2003]

Greetings.

I need to keep track of volunteer certificates for a volunteer app I am creating. Our need is to list certifications for our volunteers and track when they expire (Think First Aid, CPR, and items like Photo Releases, etc)

I set up our table of volunteers and our table of certificates.  The certificate table has things like the certificate name (CPR) and Validity time (365 days).

I then created a custom N:N table that contains Lookup to Volunteer (N:1), Lookup to Certificate (N:1), Date Obtained and expiration date. My intent was to create some business process to automatically calculate the expiration date based on the Validity length and date obtains, and then do some cool dashboards for certs that are expiring in the next 30 days etc.

All of that is going well.  My test users like what they see but remarked that it's difficult to add the 10 or so (or sometimes more certificates that a volunteer will need one at a time.  They asked if there was anyway they can search for and select multiple certificates to add to the N:N table?

I thought that was going to be an easy ask but can't seem to find a way of doing that without breaking my custom N:N relationship.  I would think that I could maybe just add another subgrid of all the certificates, have the user select the ones they need, and then somehow click a button to kick off a row-add process....

I am relatively new at Canvas apps but I am open to any suggestions including custom PCF, canvas apps or some OOB method I might have missed.

Thanks so much!

[WarrenBelz]

Hi @beckabob2003 ,

You will need to include the actual linking fields between the two Lists

Ungroup(
   ForAll(
      Volunteers As aVol,
      AddColumns(
         Filter(
            Certificates,
            LinkFieldName1 = aVol.LinkFieldName2
         ),
         "VolName",
         aVol.Volunteer
      )
   ),
   "Value"
)

You can add back any other fields from Volunteers with aVol.FieldName.

I would urge you to throw away those Lookup fields - they will cause you a lot of unwanted and unnecessary grief/

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[beckabob2003]

Hello @WarrenBelz  and thank you for the quick response.

I am not married to the lookup columns by any means so don't mind removing them.  But to clarify, are you saying to keep it a custom N:N table and convert the lookups to text (maintaining the N:1 relationships between Volunteers and Certificates) or doing something different where the N:1 relationships are removed as well?

As for the code, I understand a little about what it's doing but not understanding why and how to implement it.   Why does ungrouping the columns help with creating a picklist and how do I implement this code?

Sorry so far everything I've done in Model Driven apps has been codeless although I understand quite a bit of it.  Can you point me in the right direction?

REALLY appreciate the help.

[WarrenBelz]

Hi @beckabob2003 ,

Before I make any other assumptions here, what is your data source (I assumed SharePoint when responding)

[beckabob2003]

Oh no! I should have said ...

Dataverse. In fact it's the volunteer management solution with custom tables.

Sorry @WarrenBelz that should have been in my opening monologue.

Thanks

Bob

[WarrenBelz]

Thanks @beckabob2003 ,

Sorry, I am SharePoint only - please disregard my comment on lookup columns as well - that only applies to SharePoint.

[beckabob2003]

No problem at all. I'll just wait for one of the other smart people to join in😁

Thanks so much for trying @WarrenBelz!

[beckabob2003]

Hello experts.  Any other thoughts on this?

[PaulD1]

I would advise against using N:N Dataverse relationships. Create a 'real' set of tables/relationships (2 parent tables and one child/intersect). In my (admittedly limited) experience, the Dataverse 'extensions' to traditional db modelling (N:N, Polymorphic, Option Sets) are hugely problematic and best avoided if possible. 

[beckabob2003]

Hi there PaulD1! Thanks for joining in.

So if I understand you I think that's what I did by creating a custom table as the intersect. It has a 1:N from the first table and also a1:N from the second. I had to do that in order to add in the extra information that I want to track.. The only difference is the intersect had look ups back to both tables. I did try doing what I want without the lookups but in the same boat. Did I misunderstand?

I can't seem to find a way of selecting multiple records from the certificate table, click a button and add them to the intersect table with the volunteer name.

If this were SQL is just use a select into command...

Thanks again for the feedback!