Skip to main content
K
kaushikr0003
March 9, 2022
Solved

How to update 2 CDT's with each other values in a process model

  • March 9, 2022
  • 7 replies
  • 0 views

I am new to Appian. Still learning the basics.

I have two tables

1. Attachments - for adding attachment relate details like fileId,fileName, fileExt, fileSize etc and

2. AttachmentDetails - for description, comments etc

Below is the requirement -

When i upload a document I save them to the attachment table with attachmentDetails primary key (attachmentDetailsId)

After the documents are saved in the attachment table I need to save the attachment tables primary key (attachmentId) to the attachmentDetails table

The problem is i am not sure how to update when there are multiple attachments.

For Example - If i  upload 2 attachments and update attachmentDetails

Attachments Table

attachmentId fileId attachmentDetailsId
1 123 1
2 234 2

 Attachment Details Table

attachmentDetailsId Description attachmentId
1 Evidence 1
2 Tracker 2

I will first save the 2 attachmentDetails (array) to the db first. (at this point attachmentId will be null and I will have the attachmentDetailsId's)

Before saving the attachments to the table i need to map each attachment with attachmentDetailsId. How do i map attachmentDetailsId-1  with first attachment and  attachmentDetailsId-2 with second attachment?

After I map the above I will save them to the attachments table (at this point I will have the attachmentId's)

How do i now map attachmentId-1 to the attachmentDetailsId-1 and How do i now map attachmentId-2 to the attachmentDetailsId-2?

Best answer by viraty527193

Hi, if there is one to one relationship I can't think of any use case where we need to maintain two tables. But in case you want this and there is some use case for the same. Below is one of the possible ways to do it

a!localVariables(
  /* Assuming this to be the initial state */
  local!attachment: {
    {
      attachmentId: null,
      fileId: 123,
      attachmentDetailsId: null
    },
    {
      attachmentId: null,
      fileId: 234,
      attachmentDetailsId: null
    }
  },
  local!attachmentDetail: {
    {
      attachmentDetailsId: null,
      Description: "Evidence",
      attachmentId: null
    },
    {
      attachmentDetailsId: null,
      Description: "Tracker",
      attachmentId: null
    }
  },
  /* Wrtitng local!attachmentDetail to database using write 
  to datastore entity will give back the Ids for the same */
  local!attachmentDetailAfterWrite: {
    {
      attachmentDetailsId: 1,
      Description: "Evidence",
      attachmentId: null
    },
    {
      attachmentDetailsId: 1,
      Description: "Tracker",
      attachmentId: null
    }
  },
  /* We can use below to map the attachment detail in the
attachment table
*/
local!attachmentBeforeWrite:  a!forEach(
    items: local!attachment,
    expression: updatedictionary(
      fv!item,
      {
        attachmentDetailsId: property(
          index(
            local!attachmentDetailAfterWrite,
            fv!index,
            {}
          ),
          "attachmentDetailsId",
          {}
        )
      }
    )
  )
  
/* You can then write the attachment to database thus generating
the Ids for the same and then use the similar way to map
atatchment Ids to the attachment detail 
*/
)

The output of the mapping forloop will be something like this

 

Hope it helps

7 replies

stefanhelzle0001
stefanhelzle0001
Brainy
March 9, 2022

You can directly assign lists to lists. Give it a try. In case it does not work, please attach some screenshots of your process node configurations.

K
kaushikr0003Author
March 9, 2022

I am trying something like this

a!forEach(
items: ri!attachment,
expression: reduce(
a!update,
fv!item,
merge(
{"attachmentDetailsId","folderId"},
{ri!attachmentDetailsId, ri!folderId}
)
)

ri!attachmentDetailsId is an array

 ri!attachment is attachment type array

K
kaushikr0003Author
March 11, 2022

Thanks stefanh0002 for your reply.

andrewh0007
andrewh0007
March 9, 2022

Firstly I'd say that you don't need to reference the other table from BOTH tables. You really only need attachmentId in the Attachment Details table. Also, if it's a 1 to 1 relationship I'm not sure you need the extra table in the first place. This does depend on your exact use case but needing a description for an attachment doesn't feel like it needs an extra table (especially if a description is mandatory!). Also consider that a document in Appian can have a description too so you might not even need to save this in the database! Again...this does depend on the specific use case.

I'd suggest considering:

  1. Can you just save the description into the Appian document object?
  2. Why do you need the extra AttachmentDetails table?
  3. If you need the extra details table then:
    1. You only need to reference the attachmentId from the AttachmentDetails table and not the attachmentDetailsId from the Attachments table
    2. As Stefan said, you can nest CDTs in each other and if your foreign keys are set up correctly then a write to database node will actually take care of your primary and foreign keys
    3. You could save to the attachments table first then iterate using a!forEach() save into the attachmentId column of AttachmentDetails. There's quite a few different ways of doing this depending on the exact set up and it isn't really preferable.
K
kaushikr0003Author
March 11, 2022

Thanks andrewh0007 for the answer. It was designed initially by senior tech leads that attachment table will be a common table for all the projects and attachmentDetails table is specific for my particular project where they want to store additional information for the attachments they are uploading. So unfortunatly I have to save the details as mentioned above.

viraty527193
viraty527193Answer
March 11, 2022

Hi, if there is one to one relationship I can't think of any use case where we need to maintain two tables. But in case you want this and there is some use case for the same. Below is one of the possible ways to do it

a!localVariables(
  /* Assuming this to be the initial state */
  local!attachment: {
    {
      attachmentId: null,
      fileId: 123,
      attachmentDetailsId: null
    },
    {
      attachmentId: null,
      fileId: 234,
      attachmentDetailsId: null
    }
  },
  local!attachmentDetail: {
    {
      attachmentDetailsId: null,
      Description: "Evidence",
      attachmentId: null
    },
    {
      attachmentDetailsId: null,
      Description: "Tracker",
      attachmentId: null
    }
  },
  /* Wrtitng local!attachmentDetail to database using write 
  to datastore entity will give back the Ids for the same */
  local!attachmentDetailAfterWrite: {
    {
      attachmentDetailsId: 1,
      Description: "Evidence",
      attachmentId: null
    },
    {
      attachmentDetailsId: 1,
      Description: "Tracker",
      attachmentId: null
    }
  },
  /* We can use below to map the attachment detail in the
attachment table
*/
local!attachmentBeforeWrite:  a!forEach(
    items: local!attachment,
    expression: updatedictionary(
      fv!item,
      {
        attachmentDetailsId: property(
          index(
            local!attachmentDetailAfterWrite,
            fv!index,
            {}
          ),
          "attachmentDetailsId",
          {}
        )
      }
    )
  )
  
/* You can then write the attachment to database thus generating
the Ids for the same and then use the similar way to map
atatchment Ids to the attachment detail 
*/
)

The output of the mapping forloop will be something like this

 

Hope it helps

    K
    kaushikr0003Author
    March 11, 2022

    Thanks viraty this worked for me. 

    Terms & ConditionsAccessibility statement