0

I have a DE that looks like the below.

enter image description here

I am going to de-dupe the email at send so for this example I would like the output in the email to look like below. So basically it reads the ADDR and displays the number of rows per identical set. (in this case 2 rows for 12 some street and a separate table with one row for 15 some street)

Reading and Date will be filled by the customer.

enter image description here

My code is below. I am not getting the desired output with this.

  <tr>
    <td><b>PARTY</b></td>
    <td><b>METER</b></td>
    <td><b>Sku</b></td>
    <td><b>ADDR</b></td>
    <td><b>Reading</b></td>
    <td><b>Date</b></td>
  </tr>

%%[
var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i

set @lookupValue = AttributeValue("PARTY")
set @lookupValue = AttributeValue("ADDR") 
set @numRowsToReturn = 0
set @rows = LookupOrderedRows("DE_name",@numRowsToReturn, "PARTY desc, METER asc, Sku desc, ADDR asc, Reading desc, Date desc","ADDR", @lookupValue)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

for @i = 1 to @rowCount do

    var @Party, @Meter, @Sku, @ADDR, @Reading, @Date
    set @row = row(@rows,@i)
    set @Party = field(@row,"PARTY")
    set @Meter = field(@row,"METER")
    set @Sku = field(@row,"Sku")
    set @ADDR = field(@row,"ADDR")
    set @Reading = field(@row,"Reading")
    set @Date = field(@row,"Date")

    ]%%
   
    %%[ 
next @i ]%%

%%[ else ]%%
        
no rows found
        
%%[ endif ]%%
  
<tr>
<td>%%=v(@PARTY)=%%</td>
<td>%%=v(@METER)=%%</td>
<td>%%=v(@Sku)=%%</td>
<td>%%=v(@ADDR)=%%</td>
<td>%%=v(@Reading)=%%</td>
<td>%%=v(@Date)=%%</td>
</tr>
</table>
1
  • I am going to give this one a go and revert.
    – Rahul G
    May 19 at 3:00
1

Your issue is more with the logic behind what you're doing rather than what you're doing. You're better off looking up all the rows for a single person with your single LookupOrderedRows call rather than trying to look it up based on address. Using your example above, how do you know you need to make 2 requests for 2 addresses without parsing the data first? You can't and you don't, so you may as well just pull all the data for that person out in the order you really want it and use AMPscript to run through it. I haven't tested this but I suspect you want something like this (note I'm assuming you're looking up based on the ContactID field):

%%[
VAR @cid, @addr
SET @cid = AttributeValue("Id")

SET @cRows = LookupOrderedRows("consec_readings", 0, "ADDR_NO DESC, ICP DESC, METER ASC, Reading DESC, Date ASC", "Id", @cid)

IF RowCount(@cRows) > 0 THEN
  FOR @i = 1 TO RowCount(@cRows) DO
    IF Field(Row(@cRows, @i), "ADDR_NO") != @addr THEN
      IF @i > 1 THEN
      ]%%
      </table>
      %%[ENDIF]%%

<br><br>ADDRESS: %%=Field(Row(@cRows, @i), "ADDR_NO")=%%
<table border="1" margin="1px" align="left">
  <tr>
    <td style="text-align:center;"><b>ICP</b></td>
    <td style="text-align:center;"><b>Meter</b></td>
    <td style="text-align:center;"><b>Reading</b></td>
    <td style="text-align:center;"><b>Date</b></td>
  </tr>
    %%[
    ENDIF

    SET @addr = Field(Row(@cRows, @i), "ADDR_NO")
    SET @ICP = Field(Row(@cRows, @i), "ICP")
    SET @Meter = Field(Row(@cRows, @i), "METER")
    SET @Reading = Field(Row(@cRows, @i), "Reading")
    SET @Date = Field(Row(@cRows, @i), "Date")

    SET @formattedDate = IIF(EMPTY(@Date), "", Format(@Date, "dd/MM/yyyy"))

    ]%%
      <tr>
        <td>%%=v(@ICP)=%%</td>
        <td>%%=v(@Meter)=%%</td>
        <td><input type="text" name="Reading%%=v(@i)=%%" placeholder="insert reading here" id="Reading%%=v(@i)=%%" value="%%=v(@Reading)=%%"></td>
        <td><input type="date" name="date%%=v(@i)=%%" placeholder="reading date" id="date%%=v(@i)=%%" value="%%=v(formattedDate)=%%"></td>
      </tr>
  
  %%[
  NEXT

  ELSE
  ]%%
  <tr><td colspan="4">No rows found</td></tr>
