SQL Query not working

carlosp5114 over 1 year ago

Hi! I´m trying to make a query in order to update certain rows of a column in one Record (CPF_Lineas_Pedido) with the exact values of the columns of Record 2 (CPF_Lineas_Recepcion), where the variables "IdPedido" and "Material" are the ones used in the process

Query:

UPDATE CPF_LINEAS_PEDIDO, CPF_LINEAS_RECEPCIONES_V3

SET CPF_LINEAS_PEDIDO.CANT_RECEP_ACUM = CPF_LINEAS_RECEPCIONES_V3.CANTIDAD_RECEP

WHERE CPF_LINEAS_PEDIDO.ID_PEDIDO = ac!pedido AND CPF_LINEAS_PEDIDO.MATERIAL IN

(SELECT material FROM CPF_LINEAS_RECEPCIONES_V3 WHERE ID_PEDIDO = ac!pedido)

The query changes the values of the desired rows of CPF_Lineas_Pedido, but instead of picking the same value of the other table, it takes random values

1) I introduce the value of "Cantidad Recepcionada" for each Line, and the record is written

2) Database CPF_Lineas_Recepcion written (ok)

3) Database CPF_Lineas_Pedido written (not ok)

How would you link this fields by query? Would you try a different approach?

Discussion posts and replies are publicly visible

Top Replies

Stefan Helzle over 1 year ago in reply to carlosp5114 +1
Certified Lead Developer

That loops is back to https://docs.appian.com/suite/help/23.3/Write_Records_Smart_Service.html#update-existing-records https://docs.appian.com/suite/help/23.3/Write_Records_Smart_Service.html#create…

0 Mike Schmitt over 1 year ago

Once again I most strongly recommend against using the Query SQL node to do routine updates like this. You should be updating the Appian data in-process then using the Appian smart services (WTDS or Write Record) to push your new values over to your database.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Reject Answer

Cancel

0 carlosp5114 over 1 year ago in reply to Mike Schmitt

Hi Mike, I´m trying to do it but I´m facing trouble. I will try to explain myself in a correct way:

1) The user enters in the PO (summary view) and sees an interface with data of the Record of PO (R1), an editable grid with info of PO lines (R2) and a button "Recepcionar Pedido"

2) "Recepcionar Pedido" is a related record action from Record 1 (PO), which starts a process model that begins with this interface:

In this interface, I pass the values of the grid (plus one extra value fulfilled by hand by the user) to another record of Receptions (R3). Also, I want to update the value put by hand ("Cantidad Recepcionada") in my PO lines record (R2). (Lines 160-170 of my code)

