cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcairney
Advocate I
Advocate I

Many to Many Table Relationships in SQL Server DB

I have a database where there is a many to many relationship between "List Name" in Table A and "List Member" in Table B.  I have a joining table that maps members to lists since a member can belong to many lists (call this Table C).

 

Currently, PowerApps doesn't support Views therefore I cannot do an easy filter to show the list of List Names on one screen and then go to the members of that list on the next screen .... I can get the ListID from Table A from the first screen but then that ID doesn't exists in TableB ... only in TableC where it maps to many MemberID values.

 

Is there a work around in PowerApps where I can click on a list name and then get the list of members?   I need this to be a delagated query to SQL Server since I may have more than 500 members in total and so I cannot pull them all back and filter within PowerApps.

 

Alternatively - please provide an update as to when Views will be supported.

12 REPLIES 12
Anonymous
Not applicable


@Meneghino wrote:

I would suggest the AddColumns and save changes with a Patch()


 

Thank you for advice. This is what I did to solve the problem. I display the contact names in a gallery by using the AddColumns function. Then I use the ContactIDs in the gallery to display and save the contact.

 

Here are the formulas:

 

Browsescreen
AddColumns(Filter('[dbo].[Customer_CONTACT]', CustomerID = CustomerKey), "FullName", LookUp('[dbo].[CONTACT]', ContactID = CustomerContactID).FullName, "ContactID", CustomerContactID)

 

Detailscreen

LookUp('[dbo].[CONTACT]', ContactID = BrowseGallery_Contact.Selected.ContactID)

 

Editscreen

SubmitForm(EditForm_Contact); If(EditContact = false, Patch('[dbo].[Customer_CONTACT]', Defaults('[dbo].[Customer_CONTACT]'), {CustomerID: CustomerID, PrimaaryContact: 0, Email: EmailAddress}))

 

Regards,

Kevin

 

Delid4ve
Impactful Individual
Impactful Individual

Since you guys seem to be on a similar sort of page here i thought id ask your advice:

 

I have 3 Tables:

Equipment: ID,Make,Model,Serial

EquipmentLink: ID, EquipmentID, Job ID

Job: ID,JobNumber

 

I need a dropdown list to select a piece of equipment that is linked to a job (text box that is populated with the job number on form)

 

So I need to first pull the Job ID from the Job Table, (Can do)

Filter equipmentlink by the JobID.

Pull all the EquipmentIDs from the previous step

Populate my Dropdown with the equipment.

 

I have spent 24hrs, literally trying to achieve this now and just dont know where to start.  This was so easy in access with queries and dlookup.

 

Any help greatly appreciated.

Meneghino
Community Champion
Community Champion

Hi @Delid4ve

Once you get used to the syntax, you will find that PowerApps is even easier and more flexible than Access.  I am now a convert.

The syntax that you need for the Items property of the equipment dropdown is the following:

Filter( Equipment, ID in
     Filter( EquimpmentLink,
          JobID = LookUp( Job,  JobNumber = TextBoxJobNumber.Text, ID)
          )
     )

This is assuming that JobNumber is text, but if it is an integer, then you need use Value () around TextBoxJobNumber.Text

 

There are a few things to keep in mind.

  1. Delegation: the 'in' operator is not (yet!) delegated for SQL Server (even though the documentation says it is).  This means that only the first 500 records of the filtered EquipmentLink table will be queried.  Unless you have more than 500 pieces of equipment for any one job, then this should not be an issue.
  2. Performance: the above expression requires the concurrent querying of three tables.  Given that the 'in' operator is not delegated, then performance may be slow, and performance may be so slow that the dropdown does not get populated.  This is easily resolvable by caching some or all of the tables in memory.  This will also be useful in other parts of your app.

In any case, please try the above and let me know how you get on.  If caching is needed, this is really easy, just basically place the following as the OnStart property of the first screen:

ClearCollect(CachedEquipment, Equipment); ClearCollect(CachedEquipmentLink, EquipmentLink); ClearCollect(CachedJob, Job)

This will work if you have <= 500 rows in each of these tables.  If you have more than 500 then there is an easy work-around that I can point you to.

 

Then of course, you have to substutitute the Cached version of all data sources in the expression for the Items property of your drop down.

 

PS Don't forget that the OnStart actions only get triggered once when the app is opened, so you will have to close and re-open the app after you place the expression in the OnStart property.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (39,942)