%%[ENDIF]%%
</table>
3
  • Hi Jarrett, I completely forgot the Reading and Date has to be entered by the customer. Can LookupOrderedRows work with <Input> tags or does it only work with <Table>
    – Rahul G
    May 23 at 21:35
  • 1
    Is there going to be a value in the Reading and Date field in the data extension that you want to pre-fill or do you want it to be empty and for them to enter it? If it's empty you'd use (as an example): <td><input type="text" name="Reading%%=v(@i)=%%" id="Reading%%=v(@i)=%%"></td> If you wanted to pre-fill the value in the form field you'd add in value="%%=v(@Reading)=%%" May 25 at 5:34
  • Fantastic. thank you. i will post the code once i have it working.
    – Rahul G
    May 25 at 20:46
0

So here is the code that works.

There isn't much real estate for the address in the table so i am want to move the Address and place it above the table but I am getting results such as the below screenshots. Is there any tweaks we can make to the code to achieve the address lining up with the table below.

enter image description here

enter image description here

%%[
VAR @cid, @addr
SET @cid = AttributeValue("Id")

SET @cRows = LookupOrderedRows("consec_readings", 0, "ADDR_NO DESC, ICP DESC, METER ASC, Reading DESC, Date ASC", "Id", @cid)

IF RowCount(@cRows) > 0 THEN
  FOR @i = 1 TO RowCount(@cRows) do
    IF Field(Row(@cRows, @i), "ADDR_NO") != @addr THEN
      IF @i > 1 THEN
      ]%%
      
      %%[ENDIF]%%


<br><br>ADDRESS: <table style="width=&quot;210px&quot;" border="1" margin="1px" align="left" width="210" ;="">
<tr>
<td style="text-align:center; width:30px;"><b>Address</b></td>
<td style="text-align:center; width:30px;"><b>ICP</b></td>
<td style="text-align:center; width:30px;"><b>Meter</b></td>
<td style="text-align:center; width:30px;"><b>Reading</b></td>
<td style="text-align:center; width:30px;"><b>Date</b></td>
</tr>
    %%[
    ENDIF

SET @addr = Field(Row(@cRows, @i), "ADDR_NO")
SET @ICP = Field(Row(@cRows, @i), "ICP")
SET @Meter = Field(Row(@cRows, @i), "METER")
SET @Reading = Field(Row(@cRows, @i), "Reading")
SET @Date = Field(Row(@cRows, @i), "Date")

    ]%%
      <tr>
        <td style="width:30px;">%%=v(@addr)=%%</td>
        <td style="width:30px;">%%=v(@ICP)=%%</td>
        <td style="width:30px;">%%=v(@Meter)=%%</td>
        <td style="width:30px;"><input type="text" name="Reading" placeholder="insert reading here" value="" id="Reading"></td>
        <td style="width:30px;"><input type="date" name="date" placeholder="reading date" value="" id="date"></td>
      </tr>%%=v(@addr)=%%
  
  %%[
  NEXT

  ELSE
  ]%%
  <tr><td>No rows found</td></tr>
%%[ENDIF]%%
</table>
1
  • 1
    Rahul. I've updated my code above to allow for what you want. A couple of things... 1. I removed the widths on your td's because they didn't add up. I don't know why you only have 210px to play with but you can set them up again if you need to. 2. I'm assuming there is a value returned from the Reading and Date fields in the DE. If not this code is redundant. 3. I removed the address from the table because you have it in a heading above the table. Again, code is untested but should be just about right. May 25 at 23:48
0

It almost works!

96-100 is the address for the first table and 106 the last. the alignment is off it seems.

enter image description here

This is what i get when i remove the br tags before Address:

enter image description here

2
  • This is now fixed. I added the Address inside another table tag.
    – Rahul G
    May 26 at 1:22
  • Thank you @Jarrett
    – Rahul G
    May 26 at 1:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.