Issues running Patch function in PowerApps

[bolandre92]

Hi, 

Any help is greatly appreciate it. 

I have create the following patch (I need to insert records from PowerApps to Sharepoint list) and it works perfectly. 

Payments = collection

SPAPLICACIONES = Sharepoint list (table B) 

DOCDETALLE = form with input to table A in Sharepoint

ForAll(Payments, If(!IsBlank(Comprobante), Patch(SPAPLICACIONES,{COMPROBANTE: Comprobante, MONTO_APLICADO: Monto_Aplicado, MONTO_TOTAL: Monto_Total, MONEDA: Moneda, TIPO: Tipo, PAGO: Pago})))

However, when I add just a couple of changes to the patch formula as they are underlying below, it does not run. I have also added the changes individually but it does not run neither.  It worth mentioning that the reason why I am adding the second underlying section is because I am trying to write a link between Table A and Table B, however I have not done any "Last Submit" (any advise here to replace the "Last Submit")

ForAll(Payments, If(!IsBlank(Comprobante), Patch(SPAPLICACIONES, Defaults(SPAPLICACIONES), {COMPROBANTE: Comprobante, MONTO_APLICADO: Monto_Aplicado, MONTO_TOTAL: Monto_Total, MONEDA: Moneda, TIPO: Tipo, PAGO: Pago, CPID: DOCDETALLE.LastSubmit.ID})))

Thank you in advance

[WarrenBelz]

Hi @bolandre92 ,

Firstly I have parsed it below - it is always helpful to use Format Text and put it in a text box as below when posting

ForAll(
   Payments, 
   If(
      !IsBlank(Comprobante),
      Patch(
         SPAPLICACIONES, 
         Defaults(SPAPLICACIONES), 
         {
            COMPROBANTE: Comprobante, 
            MONTO_APLICADO: Monto_Aplicado, 
            MONTO_TOTAL: Monto_Total, 
            MONEDA: Moneda, 
            TIPO: Tipo, 
            PAGO: Pago, 
            CPID: DOCDETALLE.LastSubmit.ID
         }
      )
   )
)

Firstly, adding Defaults(SPAPLICACIONES) is the correct syntax for adding new records, so that should certainly do the job.
As you said it was running before, I assume all field types in Payments (is it a Collection or List ?) line up with the matching ones in SPAPLICACIONES.
This then leaves CPID: DOCDETALLE.LastSubmit.ID . I assume DOCDETALLE is the Form name that was submitted are you want the ID from it - if so, this code should get you the correct value. I also assume CPID is a Numeric field in SPAPLICACIONES.
Are all my assumptions correct?

As an addition, try the below - it would be good if it worked for you.

ClearCollect(
   colPay,
   AddColumns(
      Filter(
         Payments,
         !IsBlank(Comprobante)
      ),
      "CPID",
      DOCDETALLE.LastSubmit.ID
   )
);
Collect(SPAPLICACIONES,colPay)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[v-xida-msft]

Hi @bolandre92 ,

How do you submit your DOCDETALLE form data? Using Patch function or SubmitForm function?

If you submit your DOCDETALLE form data using SubmitForm function, I think you could put your ForAll formula within the OnSuccess property of the DOCDETALLE form, then you could reference the DOCDETALLE.LastSubmit.ID formula.

Please set the OnSuccess property of the DOCDETALLE form to following:

ForAll(
        Payments, 
        If(
            !IsBlank(Comprobante), 
            Patch(
                  SPAPLICACIONES, 
                  Defaults(SPAPLICACIONES), 
                  {
                    COMPROBANTE: Comprobante, 
                    MONTO_APLICADO: Monto_Aplicado, 
                    MONTO_TOTAL: Monto_Total, 
                    MONEDA: Moneda, 
                    TIPO: Tipo, 
                    PAGO: Pago, 
                    CPID: DOCDETALLE.LastSubmit.ID
                  }
            )
        )
);
ResetForm(DOCDETALLE)

when you submit your DOCDETALLE form data successfully using SubmitForm function, the OnSuccess property of the Form would be fired, then the ForAll formula would be executed, you could reference the ID value of the submitted record within your Patch function via DOCDETALLE.LastSubmit.ID formula.

More details about the LastSubmit property of the Edit form, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/controls/control-form-detail#key-proper...

If you use Patch function to submit your DOCDETALLE form data, you could consider set the submitted form data into a variable, then you could reference the ID value of the submitted record through the variable.

Set(
    SubmittedRecord, 
    Patch(
          'Table A List', 
          Defaults('Table A List'),
          DOCDETALLE.Updates
    )
);
ForAll(
        Payments, 
        If(
            !IsBlank(Comprobante), 
            Patch(
                  SPAPLICACIONES, 
                  Defaults(SPAPLICACIONES), 
                  {
                    COMPROBANTE: Comprobante, 
                    MONTO_APLICADO: Monto_Aplicado, 
                    MONTO_TOTAL: Monto_Total, 
                    MONEDA: Moneda, 
                    TIPO: Tipo, 
                    PAGO: Pago, 
                    CPID: SubmittedRecord.ID   // reference ID value from the saved variable value
                  }
            )
        )
);

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

Best regards,

[WarrenBelz]

@v-xida-msft ,

Thank you for the additional information.

@bolandre92 , Happy to continue the discussion with the answers to my queries.

Please tag whoever you want to continue this thread with.

[bolandre92]

Hi Warren, 

Thank you for your reply. 

All your assumptions are correct. Just one remark here: my collection does not have the column CPID. This is not a field that I am feeding in the first patch --Patch(Payments, ThisItem, {Fecha:DateValue(IFecha.Text), Comprobante:IComprobante.Text, Monto_Total: Value(IMonto_Total.Text), Monto_Aplicado: Value(IMonto_Aplicado.Text), Moneda: IMoneda.Text, Tipo: ITipo.Text, Pago: IPago.Text});Collect(Payments,{Fecha:"", Comprobante:"", Monto_Total:0, Monto_Aplicado:0, Moneda: "", Tipo: "", Pago: ""})