a!localVariables(

  local!DatosLineas: a!queryRecordType(
    recordType: 'recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido',
    fields: {
      'recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{fcfa4b36-4398-4430-8055-ecfa59daf13a}idLineaPedido','recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{71427dbb-12db-4f83-bd99-f625bf9af271}idPedido', 'recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{69e9a834-b2fb-4f35-a36d-3c526c97f4ac}Material','recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{e78c5b35-026f-4713-8404-05077c6c9152}Cantidad', 'recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{47f4bf32-2e41-4c5a-a7e7-9fc7786bd753}PrecioUnit', 'recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{beb59a89-a332-46d3-b718-a8285f58f942}Importe', 'recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{7fd4a6cb-f5b7-4f0f-9f86-1680005f8be5}CantidadPdteRecep'
    },
    filters: a!queryFilter(
      field: 'recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{71427dbb-12db-4f83-bd99-f625bf9af271}idPedido',
      operator: "=",
      value: ri!record['recordType!{3c95c0e4-e69d-43f6-b485-e72b24252af7}CPF Maestro Pedidos.fields.{7309402c-2312-4a4e-bdaa-5654789bffb2}idPedido']
    ),
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
  ).data
  ,
  local!DatosLineas2:{},
  local!DatosLineas3:{},
  {
    a!sectionLayout(
      label: "Datos Recepción",
      labelIcon: "dolly",
      contents: {
        a!columnsLayout(
          columns: {
            a!columnLayout(
              contents: {
                a!textField(
                  label: "Id Recepción",
                  labelPosition: "ABOVE",
                  value: ri!RecordRecepciones['recordType!{b6fc6108-1f5d-4cbe-9c5c-08da4b1d58f3}CPF Maestro Recepciones.fields.{cb86a21e-a5e5-4ec7-98dc-14e94cc5736c}idRecepcion'],
                  saveInto: ri!RecordRecepciones['recordType!{b6fc6108-1f5d-4cbe-9c5c-08da4b1d58f3}CPF Maestro Recepciones.fields.{cb86a21e-a5e5-4ec7-98dc-14e94cc5736c}idRecepcion'],
                  refreshAfter: "UNFOCUS",
                  validations: {}
                )
              }
            ),
            a!columnLayout(
              contents: {
                a!textField(
                  label: "Id Pedido",
                  labelPosition: "ABOVE",
                  value: ri!record['recordType!{3c95c0e4-e69d-43f6-b485-e72b24252af7}CPF Maestro Pedidos.fields.{7309402c-2312-4a4e-bdaa-5654789bffb2}idPedido'],
                  saveInto: ri!RecordRecepciones['recordType!{b6fc6108-1f5d-4cbe-9c5c-08da4b1d58f3}CPF Maestro Recepciones.fields.{4f22f1d7-f5cd-446a-90a2-64a5893f912b}idPedido'],
                  refreshAfter: "UNFOCUS",
                  validations: {}
                )
              }
            ),
            a!columnLayout(
              contents: {
                a!dateTimeField(
                  label: "Fecha y hora de Recepción",
                  labelPosition: "ABOVE",
                  value: ri!RecordRecepciones['recordType!{b6fc6108-1f5d-4cbe-9c5c-08da4b1d58f3}CPF Maestro Recepciones.fields.{4fa92044-3c8b-438b-b77e-30bf88efbae3}FechaRecepcion'],
                  saveInto: ri!RecordRecepciones['recordType!{b6fc6108-1f5d-4cbe-9c5c-08da4b1d58f3}CPF Maestro Recepciones.fields.{4fa92044-3c8b-438b-b77e-30bf88efbae3}FechaRecepcion'],
                  validations: {}
                )
              }
            )
          }
        )
      }
    ),
    a!sectionLayout(
      label: "Líneas Recepción",
      labelIcon: "list-alt-solid",
      contents: {
        a!gridLayout(
          label: "A continuación, introduzca las cantidades a recepcionar para cada línea de pedido y pulse el botón de Recepción",
          headerCells: {
            a!gridLayoutHeaderCell(label: "Linea Pedido"),
            a!gridLayoutHeaderCell(label: "Pedido"),
            a!gridLayoutHeaderCell(label: "Material"),
            a!gridLayoutHeaderCell(label: "Cantidad Pendiente"),
            a!gridLayoutHeaderCell(label: "Precio Unitario"),
            a!gridLayoutHeaderCell(label: "Cantidad Recepcionada")
          },
          columnConfigs: {
            a!gridLayoutColumnConfig(width: "DISTRIBUTE"),
            a!gridLayoutColumnConfig(width: "DISTRIBUTE"),
            a!gridLayoutColumnConfig(width: "DISTRIBUTE"),
            a!gridLayoutColumnConfig(width: "DISTRIBUTE"),
            a!gridLayoutColumnConfig(width: "DISTRIBUTE"),
            a!gridLayoutColumnConfig(width: "DISTRIBUTE"),
            a!gridLayoutColumnConfig(width: "ICON")
          },
          rows: a!forEach(
            items: local!DatosLineas,
            expression: a!gridRowLayout(
              contents: {
                a!textField(
                  value: fv!index,
                  readOnly:true
                ),
                a!textField(
                  value: fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{71427dbb-12db-4f83-bd99-f625bf9af271}idPedido'],
                  readOnly:true
                ),

                a!textField(
                  value: fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{69e9a834-b2fb-4f35-a36d-3c526c97f4ac}Material'],
                  readOnly:true
                ),
                a!integerField(
                  value: fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{7fd4a6cb-f5b7-4f0f-9f86-1680005f8be5}CantidadPdteRecep'],
                  required: true,
                  readOnly:true,
                  validations: {
                  }
                ),
                a!floatingPointField(
                  value: fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{47f4bf32-2e41-4c5a-a7e7-9fc7786bd753}PrecioUnit'],
                  required: true,
                  readOnly:true,
                  validations: {
                  }
                ),
                a!integerField(
                  value: fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{bd0fe4ca-322c-422e-89e5-97e5194817a1}CantRecepAcum'],
                  saveInto: {fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{bd0fe4ca-322c-422e-89e5-97e5194817a1}CantRecepAcum']},
                  validations: if(isnull(fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{bd0fe4ca-322c-422e-89e5-97e5194817a1}CantRecepAcum']), "", if(fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{bd0fe4ca-322c-422e-89e5-97e5194817a1}CantRecepAcum']<=fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{e78c5b35-026f-4713-8404-05077c6c9152}Cantidad'], "", "La cantidad recepcionada debe ser igual o inferior a la cantidad de la línea de pedido"))
                  
                )

              }
            )
          )
        )
      }
    ),
    a!buttonLayout(
      primaryButtons: {
        a!buttonWidget(
          label: "Completar Recepción",
          saveInto:{
          a!save(
            
              /*cambiar por recordLineasRecepciones*/
              local!DatosLineas2,
              a!forEach(
                local!DatosLineas,
                'recordType!{9340eead-a0d4-4504-93d9-b117f09a47d2}CPF Lineas Recepciones v3'(
                  'recordType!{9340eead-a0d4-4504-93d9-b117f09a47d2}CPF Lineas Recepciones v3.fields.{d7518970-ac55-44b5-b525-5a51247887cb}idRecepcion': ri!RecordRecepciones['recordType!{b6fc6108-1f5d-4cbe-9c5c-08da4b1d58f3}CPF Maestro Recepciones.fields.{cb86a21e-a5e5-4ec7-98dc-14e94cc5736c}idRecepcion'],
                  'recordType!{9340eead-a0d4-4504-93d9-b117f09a47d2}CPF Lineas Recepciones v3.fields.{15ae98c8-aae8-4e42-83ee-cc9f8a15054e}proveedor': ri!record['recordType!{3c95c0e4-e69d-43f6-b485-e72b24252af7}CPF Maestro Pedidos.fields.{e11e6206-fc23-4a4b-9819-4db5a5bab685}nomProveedor'],
                  'recordType!{9340eead-a0d4-4504-93d9-b117f09a47d2}CPF Lineas Recepciones v3.fields.{0a43fa95-c09b-4b60-9ae5-575d2528608e}createdOn': now() + intervalds(2,0,0),
                  'recordType!{9340eead-a0d4-4504-93d9-b117f09a47d2}CPF Lineas Recepciones v3.fields.{c6c28a01-ff0f-4127-b8d5-58afe27e5a4e}createdBy': loggedInUser(),
                  'recordType!{9340eead-a0d4-4504-93d9-b117f09a47d2}CPF Lineas Recepciones v3.fields.{8692352f-94c8-41c3-a9e8-b0e6e4357164}idLineaPedido': fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{fcfa4b36-4398-4430-8055-ecfa59daf13a}idLineaPedido'],
                  'recordType!{9340eead-a0d4-4504-93d9-b117f09a47d2}CPF Lineas Recepciones v3.fields.{13436966-037d-4db1-9485-4a63ba64df7f}idPedido': fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{71427dbb-12db-4f83-bd99-f625bf9af271}idPedido'],
                  'recordType!{9340eead-a0d4-4504-93d9-b117f09a47d2}CPF Lineas Recepciones v3.fields.{4ed86221-1e35-41ab-af39-37897a6e45ca}material': fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{69e9a834-b2fb-4f35-a36d-3c526c97f4ac}Material'],
                  'recordType!{9340eead-a0d4-4504-93d9-b117f09a47d2}CPF Lineas Recepciones v3.fields.{8746ef15-c9ee-4f02-870a-72405fadf88c}precioUnit': fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{47f4bf32-2e41-4c5a-a7e7-9fc7786bd753}PrecioUnit'],
                  'recordType!{9340eead-a0d4-4504-93d9-b117f09a47d2}CPF Lineas Recepciones v3.fields.{403a1827-c369-495b-ba26-f0f31d1d7a59}cantidadRecep': fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{bd0fe4ca-322c-422e-89e5-97e5194817a1}CantRecepAcum'] )
              )    
            
              ),
              
              a!save(
                ri!recordLineasRecepV3,
                local!DatosLineas2
              ),
              a!save(

                /*cambiar por recordLineasRecepciones*/
                local!DatosLineas3,
                a!forEach(
                  local!DatosLineas,
                  'recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido'(
                    'recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{bd0fe4ca-322c-422e-89e5-97e5194817a1}CantRecepAcum': fv!item['recordType!{495578a9-1b23-4253-8145-6418faabe727}CPF Lineas Pedido.fields.{bd0fe4ca-322c-422e-89e5-97e5194817a1}CantRecepAcum'] )
                )    

              ),

              a!save(
                ri!recordLineasPedido,
                local!DatosLineas3
              ),
        
              a!save(
                ri!RecordRecepciones['recordType!{b6fc6108-1f5d-4cbe-9c5c-08da4b1d58f3}CPF Maestro Recepciones.fields.{4f22f1d7-f5cd-446a-90a2-64a5893f912b}idPedido'],
                ri!record['recordType!{3c95c0e4-e69d-43f6-b485-e72b24252af7}CPF Maestro Pedidos.fields.{7309402c-2312-4a4e-bdaa-5654789bffb2}idPedido']
              ),
              
              a!save(ri!RecordRecepciones['recordType!{b6fc6108-1f5d-4cbe-9c5c-08da4b1d58f3}CPF Maestro Recepciones.fields.{cad778de-1c66-4aca-9abf-ebea67e7f4d3}createdOn'], now() + intervalds(2,0,0)
              ),
              a!save(ri!RecordRecepciones['recordType!{b6fc6108-1f5d-4cbe-9c5c-08da4b1d58f3}CPF Maestro Recepciones.fields.{95377c9c-61d7-4677-89bb-3cc3e5150f05}createdBy'],loggedInUser())
          },
          submit: true,
          style: "PRIMARY",
          validate: true
        )
      },
      secondaryButtons: {
        a!buttonWidget(
          label: "Cancelar",
          value: true,
          saveInto: ri!cancel2,
          submit: true,
          style: "NORMAL",
          validate: false
        )
      }
    )
  }
)

The creation of R3 and passing the values is correct

But when I try to update the value of "Cantidad Recepcionada" in R2,a new entry in My Record is created, instead of updating the values. I´m passing the values from my interface (which I use in the second pick) to a ri! and then to the process correctly

(Example with other values)

It seems like I´m not linking the values of R2 I want to update.

In this case, I´m not receiving the previous information of the PO Lines (R2) in the process model, just the new ones. I´m not sure if I have to pass some extra-data or rule input of the part of the record I want to update.

Any help or approach will be useful

Thanks a lot

0 Stefan Helzle
Certified Lead Developer
over 1 year ago in reply to carlosp5114

That loops is back to

https://docs.appian.com/suite/help/23.3/Write_Records_Smart_Service.html#update-existing-records

https://docs.appian.com/suite/help/23.3/Write_Records_Smart_Service.html#create-new-records

I highly suggest to do a small separate test to fully understand how this works.
Cancel
Vote Up +1 Vote Down

Sign in to reply

Verify Answer

Cancel