Data extension functions in AMPscript

1) DataExtensionRowCount()

 It will return the number of rows in a DE. 

 Syntax :- DataExtensionRowCount (DEName)

 Example :- 

          %%[

    Var @deRows

    Set @deRows = DataExtensionRowCount("Automations List__IN")

    Output(v(@deRows))

    ]%%

  Result = 20 

2) Lookup()

It will return the value of a specific column in a data extension. 

It will returns the value of a single column from that Data Extension.

If your search criteria return more than one result, the system returns the first matching value it finds.

Syntax:

 Lookup(dataExt,

 returnColumn,

 searchColumn1, searchValue1,

 searchColumn2, searchValue2...)

Example:

 %%[

   Var @tempValue

   Set @tempValue = Lookup("Automations List__IN", "Status", " AutomationName", "My Automation_1")

  Output(v(@tempValue))

]%%

Result : Running

3) LookupRows()

Similar to the second function, but it can store the result of multiple values in an array format. It can store upto 2000 rowsets. Rows returned will be unordered. 

This function is case insensitive. 

Syntax:

 Lookup(dataExt,

 searchColumn1, searchValue1,

 searchColumn2, searchValue2...)

Example :

 %%[

   Var @tempValue

   Set @tempValue = Lookup("Automations List__IN", " AutomationName", "My Automation_1")

  Output(v(@tempValue))

]%%

Result: System.Data.DataRow()

4) RowCount()

This will return the number of rows in an array or rowset. 

  Syntax:

  RowCount(rowset)

  Example:

  %%[

    Var @tempValue

    Set @tempValue = LookupRows("Automations List__IN", " AutomationName", "My Automation_1")

    Output(v(@tempValue))

    Set @rowCount = RowCount(@tempValue)

    Output(v(@rowCount))

    ]%%

Result :- 1500

5) Row() 

 It returns a specific row from a rowset or array.

 Syntax:

 Row(rowset, rowPosition)

 Example:

%%[

  Var @tempValue

  Set @tempValue = LookupRows("Automations List__IN", " AutomationName", "My Automation_1")

  Output(v(@tempValue))

  Set @row = Row(@tempValue, 1)

  Output(v(@row))  

]%%

Result : System.Data.DataRow()  ( Note:- We see this result as the single line result is being treated as an object here. )

6) Field()

It returns a specific field from a data row.

Syntax:

Field(row, fieldName, boolExceptionIfNotFound)

Example:

%%[

  Var @tempValue

  Set @tempValue = LookupRows("Automations List__IN", " AutomationName", "My Automation_1")

  Output(v(@tempValue))

  Set @rowCount = RowCount(@tempValue)

  Set @row = Row(@tempValue, 1)

  Set @status = Field(Row(@tempValue, 1), "Status")

  Output(v(@status))

]%%

Result : Automation 1

By Ananya Agrawal