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?

  • 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. 

Reply
  • 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. 

Children