0

Currently, I am using the following approach to define my Data Extension

 Platform.Load("core","1");
        // instantiate the WSProxy object to make API calls
        var api = new Script.Util.WSProxy();
    
        
        var queryAllAccounts = true;    
    var cols = ["JobID","DE_Name"];
    
    //Define Data Extension that will be used in FOR LOOP to retrieve data
    var DE_ContentDetails_ExternalKey = "1234-1234-1234-1234";
    
var filter = {
    Property: "CustomerKey",
    SimpleOperator: "equals",
    Value: DE_ContentDetails_ExternalKey
};

var tables = api.retrieve("DataExtensionObject[" + DE_ContentDetails_ExternalKey + "]", cols, filter, queryAllAccounts);


for (var i = 0; i < tables.length; i++) {

            var table = tables[i];
//sql += "SELECT Email_Address AS SubscriberKey FROM '" + table.DE_NAME + "\n";

if (i+1 != tables.length) sql += "UNION ALL\n"

            
        }

I think, in my FOR LOOP, tables.length is not a correct way to call / reference the data extension (my guess is - something related to array / object needed to be corrected)

When I test the following:

Write(Stringify(tables.length));

I get 3 while the records in my DE are 5

1 Answer 1

0

Looks like you want to make a list of data extensions in a data extension to dynamically create queries.

Assuming you don't go above 2500 of these (which sounds plausible to me), you could just use SSJS Platform.Function.LookupRows. (Correct me if there is an inherent value of using WSProxy here, but I would say this can be done using "old-fashioned" means, and actually turns out less verbose.)

The trick is to give all rows in your DE some common column value.

E.g. here I will be using "includeRow" as a column that has the value "1" for every row by default.

table "controlDE"

jobId,deName,includeRow
12345,myDE,1
54321,myOtherDE,1

script :

var listOfTables = "controlDE" // name, not customerkey
var listContentAsRows = Platform.Function.LookupRows(listOfTables,['includeRow'],['1']);

var dataExtension = "";

if(listContentAsRows && listContentAsRows.length > 0) {
      for(var i=0; i < listContentAsRows.length; i++) {
          dataExtension = listContentAsRows[i].deName;
          Write(dataExtension + "<br>");
      }
}

===

Edit: Judging from your comment, I understand you do not want to use this approach :D

So:

If you are interested in what exactly is going wrong with your code, my guess would be that tables is not an array. Data types are sometimes the explanation behind unintuitive results using .length, which works on things that are not arrays as well. output Write(Stringify(tables)); before outputting Write(Stringify(tables.length)); to verify.

See docs here: https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/ssjs_WSProxy_basic_retrieve.html

The response [to prox.retrieve] is an object with four properties from the APiObjects SOAP items.

And indeed, one article next to this one, you see it accessed in another example:

https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/ssjs_WSProxy_advanced_retrieve.html

 for(var i=0; i< data.Results.length; i++) {
    Platform.Response.Write(data.Results[i].Name);
    numItems++;
 }

The array you are looking for would likely be tables.Results instead of tables.

==

edit edit: Here is a good example that shows the response schema and the same answer as the above. So probably this is a duplicate.

Looping through wsproxy retrieve results with SSJS

3
  • Hi Jonas, not really a list of data extensions, I'm trying to run a loop to go through each row of one data extension and pick value of a column/ field from each row and adding to my SQL. Right now my loop is running 3 times while I have 5 rows / records. Hence I know running increment on tables.lengh is probably not right. I need help in what to mention in FOR LOOP tables.legth or tables.Results or something like that? That's all
    – kl2
    Jul 2, 2022 at 15:08
  • Hi, that's pretty much what I meant. You are looking for a DE name to add to your SQL as the FROM part . Anyways - edited my answer with some documentation on your question. Jul 3, 2022 at 11:10
  • I think you can use sht like tables.Status first to check whether the retrieve was 'OK' or 'ERROR' and then tables.Results.length
    – SebOB
    Jul 5, 2022 at 12:47

You must log in to answer this question.

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