Table function

Hello everyone,  

I have a question that table function is created in the database with set of columns and How will I use the table function in appian with some parameters?

Can anyone please help me to further proceed?

Thanks in advance.

  Discussion posts and replies are publicly visible

Parents
  • If you want to obtain data from a Table-valued Function in your database, you can create a view that queries the function, and set a data type / data store up on the view, same way as you would for a normal table. 

    I do this in one scenario where a report is generated by a function that creates/returns a temporary table, ended up being better for performance this way in my scenario, where lots of data is aggregated for a weekly summary.

    CREATE view [dbo].[VIEW_FROM_FUNCTION]
    as
    
    select 
    [Col1],
    [Col2]
    /*etc*/
    from dbo.fn_YOUR_TABLE_VALUED_FUNCTION()
    
    GO
    

  • I am getting the error while creating new Data type

  • 0
    Certified Lead Developer
    in reply to JS0001

    Did you try to follow the advised steps in the error message?

  • 0
    Certified Senior Developer
    in reply to JS0001

    Please create primary key for a column for example, create a new column with name ID and makeit primary key.

  • Additionally, you do not have to create an entire new column to add a unique column into a view.  For example in MSSQL, you can add a unique column on the fly with the ROW_NUMBER() function, for example:

    SELECT TOP (100) PERCENT ROW_NUMBER() OVER (ORDER BY id DESC) AS Row, 
    step,requestNumber,requestStartTime
    from tblCOE_SAMPLE_TABLE

  • okay thanks for your reply chris, I am facing difficulties while doing the developement that I will be sharing my code here

    a!gridField(
      labelPosition: "COLLAPSED",
      spacing: "DENSE",
      
      data: ri!ReportCountsViewData_cdt,
      columns: {
        a!gridColumn(
          label: "Summary",
          value: fv!row.reportName_txt
        ),
        a!gridColumn(
          align: "CENTER",
          label: "Due in 60 days",
        
          value: a!richTextDisplayField(
            value: a!richTextItem(
              text: fv!row.due60Days_int,
              linkStyle: "STANDALONE",
              link: a!dynamicLink(
                label: fv!row.due60Days_int,
                value: fv!row.due60Days_int,
                saveInto: {
                  a!save(
                    ri!selectedItemType_txt,
                    "Due in 60 days"
                  ),
                  a!save(
                    ri!selectedItemReportType_txt,
                    fv!row.reportName_txt
                  )
                },
                showWhen: and(
                  not(
                    isnull(
                      tointeger(
                        fv!row.due60Days_int
                      )
                    )
                  ),
                  tointeger(
                    fv!row.due60Days_int
                  ) <> 0
                )
              )
            )
          )
        ),
        a!gridColumn(
          align: "CENTER",
          label: "Due in 30 days",
         
          value: a!richTextDisplayField(
            value: a!richTextItem(
              text: fv!row.due30Days_int,
              linkStyle: "STANDALONE",
              link: a!dynamicLink(
                label: fv!row.due30Days_int,
                value: fv!row.due30Days_int,
                saveInto: {
                  a!save(
                    ri!selectedItemType_txt,
                    "Due in 30 days"
                  ),
                  a!save(
                    ri!selectedItemReportType_txt,
                    fv!row.reportName_txt
                  )
                },
                showWhen: and(
                  not(
                    isnull(
                      tointeger(
                        fv!row.due30Days_int
                      )
                    )
                  ),
                  tointeger(
                    fv!row.due30Days_int
                  ) <> 0
                )
              )
            )
          )
        ),
        a!gridColumn(
          align: "CENTER",
          label: "Past Due in 1-30 days",
          
          value: a!richTextDisplayField(
            value: a!richTextItem(
              text: fv!row.pastdue30Days_int,
              linkStyle: "STANDALONE",
              link: a!dynamicLink(
                label: fv!row.pastdue30Days_int,
                value: fv!row.pastdue30Days_int,
                saveInto: {
                  a!save(
                    ri!selectedItemType_txt,
                    "Past Due in 1-30 days"
                  ),
                  a!save(
                    ri!selectedItemReportType_txt,
                    fv!row.reportName_txt
                  )
                },
                showWhen: and(
                  not(
                    isnull(
                      tointeger(
                        fv!row.pastdue30Days_int
                      )
                    )
                  ),
                  tointeger(
                    fv!row.pastdue30Days_int
                  ) <> 0
                )
              )
            )
          )
        ),
        a!gridColumn(
          align: "CENTER",
          label: "Past Due in 30+ days",
         
          value: a!richTextDisplayField(
            value: a!richTextItem(
              text: fv!row.pastdueOver30Days_int,
              linkStyle: "STANDALONE",
              link: a!dynamicLink(
                label: fv!row.pastdueOver30Days_int,
                value: fv!row.pastdueOver30Days_int,
                saveInto: {
                  a!save(
                    ri!selectedItemType_txt,
                    "Past Due in 30+ days"
                  ),
                  a!save(
                    ri!selectedItemReportType_txt,
                    fv!row.reportName_txt
                  )
                },
                showWhen: and(
                  not(
                    isnull(
                      tointeger(
                        fv!row.pastdueOver30Days_int
                      )
                    )
                  ),
                  tointeger(
                    fv!row.pastdueOver30Days_int
                  ) <> 0
                )
              )
            )
          )
        )
      }
    )

    I need to do same summary report at cust level(view is created at unique cust level and taking care of the counts that is mentioned in the code).How will I modify the code at unique cust level and on click of counts the new grid (grid is different based on some conditions)has to be display. can anyone help on this?