Please bear in mind that CPID is a Lookup field in the list SPAPLICACIONES and this fried comes from a table field named CP. So what I am doing here is to create a link between these two tables (SPAPLICACIONES and SPDOCUMENTOSCP). The first have the rows and the second has the heads. 

Should I store CPID in the collection? if so, just as a single text field? or how?

Finally, could you please elaborate more on your second query? what this should do and please clarify if I should replace the ClearCollect that I already have. 

Thank you for your patience, I am not a super PowerApps user. 

Andrey B. 

[bolandre92]

Hi, 

Thank you very much for your time. Very useful information. Just let me clarify one step here. 

I did not find another way to get the CPID other than creating a submit form of DOCDETALLE. However, I am not submiting any input from DOCDETALLE. Actually, it stores a display view of information from a Sharepoint list called SPDOCUMENTOSCP which has the headings while SPAPLICACIONES has the lines. These two lists are connected through a lookup column in SPAPLICACIONES named CPID which brings the ID of SPDOCUMENTOS. So my idea is that once I run the Patch from the PowerApp form, these records drop into SPAPLICACIONES but remain linked to SPDOCUMENTOSCP. 

I hope this is clear otherwise more than happy to provide further explanation. 

Thank you 

[WarrenBelz]

Hi @bolandre92 ,

I assume you want me to take this forward?

That changes the context a bit as you were using LastSubmit in your post - to this point, I have only been looking at the syntax of the Patch code you supplied.

It it reflected in a field value in whatever you have selected as ThisItem or can it be related to a value there with a Lookup?

[bolandre92]

Hi Warren, please do. 

Indeed, I think the last submit is something that I should not use here, however, I have not find the way to write in CPID which links the SP list headers and rows. 

I have attached a screenshot. The button "Insertar pagos" has the -- ClearCollect(Payments,{Fecha:"", Comprobante:"", Monto_Total: 0, Monto_Aplicado: 0,Moneda:"", Tipo:"", Pago:""})

The plus button has the --Patch(Payments, ThisItem, {Fecha:DateValue(IFecha.Text), Comprobante:IComprobante.Text, Monto_Total: Value(IMonto_Total.Text), Monto_Aplicado: Value(IMonto_Aplicado.Text), Moneda: IMoneda.Text, Tipo: ITipo.Text, Pago: IPago.Text});Collect(Payments,{Fecha:"", Comprobante:"", Monto_Total:0, Monto_Aplicado:0, Moneda: "", Tipo: "", Pago: ""})

the save button has the --ForAll(Payments, If(!IsBlank(Comprobante), Patch(SPAPLICACIONES, Defaults(SPAPLICACIONES), {COMPROBANTE: Comprobante, MONTO_APLICADO: Monto_Aplicado, MONTO_TOTAL: Monto_Total, MONEDA: Moneda, TIPO: Tipo, PAGO: Pago, CPID: DOCDETALLE.LastSubmit.ID})))

Bear in mind that all in the blue side is the DOCDETALLE, but I am not submitting anything here. It is just the information from the SP list where I need the form working. 

Let me know if you any further detail. 

thank you. 

[v-xida-msft]

Hi @bolandre92 ,

Could you please share a bit more about the Item property of the DOCDETALLE form?

Do you mean that you do not submit the DOCDETALLE form data, instead, just display information from your SPDOCUMENTOSCP List?

If the DOCDETALLE form Item property is bind to selected item within a Gallery in your canvas app, you could modify your ForAll formula as below:

ForAll(
       Payments, 
       If(
          !IsBlank(Comprobante), 
          Patch(
                SPAPLICACIONES, 
                Defaults(SPAPLICACIONES), 
                {
                  COMPROBANTE: Comprobante, 
                  MONTO_APLICADO: Monto_Aplicado, 
                  MONTO_TOTAL: Monto_Total, 
                  MONEDA: Moneda, 
                  TIPO: Tipo, 
                  PAGO: Pago, 
                  CPID: BrowseGallery.Selected.ID   // Modify formula here
                }
          )
       )
)

Note: I assume that you add a Gallery in your canvas app to list all records from your SPDOCUMENTOSCP List.

If you do not add a Gallery control in your app, just type a LookUp formula within the Item property of your DOCDETALLE form, please modify above ForAll formula as below:

ForAll(
       Payments, 
       If(
          !IsBlank(Comprobante), 
          Patch(
                SPAPLICACIONES, 
                Defaults(SPAPLICACIONES), 
                {
                  COMPROBANTE: Comprobante, 
                  MONTO_APLICADO: Monto_Aplicado, 
                  MONTO_TOTAL: Monto_Total, 
                  MONEDA: Moneda, 
                  TIPO: Tipo, 
                  PAGO: Pago, 
                  CPID: LookUp(SPDOCUMENTOSCP, PrimaryColumn = "Specific value xxxxx").ID   // Modify formula here. Find specific record from your SPDOCUMENTOSCP List, then reference the ID column value
                }
          )
       )
)

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

Best regards,

[WarrenBelz]

Hi @bolandre92 ,

As mentioned in my last post, we only need to get the CPID either directly or by lookup from an item that we have access to.

So how does the CPID relate to the data in your DOCDETALLE table or alternatively an field in the Payments list?

You have said that it a Lookup field in the list SPAPLICACIONES and this field comes from a table field named CP. 

Once you tell me this, we should be able to easily reference it with a Lookup. Also is the ID you are seeking the one from CP or SPAPLICACIONES