0

Trying to work out if this is possible. I have the below code that I want to use to output a list of users and their permissions that are linked to an Admin based on subscriber key. The current code loops and pull all records that is associated to the subscriber key, data tables are quite small so not worried about efficiently of the code.

I have 2 data extensions

  • 1 Data extension(DE1) that has the list of Admins
  • 1 Data extension (DE2) that has the list of individuals that are
    related to the individual Admins with the permissions set

I only want to output the users that have been added to DE2 in the last 5 days, this will be an automated comm that is sent on a weekly basis.

I would also like to change a output value for @DEColumn4 so if the value = 1 then display Yes otherwise display No

Or is there a better way to structure the code so the table outputs a new table row for each record found in the last 5 days

The below does work fine with the exception of the date restriction

Any advise would be appreciated

<table><tr >
           <th >
            <table >
                   <tr >
               <th >
                 <table >
                 
                  <tr >
                   <td >
                     <strong>Name</strong></td>
                    <td >
                    <strong> ID</strong></td>
                    <td ><strong>WeB Access</strong></td>
                    <td ><strong>aDMIN Access</strong></td>
                  </tr>
          %%[var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i 
            set @lookupValue = AttributeValue("Subscriber Key") /* value from attribute or DE column in send context */ 
            set @numRowsToReturn = 0 /* 0 means all, max 2000 */ 
            set @rows = LookupOrderedRows("PermissionData",@numRowsToReturn,"FirstName, LastName, StoreID, WebAccess, AdminAccess, CreatedDate", "Subscriber Key", @lookupValue) 
            set @rowCount = rowcount(@rows) if @rowCount > 0 then for @i = 1 to @rowCount do var @DEColumn1, @DEColumn2,  @DEColumn3, @DEColumn4, @DEColumn5,  
         set @row = row(@rows,@i) /* get row based on counter */ 
            set @DEColumn1 = field(@row,"FirstName") 
            set @DEColumn2 = field(@row,"LastName")
            set @DEColumn3 = field(@row,"StoreID") 
           
            Set @DEColumn4 = field(@row,"WebAccess")
                     
            set @DEColumn5 = field(@row,"AdminAccess")
            set @DEColumn6 = field(@row,"CreatedDate")
            
        ]%%
          <tr >
                   <td >%%=v(@DEColumn1)=%% %%=v(@DEColumn2)=%%</td>
                <td >%%=v(@DEColumn3)=%%</td>
                <td >%%=v(@DEColumn4)=%%<br> 
              </td>
                <td >%%=v(@DEColumn5)=%%</td>
          </tr>
          %%[ next @i ]%%</table> %%[ else ]%% %%[ endif ]%%</th></tr></table></th></tr></table>

1 Answer 1

3

You should be able to do it by wrapping the table row in a conditional statement to check the date difference first. This will only display the row if the difference is less than 6 days:

%%[if DateDiff(@DEColumn6, Now(), D) < 6 then ]%%
<tr >
                   <td >%%=v(@DEColumn1)=%% %%=v(@DEColumn2)=%%</td>
                <td >%%=v(@DEColumn3)=%%</td>
                <td >%%=v(@DEColumn4)=%%<br> 
              </td>
                <td >%%=v(@DEColumn5)=%%</td>
          </tr>
%%[endif]%%

Not sure what format your dates are in, so this might require some additional date formatting.

1
  • Thanks your reply however i counldnt seam to get it to work possibly as you say needed some data formatting however I have managed to do it another way using a filter against DE2 filtered to the last 5 days that is set to auto refresh daily
    – Andyw
    Nov 2, 2020 at 0:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .