How to handle data between 2 tables?

[Arthur Sanchez]

The situation is, I want to create 2 tables, where one will be for creation and reading only and the other will be for editing, but the second table must be able to edit data from the first table.

The logic I want to do is, in a table a user creates a record, and this information is loaded to the second table for support or solver to resolve, and return the information for what was done with the record/ticket to the first table and However, in the second table he can change the values of his fields such as 'State' or 'Queue'.

My biggest mystery is how I do it, if anyone has already done it or knows the step-by-step path I would be grateful, I'm '""trying"" to do it via Studio

[Tony Chatfield1]

Hi, not sure that I have understood your business requirement\reasoning for doing this, or how this would work in real world.

But if one table is a 'reference' to the other table, then on other table list and forms you will be able to dot.walk to the fields of the first table and be able to edit (assuming your user has edit rights).

[Maddysunil · Accepted solution]

@Arthur Sanchez  

You'll need to leverage a combination of table relationships, scripting, and business rules.

Step 1: Create the Tables

1. Table 1 (Creation and Reading)

   • Create a new table (e.g., u_creation_table).
   • Add the necessary fields (e.g., short_description, description, requested_by, etc.).

2. Table 2 (Editing)

   • Create another new table (e.g., u_editing_table).
   • Add fields corresponding to the ones in u_creation_table that need to be updated (e.g., state, queue, etc.).
   • Add a reference field to link to u_creation_table (e.g., u_creation_record).

Step 2: Populate the Editing Table

When a record is created in u_creation_table, you need to create a corresponding record in u_editing_table.

1. Business Rule on u_creation_table
   • Create a new business rule on u_creation_table.
   • Set it to run after insert.
   • Use the script below to create a record in u_editing_table

(function executeRule(current, previous /*null when async*/) {
    var editRecord = new GlideRecord('u_editing_table');
    editRecord.initialize();
    editRecord.u_creation_record = current.sys_id; // Reference to the creation table
    // Populate other fields if necessary
    editRecord.insert();
})(current, previous);
​

• Step 3: Updating u_creation_table from u_editing_table

  When a record in u_editing_table is updated, the corresponding record in u_creation_table should be updated.

  1. Business Rule on u_editing_table
     • Create a new business rule on u_editing_table.
     • Set it to run before update.
     • Use the script below to update the related record in u_creation_table.

(function executeRule(current, previous /*null when async*/) {
    var creationRecord = new GlideRecord('u_creation_table');
    if (creationRecord.get(current.u_creation_record)) {
        creationRecord.state = current.state;
        creationRecord.queue = current.queue;
        // Update other fields as needed
        creationRecord.update();
    }
})(current, previous);

Step 4: Security and Permissions

1. Access Controls
   • Ensure appropriate access controls are set on u_creation_table so users can create and read records but not update them.
   • Ensure appropriate access controls are set on u_editing_table to allow the required users to update the records.

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks