I wish to create a session that can update the alternative items table based on rules of how our item coding is structured (and also belonging to a range of item groups).

So, our item code is structured into 4 parts - width, gauge, type & colour. If gauge, type & colour are the same then the item is a satisfactory alternative.

In my example code below, I can select my list of possible items and my calc.film.features function creates an array of the 3 features above. But I am now stuck! At the point marked ***, I need to move on to the next item and test whether its features are the same as my current one - if so I need to insert it into the alternative items table.

We are talking of around 1000 items that change frequently - new widths being added for example.

Any help would be gratefully received! thanks

function read.main.table()
{

citg.f = "AF0000"
citg.t = "AFZZZZ"

select tiitm001.item:otheritm
from tiitm001
where tiitm001._index8 between {:citg.f}
and {:citg.t}
selectdo
select tiitm011.item
from tiitm011
where tiitm001._index1 = {:otheritm}
selectdo
selectempty
calc.film.features(otheritm)
*** select tiitm001.item:otheritm
from tiitm001
where tiitm001._index8 between {:citg.f}
and {:citg.t}
endselect

Hi EdHubbard,

Why don't you try using ALIAS when querying the same table again and again for different conditions. By using the same table pointer, system might loose the record pointer pointing to the correct record.

Warans

Yes, I see what you are saying.

However, my main problem is going through my "list" of items to see whether it is a suitable alternative. Do I set me original select to be stored as an array? How do I do through that record by record comparing it to my extracted features?

Yes, I thought about it. Based on the first query, select all the items and store them in a dynamic array. (Only item codes).
Then scan through the array and apply your features logic to find whether the item is alternative or not by calling a function that compares (function within the script)...
once found, store it as alternative item in the table...
You can use performance boosters if required for effective usage of memory when your list of items grows.