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. 

[Pstork1]

I didn't create 3 more child tables off the project Design table.  If you are setting the foreign keys correctly it should still work.  But since those are additional relationships from the Project Design table and they are required, if they aren't correct then that may still be causing a problem.

[krdev32]

@Pstork1 

@Pstork1 the foreign keys that I have required is the MapStatus, Department, and Area which I removed and still get the error. 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 also have update cascade on for the foreign keys on the ProjectDesign table. I'm trying to find why I keep getting this error. Only having issue with the ProjectDesign table only editing. 

[Pstork1]

What are the constraints that you are putting on created and modified dates?

[krdev32]

@Pstork1 so just to troubleshoot, I deleted all columns and just had PD,TEDDesign, and work order to see if it work with changes and it did. then I add the foreign key Area (which is a lookup table) to the project design table and try to edit and i get that error. So it seems it is my foreign key that is causing that error. What is weird is that I have a employee table with a foreign key employeetype and I'm able to submit and make edits with a form with no problem. The constraints I have is defaults 

[Pstork1]

Did you also remove the constraints on CreatedDate and ModifiedDate?  Your screenshot still shows them.  Are the keys the same data type in the other table as the employeetype?  And is employeetype required?

[krdev32]

@Pstork1 So I did a troubleshoot and the trigger for the ModifiedDate is the cause of the error when trying to edit the ProjectDesign Form. I can now make changes after I deleted the trigger. Is there a way to have the ModifiedDate updated when there are changes to the ProjectDesign table? Without having this error occur? Is there something wrong with the sql code that is causing this error by chance?

[Pstork1]

As I mentioned earlier I don't think the connector is designed to support SQL tables that use SQL triggers.  If you need to update the modified Date when a change is made outside of the Power App you could add a Power Automate flow that would trigger whenever a record in SQL is modified and update the date field that way.

[krdev32]

@Pstork1 Ok thanks. I really appreciate your help. Do you know how to do that or show me how to do that, the Power Automate flow? Also how do you clear the datepicker? For example, if the user accidentally saves the date and wants to go back to the form and clear the date and save it, how can I keep that date clear when update and clear the date? 

[krdev32]

@Pstork1 Also do you know how to clear the datepicker? For example, if the user accidentally saves the date and wants to go back to the form and clear the date and save it, how can I keep that date clear when update and clear the date? 

[Pstork1]

Once a date has been entered I don't think there is a way to completely clear it back to null. ONce a date is entered you can easily change it, but you can't set it back to blank.