Sandi,

It sounds like you may be experiencing a index corruption and missing records problem similar to the one we have been struggling with. The problem seems to only appear in large multi-user environments.

After you reindex and re-run your app do you really come up with the correct total; and ARE YOU CERTAIN that all the items that were originally entered have been counted and one is just no longer showing up in the item table?

Before you reindex, if you browse your item table with the primary indexs set to the problem invoice and do a SEEK and have FOUND() = .T. does the pointer ever end up on the wrong record? If you browse without an index set do you see any records with all null data(looks like a bunch of black bars)? If you had a situation where your SEEK seemed to be successful and yet ended up on the wrong record, if you do a LOCATE for that record w/o the index set does FOUND() = .F.? If the answers to these questions are "yes", then this is what I am also seeing.

A reindex resolves the issue of the SEEK returning an erronus .T. and screwing up your totals because the record then is not only missing from the table but also the index.

I have not been able to fix our problem and am trying a work around where I create a backup table and will try to find the missing records from it when we encounter this random missing record.

Pat Chrisco

>Sorry Jim, I guess I misunderstood. My tables (arinvoic) and ariitems have a
>large number of indexes and I am not sure which tag is causing the problem. I didnt think it was related to the expression of the index because under normal conditions this problem does not occur. I thought the whole CDX file could get out of whack and the programs, when using the tables, could get erroneous results due to these. To be more specific in the main screen that you build the invoices in my save method of the screen, it has the following code:
>
>...
>SELECT ariitems
>IF gvjcenable
> CALCULATE SUM(ROUND(QTY*AMOUNT,2)), SUM(ROUND(QTY*HOLDBACK,2)) FOR
> REFNO = THISFORM.pgfPageFrame.Page1.txtRefno.Value TO
> lnTotAmt,lnTotHB
>ELSE
> CALCULATE SUM(ROUND(QTY*AMOUNT,2))FOR
> REFNO = THISFORM.pgfPageFrame.Page1.txtRefno.Value TO lnTotAmt
>ENDIF
>
>SELECT arinvoic
>lnTotGST = ROUND(lnTotAmt *
> THISFORM.pgfPageFrame.Page1.txtGstRate.Value/100),2)
>IF THISFORM.pstongst
> lnTotPST = ROUND((lnTotAmt+lnTotGST) *
> (THISFORM.pgfPageFrame.Page1.txtPstRate.Value/100),2)
>ELSE
> lnTotPST = ROUND(lnTotAmt *
> (THISFORM.pgfPageFrame.Page1.txtPstRate.Value/100),2)
>ENDIF
>
>SELECT arinvoic
>REPLACE arinvoic.amount with lnTotAmt+lnTotGST+lnTotPST, ;
> arinvoic.balance with lnTotAmt+lnTotGST+lnTotPST, ;
> arinvoic.gstamt with lnTotGST, ;
> arinvoic.pstamt with lnTotPST, ;
> arinvoic.hbamt with lnTotHB in arinvoic
>
>
>...
>
>When something is amiss the arinvoic.amount does not equal the total of the line items, (as I see them with my eyes). I have found that if I reindex the tables and then edit and save (so this code executes again) the totals are calculated properly. In all other cases, (not at this client or at this client and 99% of the time) this code executes properly. (If they edit and save again without reindexing the problem remains). This is why I though I had a corrupted index. My problem is I cannot detect when it is wrong so I cant re-index hence I was trying to find a way to avoid the indexes altogether so I could get a reliable total in all cases.
>
>My index on arinvoic is Primary and the expression is REFNO (which is a N(6) field that links the parent to the child. My index on ariitems is also the PRIMARY index for that table and the expression is STR(refno,6)+STR(itemno,3) ( which is the linking field and the item # which is incremental for that refno).
>
>No one is going in the back door.
>Sandi.
>
>>Sandi,
>>
>>You chose not to show the index expression in question. I have difficulty understanding, especially when you write below "If I look at the invoice header record it's total is wrong". Does that mean that there is an index TAG on the header's amount field?
>>
>>Is it possible that someone at that site can (and does) go into some records sometimes to make "corrections" to item amounts directly in the tables and doesn't realize that the header would need adjusting too? Or even just deleting item records without adjusting elsewhere?
>>
>>Select does NOT bypass indices IF the SQL statement itself is properly coded so that it can recognize what indexes are applicable.
>>
>>If you can't or won't tell us the index TAG expressions, then please supply a line-by-line description of what you expect to happen AND what is (not) happening.
>>
>>good luck
>>
>>
>>>Jim,
>>>I can only surmise that the index is out of whack because 99% of the time this doesnt happen, and it is only happening on one site. If I look at the invoice header record it's total is wrong, but if I reindex then edit it, when it recalcs it is corrected. This is why I believe it is an index problem. Set Deleted is on. The amount being out is not a rounding issue, it appears to be an item from a different invoice. Does SQL select bi-pass indices, so if I
>>>
>>>Select ... FROM ariitems GROUP by .. WHERE ... and NOT DELETED('ariitems'), will I get a better result?
>>>
>>>
>>>
>>>>Sandi,
>>>>
>>>>Can you tell us what index is out of whack? Also, can you tell us the expression you used to create the index in the first place?
>>>>
>>>>You don't check that SEEK indeed FOUND a record. That would be a good thing to do.
>>>>
>>>>What is the current setting of (SET) DELETED? If it is OFF, then are there deleted records that can be being SUMmed too?
>>>>
>>>>I assume that your SEEK is to position the ARIITEMS table at the first qualifying record. In that case you might use the WHILE clause of SUM (instead of FOR).
>>>>
>>>>Is "arinvoic.AMOUNT" also calculated with the ROUND() function? A rounded "arsum" might not be exactly the same value as the calculation you are doing.
>>>>
>>>>good luck
>>>>
>>>>
>>>>>I have a problem with an index that got out of wack. I have two tables, invoice header and invoice items. The total on the invoice header is supposed to be equal to the total on the items. In the case of the wacked out index this isnt true, but because the index is picking up the wrong information, it thinks its true. I have a routine in my posting run that checks the totals and stops posting, but if the index is out of wack it doesnt catch it. I need it to. In my posting run I call a method that contains:
>>>>>
>>>>>METHOD artotalchk
>>>>>*****************
>>>>>LOCAL arsum, llOK
>>>>>SELECT ariitems
>>>>>SEEK STR(arinvoic.REFNO,6)
>>>>>SUM ROUND(qty*amount,2) TO arsum FOR refno = arinvoic.REFNO
>>>>>SELECT arinvoic
>>>>>IF arsum <> arinvoic.AMOUNT - arinvoic.gstamt - arinvoic.pstamt
>>>>> DO printerr WITH "Invoice reference "+alltrim(str(arinvoic.REFNO))+" header/item mismatch"
>>>>> m.llOK = .F.
>>>>>ELSE
>>>>> m.llOK = .T.
>>>>>ENDIF
>>>>>
>>>>>
>>>>>I am wondering if there is another way that would avoid the index that could be corrupted. I think the index corruption is happening somewhere else and the customer isnt aware of it, so they dont reindex and data is being stored erronenously.
>>>>>
>>>>>TIA,
>>>>>Sandi