Microsoft Dynamics AX (Archived)

Looping through a column

(0) Share

Report

Posted on by Bharath B J

Hi Everyone,

I have a table with 2 columns A and B:

A B

101 1a

101 1b

201 2a

201 2b

I want to loop through column A and get respective values.

For example:

while(A)

{

somevariable (can be a list/array/container) = B's values for 101;

}

Output needs to be:

101 1a, 1b

201 2a, 2b

Thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

YourTable t;

while select A from t { ... }

Also please don't forget that you learn such basics from documentation.

Was this reply helpful? Yes No
Bharath B J 85 on at

Like (0)

Report

Hi Martin,

I know about the usage of while select.

I hope what I have asked for is pretty clear.

Was this reply helpful? Yes No
Yunus Shaik 330 on at

Like (1)

Report

Hi Bharath,

Use a Map to store the A column's value as key and the corresponding B column values as its value.

Then from this map, you can retrieve the values for each key.

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

No, it's not. You described a requirement but it's not clear which part you need help with. If it's not the loop, maybe it's adding to collection, or converting the collection to comma-separated string, or something else. It's hard to guess.

It would help if you actually asked a question.

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report
Example with items and item buyer groups:

static void Job115(Args _args) { InventBuyerGroup buyerGroup; InventTable inventTable; Map itemMap = new Map(Types::String, Types::Container); MapEnumerator me; str ret; int i; while select ItemId, ItemBuyerGroupId from inventTable order by ItemId { i++; if (i == 50) { break; } itemMap.insert(inventTable.ItemBuyerGroupId, itemMap.exists(inventTable.ItemBuyerGroupId) ? conIns(itemMap.lookup(inventTable.ItemBuyerGroupId), conLen(itemMap.lookup(inventTable.ItemBuyerGroupId)) + 1, inventTable.ItemId) : [inventTable.ItemId]); } me = itemMap.getEnumerator(); while (me.moveNext()) { for (i = 1; i <= conLen(me.currentValue()); i++) { ret += ret ? strFmt(', %1', conPeek(me.currentValue(), i)) : conPeek(me.currentValue(), i); } info(strFmt('%1 - %2', me.currentKey(), ret)); ret = ''; } }

Result:

Was this reply helpful? Yes No
Bharath B J 85 on at

Like (0)

Report

Hi Vilmos,

Thank you much!

But is using map the only feasible way to solve this?

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (1)

Report

You could also do while select + order by columnA ASC, columnB ASC - this will get the B column values first for your A = 101, etc.

Then store the columnA as a local variable and check if columnA has changed in the table loop. If not, like in my case concatenate columnB in a string, Set or whatever is your choosing. Then if columnA changes, you already have in your variable the previous columnA value and can info it out with the concatenated string, then reset both to the new A value of the record buffer and empty string/set for the values.

Please note that using maps and sets on large transactional tables are not advised due to the heavy memory footprint!

Was this reply helpful? Yes No
Yunus Shaik 330 on at

Like (0)

Report

For this scenario, I think using map is the only solution.

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (1)

Report

There surely are other ways - you could use a temporary table, a different type of collection, even write direct SQL query if performance is a concern... It all depends on your actual requirements.

Was this reply helpful? Yes No
Bharath B J 85 on at

Like (0)

Report

Thank you all for your valuable suggestions.

Learnt something new with every suggestion given :)

Was this reply helpful? Yes No