Create form two sql tables

[krdev32]

I have two SQL tables (image SQL Tables) Project and ProjectDesign. Project is the parent table and Project can have many ProjectDesigns. I'm trying to create a form with data from both of these tables. First question, can I use a regular edit form and add the textboxes, labels, etc independently to the form? If so, what would the code for the DataSource and item be?

Second question, currently I just independently added textboxes, data picker, etc to a screen and setup to look like a form and have a submit button with a code which is attachment called code and I'll post the code too. This code allows me to submit to both tables Project and ProjectDesign. However, I'm having trouble with showing the data from what is submitted. The RequestListScreen has a gallery and I currently have the Items as "SortByColumns(Filter(ReqView,StartsWith(PD,PDSearchBox_3.Text),StartsWith(PD,WOFilterText.Text)),"CreatedDate",SortOrder.Descending)." ReView is actually a view from SQL statement I created so I can see all the data that has been submitted. This isn't what I want. I need to be able to edit the data as well and view doesn't allow you to edit. I'm trying Collect to get the data from both tables Project and ProjectDesign but it is showing duplicate (see RequestListScreen image). The code is shown in the image RequestListScreen for the gallery. The gallery Item is collection name: "RequestList". I have a button to get the data in the collection: "Collect(RequestList,ShowColumns(Project,"PD","ProjectDesc","SummerCritical","SummerCriticalDate"),ShowColumns(ProjectDesign,"TEDDesign","WorkOrder","Department","Area","FirstCutDate","FinalSwitchingDate","AirportLocation","ConfigurationChanges","Requestor","RequestorPhone","RequestorDesc","CreatedDate","ModifiedDate"));". With this code, its showing duplicate data. Can someone help me with the codes? 

I also have another screen called ViewFormScreen. This is where from the gallery in the RequestListScreen, the user will be able to select the edit icon to edit that record. The code for the edit icon is "Set(ExistingPD, ThisItem);ResetForm(ViewForm);EditForm(ViewForm);Navigate(ViewFormScreen)". In the ViewFormScreen, I have a form with the Item set to "ExistingPD", and I have the Datasource as the collection name "RequestList". This isn't showing all the data on the form within the collection. The form isn't made independently. I actually used the edit form to view the data from the gallery. From the ViewFormScreen I'm trying to edit and save the record back to Project and ProjectDesign but I can't. The formula that I have to save back to the collection is "Patch(
RequestList,
requestGallery_3.Selected,
{
PD:PDTextInput,ProjectDesc:ProjDescTextInput1,SummerCritical:SCToggle,SummerCriticalDate:SCDate2,TEDDesign:TEDTextInput1,WorkOrder:WOTextInput1,Department:DeptDP1,Area:AreaDP1, FirstCutDate:FCDate, FinalSwitchingDate:FSDate,AirportLocation:ALToggle1,ConfigurationChanges:CCToggle1,Requestor:ReqTextInput1,RequestorPhone:PhTextInput1,MapStatus:StatusDP1,CreatedDate:CreatedDate,ModifiedDate:ModifiedDate,RequestorDesc:DescTextInput1
}
);
Navigate(RequestListScreen);".

Can someone help me with this code as well. 

[krdev32]

@Pstork1 I don't see the link of the article? What article are you suggesting? I have no probelm with the Project table to edit or adding new records.  Does it have something to do with how the ProjectDesign table is structured? See image below. Primary Key is concatenated PD and TEDDesign. Also I can add or create a new design, I just can't edit and submit what has been edit. 

[Pstork1]

I edited the reply to add it.

[krdev32]

@Pstork1 Thanks for that article. Very informational, however, it doesn't help me with my situation. I'm able to add a design with no problem, but editing I get that error. I have the primary key which is a composite key PD and TEDDesign set in the ProjectDesign table. It doesn't tell me what to do to fix the error with the composite key.

[Pstork1]

When you say you can add a design do you mean you can add one through Power Apps or just through SQL? If you can add one through Power Apps with a form then I suspect its the difference between a New Form, which just needs the default values, and an Edit Form which needs the actual record to be edited. That is the problem presented by composite Primary Keys.  But I would need to see your actual table schemas and relationships to figure out how to get the Edit form to work with a composite key.

[krdev32]

@Pstork1 I can add a Design through PowerApps. Do you need to see all the tables? The main table or the Parent table is the Project table and the child is ProjectDesign. Is the image below good enough? What image do you need to see? I also have defaults for created date and modified date, and trigger to update modified date when the record is being updated. All the foreign keys in the ProjectDesign table are just lookup tables except for the FK_ProjectDesign_Project_PD. 

[Pstork1]

I haven't worked with any SQL tables with composite keys in a while. Let me take a look and see if I can get it to work and I'll see if we can get it to work in yours.  But I see one other issue you will face. SQL table triggers aren't supported when working with a Power App. So if you do get the changes to save SQL will not trigger to update the modified field the way you want it to.

[krdev32]

@Pstork1 Sounds good appreciate your help. Below is info for Project table:

Columns info for the ProjectDesign table:

[krdev32]

@Pstork1 Hi any update on getting it to work?

[Pstork1]

I just finished and here is what works for me.

Project Gallery Setting

     - Items: Project

     - Edit Button.OnSelect:  Select(Parent);EditForm(ProjectForm)

ProjectForm

    - DataSource: Project

    - Items: If(Self.Mode=FormMode.Edit,ProjectGallery.Selected, Defaults(Project))

    - Add New Button.OnSelect: NewForm(ProjectForm)

   - Save Button.OnSelect: SubmitForm(ProjectForm)

Project Design Gallery Setting

     - Items: Filter(ProjectDesign, PD=ProjectGallery.Selected.PD)

     - Edit Button.OnSelect:  Select(Parent);EditForm(ProjectDesignForm)

ProjectForm

    - DataSource: ProjectDesign

    - Items: If(Self.Mode=FormMode.Edit,ProjectDesignGallery.Selected, Defaults(ProjectDesign))

    - Add New Button.OnSelect: NewForm(ProjectDesignForm)

   - Save Button.OnSelect: SubmitForm(ProjectDesignForm)

I didn't make all the foreign keys you have on the Project Design table and that may be the source of your problem since some of those are required.  But in general hitting either edit or add and then Submit after filling out the form worked for both tables.

[krdev32]

@Pstork1 the foreign keys that I have required is the MapStatus, Department, and Area. Are you saying you didn't make these required? Also how did you handle the created date and the modified date with the ProjectDesignForm? How can the modified date update when the form is modified? I'm still getting the same error after removing the foreign keys MapStatus, Department, and Area being required. Can I see your sql table on how you structured it? Did you have createddate and modifieddate as default in sql? I'm trying to find why I keep getting this error. Only having issue with the ProjectDesign table only editing.