HELP I don't know what to do!

[Chellybean72]

I wrote one time before. A great guy responded but I still don't exactly know what to do. I am completely new to Power apps.  I mean like 6 months ago I didn't know it existed lol Ok I am trying to do a app for a HVAC app. It will keep up with clients, Client equipment, timesheets and Work Orders. I can't get past this issue.

They will have main Client record

                      ➡️ Job site Locations (these are under the parent company. The could have 1 or 5 or whatever amount of sites.

                              ➡️ Contact Name, phone and Email. Could be one or multiple ones.  The Job sites and Contacts are one on                                              share point list

                                                  ➡️ Job Site equipment (each site could have multiple equipment)

                                                                ➡️ The equipment will possibly have Belts and Filters

Client record could be just one job site or multiple. I have the share point lists Client records, Client Equipment and Client Jobsites. Now what is the best way to hold all of this info. Right now I can get the Client to patch to the Jobsite but I am having issues with patching the equipment to the jobsite. If someone could help. I am desperate!  Or is there a better way to do it? I will need to draw from the client list along with the job sites and equipment when my customer does a work order. I appreciate any help.  

[cpadilla]

Hi!,

Apologies, but the information provided is a bit confusing for me, some additional information may help:

• what is the purpose of the app? what are you trying to achieve?
• what do you mean by "I can get the Client to patch to the Jobsite"? and "patching the equipment to the jobsite"?

I think the data hierarchy is clear Client-> jobsite/contact (one or more)->job site equipment (one or more). However, when dealing with multiple tables related, I strongly suggest using a relational database such as SQL server or Dataverse which helps a lot to visualize those relations in views (this translates into performance). If this is not possible and you need to stick with SP lists, then I suggest using as few tables as possible. Ask yourself if the equipment table could be translated into additional columns in the jobsite table.

[Drrickryp]

@Chellybean72 

It is better to use a real database like Dataverse or SQL but these are premium features and have an expense associated with them.  It is certainly possible to accomplish your goals with SharePoint but you need a good understanding of how to build the back end from SharePoint lists.  I suggest that you review my blog post about here about how to design and implement one, starting with the  overview.   https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Database-Design-Fundamentals-and-Power... 

The tables and the relationships between them are the foundation of a PowerApp.  Taking the time to properly design your tables (lists) and create the relationhips, will save you hours and days in trying to fix a poorly designed one. 

For more tips on how to avoid some common pitfalls with SharePoint, see my other blog post: 

https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/The-Ten-Commandments-of-SharePoint-in-... 

[Chellybean72]

The purpose of the app is to contain client info and to do work orders.

I can connect the client name with the job sites.  Sorry about being confusing!! Again, I am new don't know all of the terminology.  I have let's say McDonalds as the client.  McDonalds site 1 and McDonalds site 2 is on a Share point list called jobsites. The share point list jobsites is linked to the Client share point list by ID.  The next part I can't figure out is to link the equipment Share point list to the correct jobsites. 

I had originally tried to put the jobsites/contacts/equipment on one list but how would I add more then one equipment to one Jobsite. (IE: McDonalds > McDonalds site 1 > equip 1, equip 2 . McDonalds site 2 > equip 1, equip 2, equip 3) and each equipment will have filters and belts. 

Thank y'all I appreciate the help. 

[Chellybean72]

Ok I am setting up Dataverse Custom tables. I have a table for Clients, Jobsites (with contacts) and Equipment. So how do I tell it the parent/child relationship? Any suggestions on how to do the forms in the app? I appreciate y'all's help!

[cpadilla]

nice to hear you will be switching to dataverse!

To create the views, you will need a relationship between the tables, in summary:

• You need to define the relationship type of the tables. E.g. A client may have one or more jobsites (called one-to-many); following that idea, a jobsite may have one or more equipment. 
• When you are creating the relationship within dataverse you will need to define a column that relates to the tables. E.g. in client table you have an ID (should be a primary key), if you want to relate it to the Jobsites table, then in the Jobsites table you will need to create a column called "Client ID". This is done via the wizard available in Dataverse.
  
• More about relationships: Create a relationship between tables by using a lookup column - Power Apps | Microsoft Learn
• Once the above is completed, it is time to create the views, since Dataverse knows those tables are related, when you click on "create new view" it will display columns of both tables.
  
• From power apps perspective a view will be like a table. Since the join operation is done in the database, the performance will be much better 🙂

hope this helps