Using variables in FROM clause

[olivetwist]

I am trying to loop through a list of users and pull Web Service data for each.

I have a list of IDs in a table. Field name is "UserID"

I am trying to do something like this:

FOR Each LnID in UserID

Concatenate Users:

Load *

FROM [http://hrapps//humanresources/hrwebservice/emplogin.asmx/Search?LanID= $(LnID)] (XmlSimple, Table is [DataSet]);

is this possible? any advise from pulling all data from ASMX is also appreciated.

[sasiparupudi1 · Accepted solution]

try like this

Users:

FOR i=0 to NoOfRows('yourtable');-1

LET vUserID=trim(Peek('UserID',$(i),'yourtable'));

if i>0 then

Concatenate Users;

end if;

Load *

FROM [http://hrapps//humanresources/hrwebservice/emplogin.asmx/Search?LanID= '$(vUserID')] (XmlSimple, Table is [DataSet]);

next;

hth

Sasi

[marcus_sommer]

You could loop through the table which contained your UserID like SasiParupudi1 had suggested - also possible might be a loop through the field per fieldvalue() which is general faster but you will need bigger datasets to notice the differences.

If you want use FOR Each LnID in UserID the UserID needs to be a variable which contains a list like:

'User1', 'User2', 'User3'

and which could be used like: FOR Each LnID in $(UserID)

Such a listing could be created per concat() in a previous load and then per peek() assigned to a variable.

Therefore several ways are possible and the easiest way seems to be to loop through a table but if there are further requirements this could be change.

- Marcus