I have the following coding in table builder for a symbolic field called outstanding:

AA="

AO="

AR="

AA={ADJ_AMT}

AO={AMT_ORDERED}

AR={AMT_RECV}

CNT=0

CNT=COUNT(AO,@VM) + (CNT "")

FOR I=1 TO CNT

NET=AO—AR

IF AA ] 0 THEN

  NET=AA---AR

END

NEXT I

@ANS=NET

Here's the problem

In the table I have amt_ordered amt_recv outstanding and adj_amt

The first entry

amt_ordered 100, amt_recv 25, outstanding 75 and adj_amt 0. Record is saved and a 2nd entry is created placing 75 on back order, so the 2nd entry is:

amt_ordered 75 and outstanding 75 and all other fields blank.

We receive another 25 and are informed that the remaining 50 we are not getting - that part of the order has been cancelled.

The 1st entry is unchanged. On the 2nd entry we now have

amt ordered 75 amt recv 25 outstanding 0 adj_amt 25. Record is saved. (The outstanding is 0 because the adj_amt and amt recv for the 2nd entry are equal.)

When I retrieve this record after making the adjustment I get the following:

entry 1

amt ordered 100 amt recv 25 outstanding -25 adj amt 0

entry 2

amt ordered 75 amt recv 25 outstanding 0 adj_amt 25.

Can anyone explain why after an adjustment is made that the first entry is showing a -25? I realize that it is picking up the adjustment from the 2nd entry but it needs to realize that the 1st entry's adjustment is 0 and it should use the amount ordered not the adj amt.

Is there any way I can get the system to return in entry 1 outstanding 75, like I want it to, when an adjustment has been made to an subsequent entry?

Thanks for your help

Andy Becker

IL State Lottery

Andy,

You have an AMV of three multi-valued fields ADJ_AMT, AMT_ORDERED, AMT_RECV, containing a set of data entries. The situation calls for a loop where each single entry is handled individually, and so the idea of a loop is correct. But in the loop body (between FOR and NEXT) you need to talk about the individual values, e.g, AO (the I-th value of the AMT_ORDERED field) and not the whole of AO. Then, since you do calculations on single values, you must use simple arithmetic operators like "-", and not "—".

I think perhaps this is what you want to do:

AA="

AO="

AR="

AA={ADJ_AMT}

AO={AMT_ORDERED}

AR={AMT_RECV}

CNT=0

CNT=COUNT(AO,@VM) + (CNT "")

NET="

FOR I=1 TO CNT

NET=AO - AR

IF AA ] 0 THEN

NET=AA - AR

END

NEXT I

@ANS=NET

- Oystein -

Thanks Oystein - I'll give it a try

Just another case of not being able to see the forest for the trees.

Andy

View this thread on the forum...