Inquiry Regarding Creating Historical Records When Writing to Existing Records in a Process Model

Without knowing any details, I can think of two options.

Do you plan to keep both records in the same database table? What about related data?

Create an expression in Appian that takes the original record and returns two copies. One with the same primary key and some updates, the other with an empty primary key and the necessary adjustments.

Or, create a trigger in the database that inserts a new item, instead of an in-place update.

Thank you for your response.

I will be working with two different data tables. The intended process is to retrieve data 2 from table 1 using input data 1 as the key. I will then subtract input data 3 from data 2 and update the result as the value of data 2 in table 1. In addition, all input data will be written to table 2. If this explanation seems unclear, we will give a concrete example later.

I encountered a problem with the data retrieval not working when using the following code:

a!queryRecordType(
recordType: 'recordType!Table1',
fields: { 'recordType!Table1Data2'
},
filters: a!queryFilter(
field: 'recordType!Table1Data1',
operator: "=",
value: pv!Data1
),
pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 500)
).data['recordType!Table1Data2']

Your help in resolving this issue would be greatly appreciated. Thank you very much.

Hi @r7x7djutejjh0001,

The code is correct for giving the required output,
Can you please check the value you are passing in pv!Data1 has any corresponding data in the table?

Thank you for your assistance; the issue with the code has been resolved, and I appreciate your help.

However, I am not very familiar with database usage, and I am encountering a challenge when writing input data to table 2. Specifically, when the same ID is written, the value gets updated instead of preserving all entries. Since I intend to use it as a historical record table, I would like all data, even with the same ID, to be saved.

Your guidance on how to achieve this and preserve all entries, even with identical IDs, would be greatly appreciated. Thank you.

From what you said, I understand that you want to update the particular fields corresponding to any ID and the remaining fields should remain the same.
If I am not wrong then you can achieve this by update function.
you can take a reference from this code.

a!localVariables(
/* querying data from database and to update firstName and Last Name and remaining other 
fields will be same */
  local!data:index(
    rule!UA_qr_getUserDetails(
    filters: {
      a!queryFilter(
        field: 'recordType!{d8d2946d-462b-404a-ba5e-d96036a0edc1}UA User Detail.fields.{9a3cb925-d459-4387-8650-c358cd83c200}userIdPk',
        operator: "=",
        value: 1
      )
    },
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 1,
    )
  ),
  "data",
  {}
  ),
a!update(
  data: local!data,
  index: {
    'recordType!{d8d2946d-462b-404a-ba5e-d96036a0edc1}UA User Detail.fields.{3bbe35c2-dba8-4f31-955f-943ce4a66397}firstName',
    'recordType!{d8d2946d-462b-404a-ba5e-d96036a0edc1}UA User Detail.fields.{c89af5c2-f497-4850-a028-325168189ed5}lastName'
  },
  value: {
    "ABC",
    "PQR"
  }
)
)

When using the WriteRecordSmartService in my process model, if the primary key ID from the input process variables matches an existing ID in the database, the input data is currently being updated in the record with the matching ID. However, I want the system to write the input data as a new record, even if there is a match with an existing ID, rather than updating the record.

How can I configure the process to ensure that, even when there's a matching ID in the database, the WriteRecordSmartService writes the input data as a new record rather than updating the existing one? Your guidance on achieving this behavior would be greatly appreciated. Thank you.

When using the WriteRecordSmartService in my process model, if the primary key ID from the input process variables matches an existing ID in the database, the input data is currently being updated in the record with the matching ID. However, I want the system to write the input data as a new record, even if there is a match with an existing ID, rather than updating the record.

How can I configure the process to ensure that, even when there's a matching ID in the database, the WriteRecordSmartService writes the input data as a new record rather than updating the existing one? Your guidance on achieving this behavior would be greatly appreciated. Thank you.

now I am not getting your problem statement,
first, you want to update data corresponding to the primary key and now you are saying to write the input data as new data.
if you want to add new data in a database whose fields same as the data you updated.
So just need to update the primary key as null then new data will be created with a new primary key

The issue has been resolved. I have established a link between the primary key ID of Table 1 and a non-primary key ID in Table 2. Now, when writing data to Table 2, the primary key number increments (e.g., 1, 2) with each entry.

I apologize for any confusion in my previous descriptions, and I appreciate your assistance. If I encounter any difficulties in the future, I would be grateful for your guidance. Thank you once again.