Want to patch 2 different tables with one submit button

[SeanTambling]

I've been tweaking an app already deployed. I've added a couple screens for further functionality. See the image below labeled PipeLineScreensLayout.png. You'll see I have 3 screens. First screen selects a group from a gallery. Second selects a specific pipeline for that group from a gallery. The third screen allows you to edit a third gallery. 

In this third gallery called StageGallery (see imaged labeled: StageLvlSave.png), the user selects a date and then enters a comment and then when they press the save button, the OnSelect is set to:  Patch('[dbo].[tblPipelineStages]', ThisItem, {StageDate: StageDateDatePicker.SelectedDate}, {Comments: StageCommentsInput.Text}); Patch('[dbo].[tblPipeline]', PipeLineGallery1.Selected, {DateFinished: StageDateDatePicker.SelectedDate})

I've been using the first Patch statement and it works fine: Patch('[dbo].[tblPipelineStages]', ThisItem, {StageDate: StageDateDatePicker.SelectedDate}, {Comments: StageCommentsInput.Text})

But when I add this part:  ; Patch('[dbo].[tblPipeline]', PipeLineGallery1.Selected, {DateFinished: StageDateDatePicker.SelectedDate})

this part is failing and i'm not sure why. What am i doing wrong? I'm sure you will need to know some info and I'm glad to provide. Just not sure what all to give atm. 

All help greatly appreciated.

Sean

[v-xida-msft]

Hi @SeanTambling ,

Do you add the "Save" icon into your StageGallery?

Could you please share more details about the error message with your second Patch formula?

Further, does the second Patch formula execute fail? Or the selected date value is not saved back to your '[dbo].[tblPipeline]' as expected?

Based on the Patch formula you provided, I could not find any syntax error with it. If you just want to update specific record in your '[dbo].[tblPipelineStages]' table and '[dbo].[tblPipeline]' table, please consider modify your formula as below:

Set(SelectedDateValue, StageDateDatePicker.SelectedDate); 
Patch(
       '[dbo].[tblPipelineStages]', 
        ThisItem, 
        {
           StageDate: SelectedDateValue,
           Comments: StageCommentsInput.Text
        }
);
Patch(
      '[dbo].[tblPipeline]', 
      LookUp('[dbo].[tblPipeline]', 'Primary Key Column' = PipeLineGallery1.Selected.'Primary Key Column'), // find the specific record you want to update via the 'Primary Key' column
      {
         DateFinished: SelectedDateValue
      }
)

Please make sure that you have defined a proper 'Primary Key' within your '[dbo].[tblPipeline]' table.

In addition, there are some known limits with SQL Server connector, please refer to the following article:

https://docs.microsoft.com/en-us/connectors/sql/#known-issues-and-limitations

Best regards,

[SeanTambling]

@v-xida-msft 

Thank you for your help before. I've had some other problems with the app on this same screen that I felt i needed to fix first before tackling this further.

Your idea of using a variable works and I really appreciate your help. I was hoping to take this a little further if possible. I still want to patch the same 2 tables but I need to further evaluate(not sure that's the right word) the second table i want to patch.

In this example below i get an error on all of the last patch. The error states , invalid number of arguments, received 4, expected 2-3. Is it possible to rewrite this so that it will work?

Set(SelectedDateValue, StageDateDatePicker.SelectedDate);

Patch('[dbo].[tblPipelineStages]', ThisItem, {StageDate: SelectedDateValue}, {Comments: StageCommentsInput.Text});

Patch(
'[dbo].[tblPipeline]', 
LookUp(
'[dbo].[tblPipeline]', ID = PipeLineGallery1.Selected.ID, If(LookUp('[dbo].[tblPipelineStageTypes]', ID = StageGallery.Selected.PipelineStageTypeID,!IsEmpty(EndStage))) , {DateFinished: SelectedDateValue});

Refresh('[dbo].[tblPipelineStages]');UpdateContext({VisibleVar:false})

What i'm trying to accomplish is this.

For the last patch, i want to update the Pipeline table and the DateFinished Column with the variable "SelectedDateValue", but only where the ID's are the same for the selected PipelineStageGallery and the PipelineStageType table (there are PK's for this) , and If EndStage(this is a check box in the table) is not empty. I used IsEmpty, but maybe I'm trying to go about it the wrong way. I'm still so new to this I can get overwhelmed trying to figure out the logic.

Any help appreciated!

Sean