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
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.
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
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.