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]

Here is the current code I have to submit the data to the Project and ProjectDesign table (submit button On select):

Collect(RequestList,{PD:PDTextInput1.Text,ProjectDesc:ProjDescTextInput.Text,SummerCritical:SCritical.Value,SummerCriticalDate:SCDate1.SelectedDate,TEDDesign:TEDTextInput.Text,WorkOrder:WOTextInput.Text,Department:DeptDP1_1.Selected.Department,Area:AreaDP.Selected.Area,FirstCutDate:FCDDate.SelectedDate,FinalSwitchingDate:FSDDate.SelectedDate,AirportLocation:ALToggle.Value,ConfigurationChanges:CCToggle.Value,MapStatus:StatusDP.Selected.MapStatus,CreatedDate:CDate.SelectedDate,ModifiedDate:MDate.SelectedDate,Requestor:ReqTextInput.Text,RequestorPhone:PhTextInput.Text,RequestorDesc:DescTextInput.Text});

Refresh(Project);
Refresh(ProjectDesign);
Set(
ExistingPD,
LookUp(
Project,
PDTextInput1.Text in PD//[@PD]
)
);

Patch(
Project,
Defaults(Project),
{
PD:PDTextInput1.Text,
ProjectDesc:ProjDescTextInput.Text,
SummerCritical:SCritical.Value,
SummerCriticalDate:SCDate1.SelectedDate
}
);

If(
IsBlank(ExistingPD),
Collect(
RequestList,
ShowColumns(
Project,
"PD",
"ProjectDesc",
"SummerCritical",
"SummerCriticalDate"
),
ProjectDesign,
"TEDDesign",
"WorkOrder",
"Department",
"Area",
"FirstCutDate",
"FinalSwitchingDate",
"AirportLocation",
"ConfigurationChanges",
"Requestor",
"RequestorPhone",
"RequestorDesc",
"CreatedDate",
"ModifiedDate"

);
ForAll(
RequestList,
Patch(
ProjectDesign,
Defaults(ProjectDesign),
{
PD:LookUp(Project,PD=PD,PDTextInput1.Text),
TEDDesign:TEDTextInput.Text,
WorkOrder:WOTextInput.Text,
Department:DeptDP1_1.Selected.Department,
Area:AreaDP.Selected.Area,
FirstCutDate:FCDDate.SelectedDate,
FinalSwitchingDate:FSDDate.SelectedDate,
AirportLocation:ALToggle.Value,
ConfigurationChanges:CCToggle.Value,
MapStatus:StatusDP.Selected.MapStatus,
CreatedDate:CDate.SelectedDate,
ModifiedDate:MDate.SelectedDate,
Requestor:ReqTextInput.Text,
RequestorPhone:PhTextInput.Text,
RequestorDesc:DescTextInput.Text
}
)
);

);
Reset(PDTextInput1);Reset(ProjDescTextInput);Reset(SCritical);Reset(SCDate1);Reset(TEDTextInput);Reset(WOTextInput);Reset(DeptDP1_1);Reset(AreaDP);Reset(FCDDate);Reset(FSDDate);Reset(ALToggle);Reset(CCToggle);Reset(StatusDP);Reset(CDate);Reset(MDate);Reset(ReqTextInput);Reset(PhTextInput);Reset(DescTextInput);
Navigate(RequestListScreen,ScreenTransition.Fade)

[krdev32]

@Pstork1 

[Pstork1]

You can't point a form at more than one data source.  You can have a screen with more than one form tied to different tables using some relationship to load the different forms.  Or if you have a SQL view that is built from the two tables you can bind a form to that view.

[krdev32]

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 you help me with the codes? I have submitted images above so you can see. I also posted the formula to submit the data to make sure that code is correct. Any help appreciated.

[Pstork1]

If you have a one to many relationship between Project and Project design then the best way to work that out in Power Apps is to have a Gallery where you select the Project and another Gallery that shows the Project Designs filtered by the selected Project in the first Gallery. Then you can add a form for Project and a form for Project Design and edit the selected Project or Project Design.

[krdev32]

@Pstork1 Ok so I have it where I have two galleries. The Project gallery, I have it were the user can click the edit icon and it goes to a screen with the form to edit the Project table. I am able to edit the Project form and submit. Now, below the Project Form I have a gallery showing the ProjectDesigns associated with that Project as shown in the image below. 

When the user clicks on the edit icon (code: "Set(varDesign, ThisItem);ResetForm(DesignForm_1);ViewForm(DesignForm_1);Navigate(DesignScreen_1)") shown in the gallery (in the image above) it goes to a screen to edit that record which I'm using a form called DesignForm. For the DesignForm Datasource I have ProjectDesign and for Item I have varDesign. When I try to edit and submit, it doesn't work. 

The code for the yellow Update button to edit and submit the ProjectDesign data is in the image below. Can you help me with the code? FYI, the PDDataCardValue43 is from the Project Form.

Set(
ExistingPD1,
LookUp(
Project,
PDDataCardValue43.Text in PD
)
);
Patch(
ProjectDesign,
varDesign,
{
PD: LookUp(
Project,
PD = PD,
PDDataCardValue43.Text
),
TEDDesign: TEDDataCardValue43_1.Text,
WorkOrder: WODataCardValue44_1.Text,
Department: DeptDropdown2_1.Selected.Department,
Area: AreaDropdown2_1.Selected.Area,
FirstCutDate: FCDDataCardValue54_1.SelectedDate,
FinalSwitchingDate: FSDDataCardValue55_1.SelectedDate,
AirportLocation: ALDataCardValue56_1.Value,
ConfigurationChanges: CCDataCardValue57_1.Value,
MapStatus: StatusDropdown_1.Selected.MapStatus,
CreatedDate: CreatedDate2_1.SelectedDate,
ModifiedDate: ModifiedDate2_1.SelectedDate,
Requestor: RequestorDataCardValue60_1.Text,
RequestorPhone: PHDataCardValue61_1.Text,
RequestorDesc: DescDataCardValue62_1.Text
}
);

[Pstork1]

1) You can edit the Project Design using the same kind of code that you use for the Project. YOu don't need to save the record as a variable. Just use the Gallery.Selected property in the form.  Then in the edit icon use Select(Parent) to set the selected record for the Gallery.

2) I suspect your other problem is that you are using ViewForm() instead of EditForm(). You need to use EditForm or the fields on the Form will be readOnly

[krdev32]

@Pstork1 Ok the ProjectForm Datasource is Project and the Item is ExistingPD1. The code for OnSelect button to submit this form is just SubmitForm(ProjectForm). This allows me to edit and submit the form with the no problem. Now, for the DesignGallery showing the many designs, I changed the edit icon OnSelect code to "Select( Parent);ResetForm(DesignForm_1);EditForm(DesignForm_1);Navigate(DesignScreen_1))."

For the DesignForm Datasource, I have ProjectDesign and for Item I have changed from varDesign to DesignGallery.Selected. The code for the submit button OnSelect for the DesignForm is SubmitForm(DesignForm_1). When I click submit I get an error stating in the image below. What does this mean?

[Pstork1]

to Edit or add new records to a SQL database from Power Apps the database table has to have an identity column.  Take a look at this article that describes the issue.

Power Apps Guide - SQL - What to do when edit/add/delete options missing from an app, or edit contro...