#1 - where is the Index actually stored, so I can look at the indexed resaults of the column.

#2 - Problem == I have an index that clearly returns a value, shown by "LIST file BTREEINDEX_COLUMN"

When the date changes the information is somehow being interpreted incorrectly (using btree.extract) to return keys from index.

I then rebuild the index in the indexing menu, and the resualts are now correct (using btree.extract)

But I have not changed anything and the value shown by

"LIST file BTREEINDEX_COLUMN" is exactly the same.

Therefore btree.extract must be extracting information in such a way that I am getting incorrect resaults.

So I need to find the actual location of the stored indexed btree so I can compare the before and after rebuild.

HAS ANYONE EVERY HEARD OF THIS BEFORE, WHERE BTREE.EXTRACT RETURNS WEIRD RESAULTS THAT DONT MAKE SENCE WITH THE ACTUAL BTREE.INDEX

ANY AND ALL COMMENTS WILL BE APPRECIATED — THANK YOU!!!

Just to get the obvious question out of the way, your BTREEINDEX_COLUMN is not a symbolic that perhaps XLATEs to another table to get the date value, is it? You didn't post any details on the nature of the indexed column so I had to ask… sorry.

Thanks for the input but no that is too obvious a problem as anyone should know that indexing a symbolic column which brings in data from another source does not work.

But the column I am using is a symbolic, it is indexed, but only refers to @record and date().

Now the date() function I assume will work properly in a indexed symbolic, but I could be wrong.

Again though my point is the symbolic returns the same CORRECT value all the time. The problem is when the actual date of the system changes to the next day. I belive it is becuase the btree.extract is retrieveing incorrect resaults but I need to see the actual physical btree index created so I can compare it to the rebuild done when the date changes.

date() is changing dynamically whenever the system date changes so naturally when you rebuild the indexes they will be different from the day before.

The symbolic is re-evaluated by rlist every time you refer to it.

Thus if I have a symbolic THISDATE (formula @ANS=DATE()) indexed when I save the record on 1 JAN 2003 the value will get stored in the index as 12785.

When I do a SELECT MYFILE WITH THISDATE EQ '1 JAN 2003' the RLIST processor will re-evaluate THISDATE for each record and compare this against the index keys for the internal date value of 12785. This is fine if I run the query on 1 JAN 2003 because that is what THISDATE evaluates to during the query. If I run the same query on 2 JAN 2003 THISDATE will evaluate at the time of the query to 2 JAN 2003 or 12786 ICONVed and will not match against the indexed value of 12785.

If I then rebuild the index on 2 JAN 2003 THISDATE will evaluate to 12786 internal and will be indexed under 12786. There will no longer be keys under 12785.

Perhaps if we understand what you are trying to do with this index we can figure out what is going on.

What is the formula you are using for the symbolic?

If you are trying to timestamp the record to track modification the timestamp has to be "hard" data - an actual column in the row entered at the time the row is written to the table. A MFS or wrapup process in the data entry window is how this is normally handled.

Results are inpredictable when indexed fields does not depend only on record/key information. … Such use is misunderstanding of BTREE indexing.

Index is updated whenever record/key information is changed.

(At least update is planed, and indexor does it later).

BTREE.USAGE does not require the original table at all (if index is flushed) … it uses information stored elsewhere (!FILE).

The problem is related to the use of the Date() function which will change every day. Btree-Extract does NOT force an update to the index. It just uses what is there. By using a current date, the contents of @RECORD will not change but the contents of a field depending on a change of date will. This will not be seen by the index processor since the contents of the "Real" field haven't changed at all.

For example, I use a field called "Days_Old" for invoice aging which is calculated by subtracting the Invoice_Date (real field) from the current date .. @ANS={INVOICE_DATE)-DATE(). This is fine for reporting but won't work as an index ..

SELECT INVOICE WITH DAYS_OLD ] 30 would be hopelessly inaccurate. However, if the index is placed on INVOICE_DATE then

SELECT INVOICE WITH INVOICE_DATE FROM "somedate" TO "otherdate" AND WITH DAYS_OLD ] 30 works just fine.

Maybe I'm barking up the wrong tree .. as some old dogs will.

Don M.

So now you know that using DATE() is just as much as issue as using a XLATE in the formula. The indexing system doesn't know that the index value has changed.

If you are looking for some type of expiration date. Perhaps the formula can just reference that date from data in @RECORD. For instance, if you want to find rows with an expiration date 30 days from the {INVOICE_DATE} then the formula looks like @ANS={INVOICE_DATE} + 30. Assumming INVOICE_DATE is unchanging.

