Table function

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

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

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

okay thanks.

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?

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?

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