• State Not Answered
  • Replies 7 replies
  • Subscribers 7 subscribers
  • Views 256 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Process

Ideas on creating process for importing excel data to database

Lrk0000
Certified Associate Developer

Hi,

I am working on a task where the main function is importing excel data to the database. 
I need to create an interface where I upload the excel file and the file gets saved somewhere in the directory, I did this by simply adding a file upload section which contains this code:

a!sectionLayout(
  label: "Upload Employee File",
  contents: {
    a!fileUploadField(
      label: "File Upload",
      labelPosition: "ABOVE",
      value: ri!document,
      saveInto: ri!document,
      target: cons!HR_EmployeeDataPointer,
      maxSelections: 1
    )
  }
)


Now, I need to create a process model where first I get the data from this uploaded file, query and match the data in the database and check if the employee exists and if it doesn't I need to update the database or leave it as it is if it exists. After these I need to show all the data in a grid in another interface.
Besides the first interface I also created the expression rule to read through the excel file even though it's still not finished yet. It has this code which goes through the uuid field.
if(
  a!isNullOrEmpty(ri!document),
  {},
  a!localVariables(
    local!data: readexcelsheetpaging(ri!document, 0, a!pagingInfo(1, 5000)),
    local!onlyData: index(local!data, "data", null),
    local!employeeData: a!forEach(
      items: local!onlyData,
      expression: 'recordType!{SYSTEM_RECORD_TYPE_USER}User'(
        'recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_uuid}uuid': index(fv!item.values, 1, null)
      )
    ),
    local!employeeData
  )
)


Now, some ideas or advice on creating the process model would be helpful because that's where I am kind of stuck

  Discussion posts and replies are publicly visible

Parents Reply Children
  • 0
    Certified Associate Developer
     in reply to Stefan Helzle

    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

  • 0
    Certified Lead Developer
     in reply to Lrk0000

    What problems do you face here?

    Lrk0000 said:
    problems with referencing the expression rule in the script task
  • 0
    Certified Associate Developer
     in reply to Stefan Helzle

    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
    )
  )
)
    This is where I would need some help :D

    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

  • 0
    Certified Lead Developer
     in reply to Lrk0000

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

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

  • 0
    Certified Associate Developer
     in reply to Stefan Helzle

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

  • 0
    Certified Lead Developer
     in reply to Lrk0000

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

    isusernametaken(ri!workEmail)

    You are not doing anything with your local!users

© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.