Dave

Dave ..

But of course .. that's how I calculate the DUE_DATE field: @ANS={INVOICE_DATE}+{PAY_TERMS_DATE} .. which is parsed from the TERMS field in the entry screen, but the DUE_DATE is kept in a real field.

Don M.

Thank you all for your input, you basicly confirmed something I thought of last night, the records are being indexed when they are created but they are not changeing with the date() function.

So the btree.extract is returning the index values where as when I do

"LIST file BTREE.COLUMN" the value comes out correctly.

Thanks for all your help!!!

Well this is not working out, all your information about the date is great but its still not solving my issue.

I need to do a direct comparision against the actual calender date thus my equation is

if Value < date() then

So I need some way of referring to the calender date in the symbolic.

This may not be possiable and I may have to rewrite this section.

ANY COMMENTS ON HOW I AM GOING TO MAKE THIS WORK

I'm still not sure what you want to do but the formula:

If Value < date() then xxx

is valid and should work for comparisons against the current system date.

Consider if the system date is today's date which happens to be 29 JAN 2003 as I write this message the function DATE() will return the value 12813. Remember that DATE()'s return value is in integer format and the date value you are comparing it to must be in integer format also.

So Value=ICONV('28 JAN 2003','D')=12812 would evaluate to true in the above formula.

Where is Value getting set? What is being done if the statement is true or false?

None of that matters warren, but if you must know

I am doing the folowing:

if Date + 1 < date() then

 loop

   checks Date against weekends and holidays

   if weekend or holiday then date += 1 

 repeat 

end

So as you can see it does not matter what goes on after the only part that matters is that I need to do a direct comparrision of a date in internal format as you mentioned (an integer) against the interger date returned by the date() function but I can not use the date() function in a indexed symbolic, so HOW do I do the comparrision ???

Ok, having been in a sort of a mental fog the past few years I'm guessing what you want is a function that will either tell you if a given date is a weekend or a holiday or a one that will return the date of the next working day if a given date is a holiday or weekend.

Determining if a date is a weekend is easily accomplished with the modulo function on an integer format date:

x=mod(idate,7)

where: 0=Sunday, 1=Monday, 2=Tuesday…6=Saturday

So you can have a function: ISWEEKEND

subroutine ISWEEKEND(idate)

rval=0

dow=mod(idate,7)

if dow eq 0 or dow eq 1 then rval=1

return rval

So your symbolic to return the next working day after a weekend would be:

declare function ISWEEKEND

date=whatever date your are testing, from @record hopefully

loop

while ISWEEKEND(date)

date += 1

repeat

@ans=date

Determining if a date is a holiday requires a table lookup of some sort. You need a file which either has a record for each holiday (key could be integer format of date) or a single record with each date delimited. The first method you would read to see if the given date exists in the file or not. The second method you would have to read the record in question and scan the data (most likely with a locate statement) to look for a match.

You could always make the function so that you just give it a date and it returns the next working day if that date should be a weekend or holiday.

if Date + 1 < date() then

loop

checks Date against weekends and holidays

if weekend or holiday then date += 1

repeat

end

So as you can see it does not matter what goes on after the only part that matters is that I need to do a direct comparrision of a date in internal format as you mentioned (an integer) against the interger date returned by the date() function but I can not use the date() function in a indexed symbolic, so HOW do I do the comparrision ???

---

You can use the date() function in an indexed symbolic. However the indexed value will not change until the index is rebuilt on a date different from that on which the record was first created or last edited.

To keep the indexed values current, you must rebuild the index daily. To make sure the values never change, you must never rebuild the index. Is either of these what you are trying to obtain? Or is it something else?

It's not clear from your query and responses what [b]Date[b] in line 1 of your snippet represents. Is it a fixed value or your symbolic?

If symbolic, is there a compelling reason it cannot be real data?

Bill

Waren's response solves the conditional branch.

What I don't understand is the condition.

Your symbolic has two states (one final and potentialy one initial).

Indexor must be invoked somehow to reflect the change to final state.

… it cannot work well

For planned actions we use FUTURE table.

Subkey contains date when the action should be taken.

Periodical processing performs required actions.

… no indexing issue, but issue of FUTURE processing …

P.S.: :))

At least this can be used just to inform indexor of switching to final state of your symbolic. From that time btree will work well.

View this thread on the forum...