cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Re: Many to Many Table Relationships in SQL Server DB


@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
Level 10

Re: Many to Many Table Relationships in SQL Server DB

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
Level 10

Re: Many to Many Table Relationships in SQL Server DB

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
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (7,024)