I have two files that have multiple records that i want to setup a reclation index for. Can someone tell me if I have set the index up wrong, as the system is not alway updating the index.

The internet system writes data to the temp file as an employee moves from one screen to another. When the employee accepts there elections the system writes the data from the temp file to the live file. The problem is at times when records are written to the live/temp file the index is not being updated all the time, suggestions. There are no other indexs on these files.

INET_BENEFITS_TRANS is my source (SOURCE FILE ONE) or data file. The file has a multi-part key, one part being EMP_NO. There is many records in this file for one employee.

INET_BENEFITS_TRANS_TEMP is my source (SOURCE FILE two) or data file. The file has a multi-part key, one part being EMP_NO. There is many records in this file for one employee (really this is a duplicate of the INET_BENEFITS_TRANS file.

INET_BENEFITS_TRANS_INDEX is my destination or index file. The key to this file is a singled valued field called EMP_NO. I have created a dict field call ACCEPT_KEYS (position one, and MV) which contains the key values from INET_BENEFITS_TRANS. I have created an other dict field call TEMP_KEYS (position two, and MV) which contains the key values from INET_BENEFITS_TRANS_TEMP.

So the relation is EMP_NO from INET_BENEFITS_TRANS to ACCEPT_KEYS in INET_BENEFITS_TRANS_INDEX and EMP_NO from INET_BENEFITS_TRANS_TEMP to TEMP_KEYS in INET_BENEFITS_TRANS_INDEX.

Tom

Not sure without seeing your code. It is possible the destination file (with ACCEPT_KEYS) is getting locked.

In any event at Prosolve we tend not to use relational indexes at all. How about putting a btree on EMP_NO in the transaction file(s). Then if you need a list of the transaction records you can easily get at them using btree.extract. It is simple enough to then turn the results of btree.extract into a browse list.

Tony

Tom,

Tony says relational indexing can't be trusted. Another problem is relational indexes can get full (might not happen in your specific example, of course). The problem is the indexed keys are stored in fields in ordinary tables. When you get too many related keys the row hits the 64K limit. (Or is it 32K? Or Y2K? At least it's not OK.)

The only place I use rel ixes is a couple of static tables.

- Oystein -

Oystein,

Tony didn't say Relational indexes can't be trusted, he only said they don't use them. I would be interested to know his reasons.

Truthfully, we also don't have a single deployed application (that we've written) that uses relational indexes (AREV or OI). Mainly this is because of the potential 64K problem. For this same reason we don't use QuickDex either. However, I must breakdown and confess that I got lazy and choose to use relational indexes recently in our in-house time and attendance system. We needed a better way to quickly associate our work entries with specific client projects so I created a Projects database and decided to link our work logs to this relationally. My "insanity" defense is that no single project could ever have enough work entries to cause a 64K blow up. (Famous last words…)

dbakke@srpcs.com

SRP Computer Solutions

Don,

You're right. I misread Tony. But I've met this pair of experienced developers who didn't trust relational indexing. They too recommended using Btree.Extract instead.

- Oystein -

I don't like btree index as they are slow to access and you really have to do an index update to be sure that you are getting all keys. Relation indexes are must faster way to access a group of records as long as a single group doesn't have 64k worth of keys.

in my current program that is not going to be a problem.

Why don't you guy's like relational indexes (besides the size think)?

Tom

Tom (and Don & Oystein)

We don't use them because of 64k issue for sure but also because of previous unreliability - in Arev at least. In Arev, I guess I'm talking 1.x or 2.x versions as we haven't used them since, if you have a parent window open then choose relational window with a browse list of child records then tried to add a new record the relational index would not be added as the indexer couldn't get a lock on the parent. I my experience this then often simply didn't update at all even, after all records were released.

Now it may be that this has been fixed in later versions. But something just doesn't seem right about having data and indexes in the same file. I much prefer to have btree data which is held in its own logical file. I must say that I have never found btree.extract to be slow and using btree.extract with perhaps v119 to sort keys into a particular order is far more flexible than living with the once only sort order assigned to the relational index.

It seemed from your original post Tom that you may have been getting similar problems as I experienced with Arev all those years ago.

Tony

Tom,