Reply
  • okay thanks for your reply chris, I am facing difficulties while doing the developement that I will be sharing my code here

    a!gridField(
      labelPosition: "COLLAPSED",
      spacing: "DENSE",
      
      data: ri!ReportCountsViewData_cdt,
      columns: {
        a!gridColumn(
          label: "Summary",
          value: fv!row.reportName_txt
        ),
        a!gridColumn(
          align: "CENTER",
          label: "Due in 60 days",
        
          value: a!richTextDisplayField(
            value: a!richTextItem(
              text: fv!row.due60Days_int,
              linkStyle: "STANDALONE",
              link: a!dynamicLink(
                label: fv!row.due60Days_int,
                value: fv!row.due60Days_int,
                saveInto: {
                  a!save(
                    ri!selectedItemType_txt,
                    "Due in 60 days"
                  ),
                  a!save(
                    ri!selectedItemReportType_txt,
                    fv!row.reportName_txt
                  )
                },
                showWhen: and(
                  not(
                    isnull(
                      tointeger(
                        fv!row.due60Days_int
                      )
                    )
                  ),
                  tointeger(
                    fv!row.due60Days_int
                  ) <> 0
                )
              )
            )
          )
        ),
        a!gridColumn(
          align: "CENTER",
          label: "Due in 30 days",
         
          value: a!richTextDisplayField(
            value: a!richTextItem(
              text: fv!row.due30Days_int,
              linkStyle: "STANDALONE",
              link: a!dynamicLink(
                label: fv!row.due30Days_int,
                value: fv!row.due30Days_int,
                saveInto: {
                  a!save(
                    ri!selectedItemType_txt,
                    "Due in 30 days"
                  ),
                  a!save(
                    ri!selectedItemReportType_txt,
                    fv!row.reportName_txt
                  )
                },
                showWhen: and(
                  not(
                    isnull(
                      tointeger(
                        fv!row.due30Days_int
                      )
                    )
                  ),
                  tointeger(
                    fv!row.due30Days_int
                  ) <> 0
                )
              )
            )
          )
        ),
        a!gridColumn(
          align: "CENTER",
          label: "Past Due in 1-30 days",
          
          value: a!richTextDisplayField(
            value: a!richTextItem(
              text: fv!row.pastdue30Days_int,
              linkStyle: "STANDALONE",
              link: a!dynamicLink(
                label: fv!row.pastdue30Days_int,
                value: fv!row.pastdue30Days_int,
                saveInto: {
                  a!save(
                    ri!selectedItemType_txt,
                    "Past Due in 1-30 days"
                  ),
                  a!save(
                    ri!selectedItemReportType_txt,
                    fv!row.reportName_txt
                  )
                },
                showWhen: and(
                  not(
                    isnull(
                      tointeger(
                        fv!row.pastdue30Days_int
                      )
                    )
                  ),
                  tointeger(
                    fv!row.pastdue30Days_int
                  ) <> 0
                )
              )
            )
          )
        ),
        a!gridColumn(
          align: "CENTER",
          label: "Past Due in 30+ days",
         
          value: a!richTextDisplayField(
            value: a!richTextItem(
              text: fv!row.pastdueOver30Days_int,
              linkStyle: "STANDALONE",
              link: a!dynamicLink(
                label: fv!row.pastdueOver30Days_int,
                value: fv!row.pastdueOver30Days_int,
                saveInto: {
                  a!save(
                    ri!selectedItemType_txt,
                    "Past Due in 30+ days"
                  ),
                  a!save(
                    ri!selectedItemReportType_txt,
                    fv!row.reportName_txt
                  )
                },
                showWhen: and(
                  not(
                    isnull(
                      tointeger(
                        fv!row.pastdueOver30Days_int
                      )
                    )
                  ),
                  tointeger(
                    fv!row.pastdueOver30Days_int
                  ) <> 0
                )
              )
            )
          )
        )
      }
    )

    I need to do same summary report at cust level(view is created at unique cust level and taking care of the counts that is mentioned in the code).How will I modify the code at unique cust level and on click of counts the new grid (grid is different based on some conditions)has to be display. can anyone help on this?

Children
  • 0
    Certified Senior Developer
    in reply to JS0001

    I think you can use your condition based on your selectedItemType_txt variable. Please use apply when functionality into query filters of querylogicalexpression.

    a!localVariables(
      local!data: a!queryEntity(
        entity: cons!CDU_TRACK_ITEMS_DSE,
        query: a!query(
          pagingInfo: a!pagingInfo(1, - 1),
          logicalExpression: a!queryLogicalExpression(
            operator: "AND",
            filters: {
              a!queryFilter(
                field: "id",
                operator: "=",
                value: 1,
                applyWhen: ri!selectedItemType_txt = "1"
              ),
              a!queryFilter(
                field: "id",
                operator: "=",
                value: 2,
                applyWhen: ri!selectedItemType_txt = "2"
              ),
              a!queryFilter(
                field: "id",
                operator: "=",
                value: 3,
                applyWhen: ri!selectedItemType_txt = "3"
              ),
              a!queryFilter(
                field: "id",
                operator: "=",
                value: 4,
                applyWhen: ri!selectedItemType_txt = "4"
              )
            }
          )
        )
      ).data,
      local!data
    )

    In the above code, i fetch the data based on the variable. 

  • Hi yes  I have used the same query rule and but my requirement is to get the same grid at cust level in that how would I modify the above shared code ? and on click of dynamic link counts as i said need to display the other grid which data is coming from the other view.