cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SeanTambling
Helper IV
Helper IV

Need help with a patch statement when selecting a save button inside of a gallery item

 

I have an app that i've been working on for a while. The app is connected to a SQL database. The app has 3 screens. First screen is a gallery of "Groups" or companies. OnSelect of a group, you are navigated to another gallery screen with a list of the available "Pipelines" assigned to them. OneSelect of a specific pipeline, you are navigated to the last gallery screen with a list of the "Stages" the selected pipeline. There are 8 stages for each pipeline. Each stage has a date and comment field that are editable and OnChange make a Save button visible. 

On the StageGalleryScreen, each gallery item is a different stage from 1-8. Each stage when edited (OnChange) will need to Patch at least 1 table and possibly 2. The Stage Type table has an additional column named "EndStage". This column is a check box (bolean true/false). 

I'm working with a total of 3 tables here, but only need to patch 1-2 of them. For the formula of the OnSelect of the save button, I have this formula written. 

 

 

Set(SelectedDateValue, StageDateDatePicker.SelectedDate);

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

Patch(
'[dbo].[tblPipeline]', 
If(LookUp(
'[dbo].[tblPipeline]', ID = PipeLineGallery1.Selected.ID)) && If(LookUp('[dbo].[tblPipelineStageTypes]', ID = StageGallery.Selected.PipelineStageTypeID && EndStage = true)) , {DateFinished: SelectedDateValue}); Refresh('[dbo].[tblPipelineStages]');UpdateContext({VisibleVar:false})

This section of the formula above is giving me an error:

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

 

The error says: The function "Patch" has some invalid arguments: Invalid number of arguments: received 1, expected 2 or more

 

 

I could use help writing this formula correctly. I'm guessing i have something out of order, or i guess i can't use the double lookup or i'm trying to do it incorrectly.

 

Any and all help greatly appreciated.

 

Take care,

Sean

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @SeanTambling ,

Could you please share a bit more about your scenario?

Do you want to update the record within your '[dbo].[tblPipeline]' table?

 

Based on the formula you provided, I think there is something wrong with your second Patch formula. The second argument of the Patch function should be provided with a record value.

 

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]', ID = PipeLineGallery1.Selected.ID), // Modify formula here
      {
         DateFinished: SelectedDateValue
      }
);
Refresh('[dbo].[tblPipelineStages]');
UpdateContext({VisibleVar: false})

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
SeanTambling
Helper IV
Helper IV

@Shanescows 

Any chance you could take a look at my question above?

v-xida-msft
Community Support
Community Support

Hi @SeanTambling ,

Could you please share a bit more about your scenario?

Do you want to update the record within your '[dbo].[tblPipeline]' table?

 

Based on the formula you provided, I think there is something wrong with your second Patch formula. The second argument of the Patch function should be provided with a record value.

 

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]', ID = PipeLineGallery1.Selected.ID), // Modify formula here
      {
         DateFinished: SelectedDateValue
      }
);
Refresh('[dbo].[tblPipelineStages]');
UpdateContext({VisibleVar: false})

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@v-xida-msft  Hi Kris,

To your question: "Do you want to update the record within your '[dbo].[tblPipeline]' table?"    YES

and Yes, the second patch function is where I have an error.

 

Your solution does work as far as updating the  '[dbo].[tblPipeline]' table, but i need to have 2 arguments be settled to true before updating the field.

 

This is the cut out section of the entire formula that has an error:

 

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

 

 

First, i need to find the correct item to update, that is done with this part of the code: 

LookUp(
'[dbo].[tblPipeline]', ID = PipeLineGallery1.Selected.ID)

Next i need to evaluate a different table called: 

'[dbo].[tblPipelineStageTypes]'

I need to look at each of the items in that table and see if they have a check mark in the column labeled "EndStage" (if true), and then if its ID  equals StageGallery.Selected.PipelineStageTypeID (if true):

Then the patch should update the  '[dbo].[tblPipeline]' table. 

There are 2 possible EndStages and could be more in the future. If a Pipeline has reached its finality then I want to update the DateFinished field ('[dbo].[tblPipeline]' table) with the same date that is updated in the stage ('[dbo].[tblPipelineStages]') that is an EndStage ('[dbo].[tblPipelineStageTypes]'". 

 

Is it possible to have this complicated of a lookup scenario? The PipeLineStage table currently only has 8 rows as does the PipeLineStageTypes table. Neither of these tables will ever be more than 20 rows at max and probably less than 10.

 

Thank you for trying to understand and help.

Hi @SeanTambling ,

Do you want to check if there is an existing record in your '[dbo].[tblPipeline]' table, and there is a matching record existed in your '[dbo].[tblPipelineStageTypes]' before executing the Patch function?

 

I have made a test on my side, please consider modify your formula as below:

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

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-xida-msft 

This formula has no errors! So that's a good sign. The problem is that it didn't update the field {DateFinished: SelectedDateValue} in the '[dbo].[tblPipeline]' table. It seems like it should have patched when i selected a date for an endstage date field and submitted it, but it didn't.

 

Kris, i'd also like to learn and understand why the formula needs the !IsBlank portion in front of the LookUps? I tried taking them out and the formula automatically errors. If you can explain what the reason it is needed i think i could learn more about its logic. If not, no worries. I do a lot of stuff and have no idea why it works, just know that it does 🙂 

 

@v-xida-msft 

 

I was wrong, the table is being updated, so your formula worked perfectly.

 

If you have time, I would still love to understand why the !IsBlank was used in it.

 

Thank you so much!!!

Take care,

Sean

Hi @SeanTambling ,

Have you solved your problem?

 

If you have solved your problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Actually, the result the LookUp formula returned is a record value (including Blank record), the IsBlank() function is used to detect if the result the LookUp formula returned is a blank record.

 

Based on the needs that you mentioned, the Patch function would be executed only when the result the LookUp formula returned is not a Blank record (In other words, the specific record is existed in your data source). So within above formula, I use the following formula (execute the Patch formula only when the LookUp formula result is not Blank record😞

If(
   !IsBlank(LookUp('[dbo].[tblPipeline]', ID = PipeLineGallery1.Selected.ID)) && 
   !IsBlank(LookUp('[dbo].[tblPipelineStageTypes]', ID = StageGallery.Selected.PipelineStageTypeID && EndStage = true)) ,
   ...
   ...
)

 

More details about the IsBlank function and LookUp function, please check the following article:

IsBlank function

LookUp function

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (88,377)