Entity Relationship 1:N in Model Driven Apps how does it works

[mariopdemarchi]

introduce myself: "I am No-Code guy at all and I need a little bit of help... :=).

I want to build an app to track  Vendor's invoices  that bring Training Classes.   Each invoice reference to a particular Training Class ID (Sence ID). I need not only register the invoice but also the list of participants that assisted to those training classes.  So I build two Custom entities in CDS:  "Invoices" and "Participant"     

Entity #1 "Invoices".  Primary Field is an autonumeric (I find it that will not hurt anybody this approach)  

I will use Dataflow to fill this entity with the inovice's info (I daily receive this info in a .xls list i.e: invoiceNumber, Vendor TaxID, Amount, SENCE ID, Date, etc..). 

Each entity record will have not only the primary field value but a unique value in a custom field called "InvoiceKEY" ( which is a concatenate of Vendor's TaxID and  InvoiceNumber that comes from the imported list on dataflow). 

Each entity record has a unique Training Class code (SENCE ID)  

Entity #2 "Participant". Primary field is an automumeric ( hidden field in forms) 

This entity has a lookup field "SENCEID" and other fields like , first name, last name participant, % attended, etc.. 

I created a 1:N relationship ( Invoices Entity – "Primary"; Participant entity "Related" ) 

The SENCE ID field in Related entity is a "lookup field". 

Entity "Participant" has  SENCE ID as Key field.  

Inside the Model-driven app I have to Area/Group/SubArea that provides the proper navigation with view / forms .   

Here is the dilema:!  After importing the invoice info into Entity #1 , when I go to the Participant entity and I want to add a new record , in the SENCE ID lookup field .. it shows a list of all the values of  "primary Field of Invoices", not the SENCE ID field which has the 1:N relationship!!. I need to be able to show me by the SENCE ID values!.  I must be doing something wrong with the understanding of entity relationship or do not what.!. I need help. 

(Don't tell me that SENCE ID in Invoice entity has to be the primary field!..i has to be another way..  Please.. :=)    

Thanks in advance !! 

[Drrickryp]

Hi @mariopdemarchi 

I'd like to help you with this but I am confused from your description.  I see that you have described two entities, Participant and Invoice.   What exactly are you looking to show?  Which entity is the One side and which is the Many side?  Can one participant have many invoices or can one invoice have many participants.  In your app do you want to show the participants in one invoice or do you want to show the invoices assigned to each participant?  BTW it is possible to use a different field other than the primary field as the key but it has to meet the requirement that it be without duplicates.  You may need to see the documentation on Alternate Keys https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/define-alternate-keys-entit... 

[mariopdemarchi]

Hi! thanks for coming to rescue me! :=) .  

Invoice (1) ; Participant (N). Entities relationship.  I should attach something to depict better the case (please see attachment).

In the Model-Driven App , after fill in with the vendor's invoices (stored in the Invoice Entity) , I add all the participant for each particular invoice; so I move to the "participant" entity, and the only way to reference the participant's list for a particular class is the field "sence ID" . (that's why I created a lookup field in the "N" entity.  ).    The Sence ID is part of the  the Vendor's invoice information , among price, etc. etc... 

Should I use a N:N relationship instead? . 

Thanks in advance!!

Mario

[Drrickryp]

@mariopdemarchi 

I have a passing knowledge of Alternate keys and in the model driven apps that I have created, I have always used the primary key as a lookup. When I tried the other way, I had to do it using a canvas app embedded in the MDA to make it work.   I would suggest getting @EricRegnier 's help here. 

[mariopdemarchi]

Ok. I am reading your suggestion about using Alternate Key... Don't know why but didn't see it your reply before.  If I need +help I will contact Eric. Thanks a lot !!! 

Regards, Mario

[EricRegnier]

Hi @mariopdemarchi ,

If I understand correctly, it seems like you have your 1-N relationship the wrong way. 

• Invoice entity. This is a unique invoice per customer/vendor right? Typically it should be.
• Participant entity. I assume this is like master data in your system and you maintain and use the same participant record if the participates in >1 training. Again, typically it should be.
• Your 1-N relationship should be Participant (1) -> Invoices (N). So the lookup field should be on Invoice. I don't think you need a N-N relationship.

For the alt keys, as @Drrickryp said, it's good practice to have some defined especially in your case for SENCE ID and Invoice Number. This will allow you to map these records in your Dataflows. 

Besides what I mentioned, did I miss anything regarding your issue?