Ideas on creating process for importing excel data to database

Can you share more details about the problem you have with the process model?

Yeah, so far I've added an user input task where the interface is referenced, 

I am having problems with referencing the expression rule in the script task and getting the data from there, as you can see the process is still not finished but I am looking more for ideas or advice on how could this process be implemented

What problems do you face here?

Lrk0000 said:

problems with referencing the expression rule in the script task

So, I took this approach,

We upload the excel file in the interface, I made a rule which querys through the document and extracts the username, which is also the work email, and it works like this.

if(
  a!isNullOrEmpty(ri!document),
  {},
  a!localVariables(
    local!data: readexcelsheetpaging(ri!document, 0, a!pagingInfo(3, 5000)),
    local!onlyData: index(local!data, "data", null),
    local!emailIndex: wherecontains("Work Email", c=ast(typeOf({"test"}), index(local!onlyData,1).values) ),
    local!employeeData: a!forEach(
      items: local!onlyData,
      expression: if(
        fv!index = 1,
        null,
        index(fv!item.values, index(local!emailIndex, 1), null)
      )
    ),
    remove(local!employeeData, 1)
  )
)

Now, I am creating script task that returns boolean array for each of the usernames(if they exist as user record type) but which is not returning correctly since it's returning all of the usernames.

a!localVariables(
  local!users: a!queryRecordType(
    recordType: 'recordType!{SYSTEM_RECORD_TYPE_USER}User',
    fields: {
      'recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_username}username'
    },
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
  ).data,
  a!forEach(
    items: local!users,
    expression: if(
       contains(
        cast(typeof("test"), local!users),
         ri!workEmail
       ),
       {},
       fv!item
    )
  )
)

After this, I would a rule to check if the usernames are found in User table and Employee table and after update the database and show the data in the interface

So, I took this approach,

We upload the excel file in the interface, I made a rule which querys through the document and extracts the username, which is also the work email, and it works like this.

if(
  a!isNullOrEmpty(ri!document),
  {},
  a!localVariables(
    local!data: readexcelsheetpaging(ri!document, 0, a!pagingInfo(3, 5000)),
    local!onlyData: index(local!data, "data", null),
    local!emailIndex: wherecontains("Work Email", c=ast(typeOf({"test"}), index(local!onlyData,1).values) ),
    local!employeeData: a!forEach(
      items: local!onlyData,
      expression: if(
        fv!index = 1,
        null,
        index(fv!item.values, index(local!emailIndex, 1), null)
      )
    ),
    remove(local!employeeData, 1)
  )
)

Now, I am creating script task that returns boolean array for each of the usernames(if they exist as user record type) but which is not returning correctly since it's returning all of the usernames.

a!localVariables(
  local!users: a!queryRecordType(
    recordType: 'recordType!{SYSTEM_RECORD_TYPE_USER}User',
    fields: {
      'recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_username}username'
    },
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
  ).data,
  a!forEach(
    items: local!users,
    expression: if(
       contains(
        cast(typeof("test"), local!users),
         ri!workEmail
       ),
       {},
       fv!item
    )
  )
)

After this, I would a rule to check if the usernames are found in User table and Employee table and after update the database and show the data in the interface

This seems to be somewhat over-engineered. Did you try this function?

https://docs.appian.com/suite/help/24.1/fnc_people_isusernametaken.html

I didn't know about this function, thanks.

a!localVariables(
  local!users: a!queryRecordType(
    recordType: 'recordType!{SYSTEM_RECORD_TYPE_USER}User',
    fields: {
      'recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_username}username'
    },
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 1)
  ).data,
  a!forEach(
    items: local!users,
    expression: isusernametaken(ri!workEmail)
  )
)

I think you should be able to further reduce this to just

isusernametaken(ri!workEmail)

You are not doing anything with your local!users