Unanswered

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

(0) Share

Report

Posted on by rlanger

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!

Categories:

General Questions

Connecting to Data

I have the same question (0)

All responses (10)

Answers (0)

WarrenBelz 151,875 Most Valuable Professional on at

Like (0)

Report
Re: Select multiple lookup items and add to custom N:N entity

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

Was this reply helpful? Yes No
rlanger 10 on at

Like (0)

Report

Re: Select multiple lookup items and add to custom N:N entity

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.

Was this reply helpful? Yes No
WarrenBelz 151,875 Most Valuable Professional on at

Like (0)

Report

Re: Select multiple lookup items and add to custom N:N entity

Hi @beckabob2003 ,

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

Was this reply helpful? Yes No
rlanger 10 on at

Like (0)

Report

Re: Select multiple lookup items and add to custom N:N entity

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

Was this reply helpful? Yes No
WarrenBelz 151,875 Most Valuable Professional on at

Like (0)

Report

Re: Select multiple lookup items and add to custom N:N entity

Thanks @beckabob2003 ,

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

Was this reply helpful? Yes No
rlanger 10 on at

Like (0)

Report

Re: Select multiple lookup items and add to custom N:N entity

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

Thanks so much for trying @WarrenBelz!

Was this reply helpful? Yes No
rlanger 10 on at

Like (0)

Report

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

Hello experts. Any other thoughts on this?

Was this reply helpful? Yes No
PaulD1 2,914 on at

Like (0)

Report

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

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.

Was this reply helpful? Yes No
rlanger 10 on at

Like (0)

Report

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

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!

Was this reply helpful? Yes No
PaulD1 2,914 on at

Like (0)

Report

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

Sorry beckabob2003, I though you were using the Dataverse N:N 'feature'.
My knowledge of Dataverse is limited, just enough to know that I find super frustrating and massively inferior to SQL. There is a Dataverse subforum here so it might be worth posting the question there as well.
Reading your question again, if this is a canvas App and you have a Gallery with the certificate options, you could add a checkbox (so user can select multiple rows) then have a button to kick of a ForEach based on Gallery.AllItems and check row by row to see which records are checked and then patch that record to the intersect table.
As ever with solutions like this, be wary that you cannot (AFAIK) wrap the patches into a transaction such that either one fails or all fail, so you'll need a way to deal with that when it happens (maybe a confirmation screen to show which certificates were added so the user can see if one or more failed).

Was this reply helpful? Yes No