Creating PowerApps with back-end SQL one-to-many relationships

[jared_simmons]

Hi, I'm having trouble finding an exact answer on how to design applications with the above Subject line in mind. I've looked over a couple different documents and resources on the web but I'm just not seeing where anyone is specifically addressing this methodolgy. Has anyone had implementations with this in mind? Can you share how you've managed to keep your IDs and keys consistent table to table? To paraphrase, I just want to know the following:

• What type of app is recommended for use? Canvas? Model-Driven? Does it matter?
• Do I need to be staging this data in a CDS on the PowerApps platform before using Patch() to SQL? Is that possible/recommended or am I misunderstanding what that is used for?
• I'm assuming that I should be able to take the parent ID generated on Table A and pass it to be used by my children records in Table B (one-to-many), what formula is recommended for this? I've seen users implement LookUp(), Last(), First(), etc. to do this but that can't be really it, right? Simulataneous user entry could zap that entire alignment and cross your records up...
  • Side question, does my SQL schema magically handle this? Would the PowerApp inteprept it somehow and know what to do with my records?

Thanks again, please link any resources that address this clearly if you have them. Shane and Pragmatic Works on YouTube have both come very close to answering this.

[jared_simmons]

One more thing to add, I have a couple projects that are pending on figuring out the best path forward for design around this idea, so if one of the msft accounts could give me a shout, or respond here, just would like to know what my possibilities are or if I just need to go down the path of traditional development to solve. At a time, I might need to update 2-3 tables together when submitting data from the PowerApp client... would prefer to use the PowerApp path considering cost, time, effort, and everything needed seems to be here, but I can't put my finger on how to proceed with this concept, as it is my only constraint right now.

[wyotim]

I work primarily with Azure SQL in PowerApps. I use canvas apps and no CDS.

For the parent/child relationship, it kind of depends on what you are doing. I mainly have had to design for offline app scenarios and it seems to me that having a slightly de-normalized structure for the SQL tables can help tie things together. How to go about that depends on your data structure and what you are trying to do with it. I mainly use LookUp() in those scenarios but Last() and First() can also work depending on how sorting and filtering are applied and, again, how your data is structured.

If you would like to share general details about the scenario(s) you are designing for I would be happy to dialog with you about it/them and I am sure we can reach the solutions you are after.

[jared_simmons]

Ok great! Yeah, so I have 6 tables, they might each have 3-15 columns in them. There is a need for a self-incrementing Parent ID to be generated on one table, call it a "REQUEST_ID" in this case, and there's also a need for another table to have a second self-incrementing Child ID, called it "SUB_ID". On 2 tables, I'm only using REQUEST_ID to dilneate, on the other 4, I have a compound key of both REQUEST_ID and SUB_ID.

I have different user groups iteratively making updates after one another in a process, so I don't think simulataneous writes and ID generation will take place here. I think for the most part, I can bring down the IDs to the client and use those to write back to the correct records, am I able to do this in a compound key situation? "Lookup(REQUEST_ID) && Lookup(SUB_ID)" so to speak?

Side question, have you ever done app level security from SQL too? As in assigning role sets in SQL and having the client interpret those records when a user signs in?

[wyotim]

That sounds like a great approach to take! To do a "compound lookup" if you will, you would use a statement similar to the following:

LookUp(
     DataTableOrCollection, 
     REQUEST_ID = Ref1 && SUB_ID = Ref2
).FieldYouWant

or

LookUp(
     DataTableOrCollection, 
     REQUEST_ID = Ref1 && SUB_ID = Ref2,
     FieldYouWant
)

The difference between the two can be useful. The first looks up the entire record and then references the field you designate. The second just looks up the designated field in the record. Subtle but there are times when it make a difference.

For your side question, I haven't done any app level security from SQL unfortunately. The closest I have come is to enable or disable features based on the Office365 username which is checked against an employee table in SQL but it isn't security-based at all, just differentiating between a field employee and an office employee and the features to give based on that.