About the unreliability issue: I have no concrete experience with that. I just referred what I vaguely remember having heard.

64K: This issue is enough for me not wanting to use relational indexes. I have a possible application (as in use, not program) of relational indexes, but I'll use Btree.Extract instead. Admittedly Btree.Extract has a 64K limit too, but I can program around that in a graceful way. I don't know of any way I can get a relational index, and the rows it is stored in, to behave gracefully when it gets full.

- Oystein -

I have just developed an in house application aimed at contact management, and I found it easier to use relational indexes. I also viewed the once only sort order (that Tony didn't like) index definition as a convenience, for it allows me to present data (specially dates) in a last-in first-out fashion without furter index or data handling.

However, I have some complaints; Btrees and Relationals don't seem to collaborate with each other. When I introduced relationals to a table, btrees stopped to auto update. I had to delete btrees and trust fast key seeking to my machine's performance.

Another problem is Oi's APPBACKUP doesn't like relationals too, and won't backup them.

Another problem is Oi's APPBACKUP doesn't like relationals too, and won't backup them

Can you elaborate on this. Relational indexes are stored as dictionary flags or protected data columns. How could APPBACKUP not back them up when selecting the Data Tables option? Granted, we never use that option (or APPBACKUP anymore) but it would seem odd to me that it could selectively exclude relational index information.

dbakke@srpcs.com

SRP Computer Solutions

Don,

When I try to 'APPBACKUP' my application selecting 'copy data tables', I get a message like this:

Relational indexes will not be copied from the following tables

!my rel. indexes

!etc

press any key to continue

If I leave the copy tables option unselected, it proceeds, but then, when I restore the application, I sometimes get error messages related to my indexes.

I expected the copy table thing since neither ARev nor OI is very happy about copying files with relational indexing on it.

I didn't expect relationals to interfere with BTREE indexes. I don't use relationals often, but have in the past, and have a few clients that still do. I've never seen the addition of a relational index stop updates from occuring.

Relationals can be pretty squirrelly, but that's usually only in data integrety issues. They usually don't go through and muck up other things (except copy table).

Could you elaborate further? We might be able to eliminate the problem?

akaplan@sprezzatura.com

Sprezzatura Group

I guess I expected APPBACKUP to be doing more of an OS copy rather than a COPY_TABLE procedure. This explains why relationals have problems. This also affirms my decision never to use the Copy Table option as well.

dbakke@srpcs.com

SRP Computer Solutions

Aaron:

A description of tables and indexes follows, as well as where an how

I use the indexes.

1.- A btree index was added to column 'LAST_CALL' in table 'CUSTOMERS' wich by now has some 10000 records, with average 200 chars/record.

2.- I can print a report (from a button) listing calls by date, as follows:

RLIST('SELECT CUSTOMERS WITH LAST_CALL EQ ':date:' BY CUSTID',4,

"CUSTCALLS"," "," ")

RetVal=UTILITY("RUNWIN","REPORTER.EXE /AP=CONTACTS /UN=SYSPROG /SELTYPE=

REVELATIONBASIC /QU=1 /PV=1 /RP=LASTCALL.RPT /RE=1 /SEL=GETLIST CUSTCALLS")

Date is previously entered at an edit line.

3.- New customers are added to table and the report shows accurate info.

4.- A need arose to show customers with a call due for selected date, so I added a relational index whose definition is:

SOURCE TABLE: CUSTOMERS

SOURCE COLUMN: NEXT_CALL

DEST. TABLE: CUST_CALLS

DEST. COLUMN: CUSTID

5.- Tables 'CUSTOMERS' and 'CUST_CALLS' are shown in 'CALLS_FOR_TODAY' form, where I display:

- Today's date

- All Id's for customers with a call due for today, plus,

- contact info, subject, etc.

This works OK and the info is always up to date.

6.- Report shows inacurate info (or no info at all) so indexes need to be rebuilt (not just updated) every time a new customer is added to 'CUSTOMERS' table.

7.- I removed btree indexes for 'CUSTOMERS' table, and the reporter worked OK again.

So, I would ask:

Is it an index, or reporter (or both) related problem? Do I really need Btrees?

Regards,

Ilde.

View this thread on the Works forum...