[SalesForce] Cloudpage Ampscript rowcount

I am using a cloudpage to display some general metrics on a data extension.

Having trouble displaying rowCount based on a field called entry_date

%%[ var @count, @today
SET @today = Format(Now(), "MM d, yyyy")
SET @count = RowCount(LookupRows('PlayTogetherWinTogether_Entries_Main','entry_date', @today))


]%%

%%=v(@count)=%%

A sample of the date of entry displays in the data extension as Monday, December 11, 2017 11:32 PM.

Basically just want to grab new records from today.

Best Answer

The simplest way (by sticking with AMPScript) is to use a lookuprows to get all your entries, and then use a FOR with IF ELSE statement. This will only work though if you have a universal identifier in the data. E.g. I usually use a column named 'secret' that defaults to a value of 1.

If you do not already have a field like this, what I would recommend is first to add a nullable field named 'secret' with a default value of 1 to 'PlayTogetherWinTogether_Entries_Main'. Then you can either create a SQL Query to update the value or export and import with the update made in excel or similar editor.

Example SQL: 

SELECT p.*, '1' as secret
FROM PlayTogetherWinTogether_Entries_Main as p

Then select the target DE as 'PlayTogetherWinTogether_Entries_Main' and update type as 'Update'.

Below would be sample AMPScript:

%%[ VAR @count, @today, @rows

SET @count = 0
SET @today = Format(Now(), "MM d, yyyy")
SET @rows = LookupOrderedRows('PlayTogetherWinTogether_Entries_Main', DataExtensionRowCount('PlayTogetherWinTogether_Entries_Main'), 'secret', 1))

FOR @i = 1 to Rowcount(@rows) DO

  VAR @entry_date
  SET @entry_date = Field(Row(@rows, @i), 'entry_date')

  IF FORMAT(@entry_date, "MM d, yyyy") == @today THEN

   SET @count = ADD(@count, 1)

  ENDIF

NEXT @i

]%%

%%=v(@count)=%%

It is not as efficient as ROWCOUNT and LookupRows, but it should do the trick.

I have not tested the sample script, so if it throws an error, let me know and I will adjust accordingly.

Related Topic