Randomly displaying single or multiple records 2

[tsibert]

I haven’t used foxpro in a while but recently had the need to dig it out. In the past I have used foxpro to link multiple tables together based on tables having the fields with the same name and size and data types. But I’m running into a issue where when the tables are linked and the relationship from A set into B I sometimes see all the records from B and other times I can only see the first matching record but when I click into B all the records show. Never had this issue before.., any help greatly appreciated

 

[Mike Lewis]

Whjat method are you using to link the tables? Are you using SET RELATION, or SELECT - SQL, or some other mthod?

Mike

 

[tsibert]

Thank you for your reply.. after an index has been created in B I select A and issue command set relation to (common field) into B

 

[Chris Miller]

Assuming you set a relation.

After setting a relation it becomes active immediately, but to make sure you better move to some specific record in the table where a relation starts. It matters if you also SET SKIP or not into other tables what exactly you see in other browse windows.

And you better only browse after setting relations, i.e. write a prg that uses tables, makes relations and finally browses tables starting with the ones from which a relation starts, then next hierachy level and so on, when you have several levels of 1:n:m relations.

If you browse a table on the n side of a 1:n relation, the relation is yet not limiting the records in the table and you see all, you've not yet had a specific head/parent record that limits the browse to child records of that parent only.

And last not least, you can get quirky behavior from tables pointing at each other and circular relations, if you do set relations within a form when the dataenvironment of the form already has some relations set, that can bite you.

 

[Chris Miller]

after an index has been created in B I select A and issue command set relation to (common field) into B

There's nothing wrong with that, though you wouldn't necessarily create an index everytime. Once it exists you can SET ORDER to it (or a tag of it with a CDX, which is recommended).
If you start a relation in a child table to a parent, i.e. select orderitems and set relation to orderid into orders, you get a technically working relation, but it won't reflect the 1:n relationship of orders into orderitems.

With a relation set this way around traversing the orderitems, you'll always see the order an item belongs to in the orders browse. An orderitems obviously can't point to tw orders, it only belongs to one by its orderid and so that relation (it can be useful, too) will always only show one order, nothing wrong with that.

If you want to see all orderitems of an order, the relation has to be set the other way around, you select order and set relation into orderitems. Orderitems first need to be sorted with an index on orderid, of course, but you know that. The start/end poions of a relation makes a difference, though.

Of course, I don't know what data you have, I picked order/orderitems as a well known and - even if not known - easy to understand example where an order is stored into a main record of an order table and multiple orderitem records of an orderitem table that all point to their main order record by an orderid that's common to both tables, unique in orders, foreign key (able to repeat as many times as you have items) in orderitems.

There's a bit more that could play a role, like an index not in sync with the data (can happen with IDX easily) and filtered indexes and VFPs very special unique index type that's not disallowing double values but filters them. Llet's not start talking here, I don't assume you even have those special cases, but only say it could of course mend a broken relationship if you REINDEX or create indexes fresh, from that persepctive it's always good, just a waset of time for healthy situations and not the only way things can become ghastly. I'd always first look into whether you have your relations the right way, then, before index defects play a role, it can simply be filters set on the workareas, not only indirectly by filtered indexes, but by actual SET FILTER conditions, of course.

If you start from an empty datasession, create indexes freshly, don't set filters, don't set skip - then it can mainly be the wrong direction of the relations and the handling of the browse windows, i.e. the order in which you open and use them. The side from which a relation starts (let's say A) is unfiltered and picking a record in the full list triggers the relation mechanism to filter the records in the table into which the relation points, let's say B, that's the major mechanism of activities. This filtering also happens, if the table B is not yet browsed, but BROWSE then may still show you all records in B, better have B already open in a window before you pick a record in A and trigger the relation mechanism, then you're on the sure side of the relation acting.

 

[tsibert]

Thank you Mike and Chris for your replies. I will try to describe this situation better. I have two tables. Table A is a 5000 record unique character field 240 character's in length. Table B is a much larger file that has many columns and a field/column with 240 characters the same name and data type as A. I want to navigate through the records in A and only see the matching records from B.while navigating through A after relationship has been set into B I’m randomly seeing all records that match in B or only the first record is displayed? If when I get the single first record and ten click into B then it will show all records. I’m going to try to clean trim the two common columns and rebuild my index to see if that helps. I’ve never seen this random display issue before?

 

[Chris Miller]

You're at the maximum width of an index with 240 characters, do you use a collation (unintentionally, maybe) other than MACHINE? That would have an effect on the index creation and matching, perhaps also of relations, but it wouldn't explain why you see the first match and further matches appear when you activate the window of B. It would only explain smaller than expected sets of matching rows.

There is another reason it influences the "result set" in B for a record in A, though, as you then could only index 120 characters. New indexing than should fail, on one side, and already existing indexes will work with the collation as it was set when they were created.

 

[Piotr Czekalski]

@tsibert, code works.

Index keys total over 240 characters.

There may be something wrong with your object-oriented programming code.

CREATE TABLE table1 ;
   (FIELD1 C(240))
 
 
CREATE TABLE table2 ;
   (FIELD1 C(240)   ;
   ,FIELD2 C(10)    ;
   ,FIELD3 C(20)    ;
   )  
 
Sele table1

Dele Tag All
Inde ON Field1 Tag T1

Sele table2
Dele Tag All
Inde ON Field1 Tag T2
Inde ON Field2 Tag T3
Inde ON Field3 Tag T4


Set Order To Tag T2

INSERT INTO  table1 VALUES(Repl("A",240))
INSERT INTO  table1 VALUES(Repl("B",240))
INSERT INTO  table1 VALUES(Repl("C",240))


INSERT INTO  table2 VALUES(Repl("A",240),"A1","A1")
INSERT INTO  table2 VALUES(Repl("A",240),"A2","A2")
INSERT INTO  table2 VALUES(Repl("A",240),"A3","A3")
INSERT INTO  table2 VALUES(Repl("B",240),"B1","B1")
INSERT INTO  table2 VALUES(Repl("B",240),"B2","B2")
INSERT INTO  table2 VALUES(Repl("B",240),"B3","B3")
INSERT INTO  table2 VALUES(Repl("C",240),"C1","C1")
INSERT INTO  table2 VALUES(Repl("C",240),"C2","C2")
INSERT INTO  table2 VALUES(Repl("C",240),"C3","C3")

Sele Table1
Set Relation To Field1 Into Table2
Set Skip To Table2
Browse Fields Table1.Field1,Table2.Field2,Table2.Field3 Noedit

Close DataBases

 

[Chris Miller]

Do you want to say "avoid the problem by having just one BROWSE"?

Could be a solution, but not for more complicated scenarios involving several tables. I'd just for sake of clarity SET COLLATE TO "MACHINE" first and browse separately, no matter if with or without SET SKIP:

Set Collate To "MACHINE"
Create Table table1 ;
    (FIELD1 C(240))


Create Table table2 ;
    (FIELD1 C(240)   ;
    ,FIELD2 C(10)    ;
    ,FIELD3 C(20)    ;
    )

Sele table1
Index On FIELD1 Tag T1

Sele table2
Index On FIELD1 Tag T1
Index On FIELD2 Tag T2
Index On FIELD3 Tag T3

Set Order To Tag T1

Insert Into  table1 Values(Repl("A",240))
Insert Into  table1 Values(Repl("B",240))
Insert Into  table1 Values(Repl("C",240))


Insert Into  table2 Values(Repl("A",240),"A1","A1")
Insert Into  table2 Values(Repl("A",240),"A2","A2")
Insert Into  table2 Values(Repl("A",240),"A3","A3")
Insert Into  table2 Values(Repl("B",240),"B1","B1")
Insert Into  table2 Values(Repl("B",240),"B2","B2")
Insert Into  table2 Values(Repl("B",240),"B3","B3")
Insert Into  table2 Values(Repl("C",240),"C1","C1")
Insert Into  table2 Values(Repl("C",240),"C2","C2")
Insert Into  table2 Values(Repl("C",240),"C3","C3")


Select table2
Browse Nowait Name B2
B2.Left = 440
B2.Column1.Width = 50


Select table1
Locate
Browse Nowait Name B1
B1.Width = 420

Set Relation To FIELD1 Into table2
*Set Skip To Table2

 

[Piotr Czekalski]

Chris, I want to say that it is not a problem of 240 characters in the index key. The mechanism works. We don't know the code of program. Any error in the program code is possible. Why does he need such a large text field as an index key?

Of course 2 browsers look better

 

[Chris Miller]

Yes, it should also work no matter what content, but there are some quirks with indexes and matching in other collations than MACHINE. And if he used an index on LEFT(field1,120) he could also use other collations without error, just because the field was the width, the index doesn't have to. Also I have a faint memory of effects of using an index created with collation X while having set another collation Y causing trouble in older VFP versions, Or - affecting relations, when the collation of table data differs from the index. I think they fixed some ussies there in VFP8 or 9.

A half index would obviously not match the full values, but would also not explain records appearing just by activating a window. That points out some window refresh getting stuck and getting triggered by activating the window and causing a repaint - more of a grpahics problem, overall.

All you can try is reindex and then set the relation in the right direction, it then should work flawless, otherwise I'd like to get a sample of the data and code.

 

[Mike Lewis]

Piotr and Tsibert,

If your 240-character index keys work, that's fine. You probably won't want to change it.

But I have to say that 240 characters is exceptionally long for a key, and very unusual. The point is that when you have two or more related tables, a lot of comparisons will take place as you navigate or search or browse the tables. Comparing two 240-character values will take considerably longer than comparing, say, two integers. In other words, the performance of your application is likely to suffer.

That probably won't be a problem if your tables are quite small. But if that's not the case, then you will most likely notice a loss or performance. It would be much better to use so-called surrogate keys. These would be integers (and perhaps autoinc integers) that uniquely identify each record. These integers needn't be visible to the users, who will still see the 240-character fields and will still use them to specify queries and so on. But they will be used to serve as primary and foreign keys and to define the relationships between the tables.

Mike

 

[Piotr Czekalski]

Dear Mike, I don't use such long index keys. I consider that using long index keys is a big database project mistake. Please don't misinterpret my statements

 

[tsibert]

UP DATE! Thanks to everyone’s help with this… I’m not sure how it fixed this issue? But others were correct… I had fields that were 254 characters I used a left 240 to create the index… I changed the field sizes both to 240 and recreated the index. Bingo, now when scrolling A the B table displays all of its records. Thanks again for everyone’s comments… what a great community!

 

[Piotr Czekalski]

Alles klar, Herr Kommissar ...

 

[Chris Miller]

Ok, thanks for the info.

But it should work anyway, when I adapt demo code to use C(254) fields, Repl("A",254) etc. and index on LEFT(field1,240), I don't get the error you describe.

You must have had an index anomaly, though you said you create the indexes every time, which makes that almost impossible - a fresh index has no errors unless something goes extremely wrong a disk sector or flash memory cell of an SSD has an error and verification/correction mechanisms of the hdd controller also fail. Besides, in itself it would only explain unexpected matches, not the glitch of first only seeing one match and then all.

It's also usually not necessary, indexes update themselves, once they are created, When you use INDEX ON expression TAG tagname an index becomes part of the main tables CDX file (the CDX file with the same stem name as the DBF file) and any CDX tag updates whenever data changes or is deleted, in short it stays in synch with DBF data - and even FPT data, when you index on LEFT(memofield,240) or shorter. The only good thing about fresh indexing is that the index then is created optimally and most likely without any error.

You can't index fields larger than 240 characters, that errors, you had to use LEFT() - at least that's the usual way to get the left part, you could also index on RIGHT() or SUBSTR() parts of a field, but that's clearly very unintuitive. Anyway, any index expression also works, you just also have to SET RELATION TO LEFT(FIELD1,240) INTO table2 or in general the same index expression as the index on table2 used to sort it and then filter it with the relation. SET RELATION TO field1 INTO table2 doesn't error even if the index is on LEFT(field1,240), and that also works. Just like your SEEKS don't need to seek a value that's exactly as long as the index expression, i.e. you can SEEK 'A' on a char field longer than 1 char and you can also seek a longer value than the index expression is. I bet you even used this without thinking about it when you define a usual index on a field and then SEEK 'Smith' in a lastname field that's also allowing longer last names. And you can even SEEK 'Anagnostokokudulopoulos' in a index on a field that's shorter than that,

To be on the safe side, I'd always SET RELATION to the same expression as is used by the index of the table the relation is set INTO.

There is one reason that favors not using a LEFT() expression as index to later use in a RELATION: The relation will only match the LEFT part of a field, not the full field. And that would give you a matched set of records that's including not fully matching values, because just the left part has to match. That's also giving you another than the expected set of records in the table2 browse, but rather too much than only 1, so still won't explain the glitch you saw. By shortening the field to 240 chars you made it all simpler and straight forward. Having new indexes, obviously, after changing that, put you into a cleaner situation, that's mainly all. There's no conclusive answer to why the glitch happens, it just doesn't happen usually, when everything is clean. You could and should verify your data, now that you shortened it, to only have duplicates as far as duplicates are allowed in both tables. I don't know, you don't have to take the usual key matching rules, you could define a relation of multiple rows of table1 matching multiple rows of table2, then duplicates are obviously allowed on both sides, but a SET RELATION will never reflect such a direct n:m relationship because it always acts on one record of the table the relation starts in and 1-n records in the table it is set INTO and ends in. An n:m relation would technically require to split it into a n:1 an 1:m part where the 1 is a third central table to both relations, that will only allow a combination of a tuple of values once. All a single relation can be is a 1:1 or a 1:n relationship.

What remains after all this is the thoughts of others, and let me join in, that using such a long field for a relation is awkward. I guess the reason has to be outside of the usage of this field for relating the two tables, i.e. the field has another meaning, mainly, than for joining two tables.

This question comes up in any developer knowing that you design data to have key values, primary and foreign keys, usually in single fields of a table, that are solely used for pointing out the relationships between records. And those are the usual fields to use in a RELATION. Even if you consider a random key in a vast realm of possible values that makes double values as close to impossible as you can with finite bits: The usual choice is GUID for that, not a char(254) or char(240) field.

 

[Piotr Czekalski]

Chris is 100% right. I advise you @tsibert to reorganize the table structures and add small ID fields (FIELD_ID). These will be index keys. In the "A" area, unique keys. In the "B" area, they will be repeated. Unique keys are easy to create in numeric ID fields (INTEGER). 5-6 characters are enough, depending on the planned number of records. Below is a simple example of how to create such keys in FIELD_ID. Other sample data are contained in fields FIELD1 and FIELD2.

**************
* CREATE TABLE
***************


IF ! File("akod_demo.DBF")

  CREATE TABLE akod_demo ;
     (FIELD_ID N(5)      ;
     ,FIELD1 C(24)       ;
     ,FIELD2 C(24 )      ;
     )

  Dele Tag All

  Inde On Field_id Tag Fid
  Inde On Field1   Tag F1
  Inde On Field2   Tag F2

  Use

EndIF




**************
* program body
***************

Set Exclu OFF


Wait "Processing" Window NoWait

Use akod_demo Alias Demo

For I=1 To 10000
 
   **
   Ir=1
   Do While ! RLock("0","Demo")
     Ir=Ir+1
     IF Ir>10
       Wait "Houston, we have a problem" Wind
       Quit
     EndIF
     Delay(100)
   EndDo
   **
 
 
   **
   X_FId=Auto_ID()
   **

   Appe Blank
 
   Repl Field_Id With  X_FId
   Repl Field1   With  "A"+Str(I,5)
   Repl Field2   With  "B"+Str(I,5)

   Unlock
 
EndFor


Wait Window "Finished  "  TimeOut 2


Go Top
Browse
 
Use

IF MessageBox([erase table "akod_demo.DBF" ?],36)=6
  Erase akod_demo.DBF
  Erase akod_demo.CDX
EndIF



Retu




**********
* library
**********



Function Auto_Id
**********************
* Automatic coding ID
**********************

Sele Max(Field_id) AS My_Id ;
     From Demo              ;
     Into Cursor Temp_Id

Sele Demo
IF RecCount()>0
  Xo_Auto_ID=Temp_Id.My_Id+1
Else
  Xo_Auto_ID=1
EndIF

Retu Xo_Auto_ID




Function Delay

Param _tDelay

Priva _lnstart

_lnstart=SECONDS()
Do While (SECONDS()-_lnstart)*1000 < _tDelay
EndDo

Retu .T.

 

[Piotr Czekalski]

I publish slightly modified code. Tested with 7 simultaneously working processes.

**************
* CREATE TABLE
***************


IF ! File("ID_demo.DBF")

  CREATE TABLE ID_demo  ;
     (FIELD_ID   N(5)     ;
     ,FIELD1     C(24)    ;
     ,FIELD2     C(24 )   ;
     ,FIELD3     C(8)     ;
     )

  Dele Tag All

  Inde On Field_id  Tag Fid
  Inde On Field1    Tag F1
  Inde On Field2    Tag F2

  Use

EndIF




**************
* program body
***************

Set Exclu OFF

X_Start_Process=Time()

X_Max_I=100


Define Window XXX From 10,20 to 30,70
Activa Window XXX


@ 8,10 Say "Time start process: "+X_Start_Process


Set Reprocess To 10 Seconds

Use ID_demo Alias Demo

Set Reprocess To 2 Seconds

For I=1 To X_Max_I
 
   @ 10,18 Say "Progress:"+Str((I/X_Max_I*100),3)+"%"
 
   **
   Ir=1
   Do While ! RLock("0","Demo")
     Ir=Ir+1
     IF Ir>10
       Wait "Houston, we have a problem" Wind
       Quit
     EndIF
     Delay(10)
   EndDo
   **
 
 
   **
   X_FId=Auto_ID()
   **

   Appe Blank
 
   Repl Field_Id   With  X_FId
   Repl Field1     With  "  my data  - A "+Str(I,3)
   Repl Field2     With  "  my dfata - B "+Str(I,3)
   Repl Field3     With  X_Start_Process

   Unlock
  

   Delay(500)
 
EndFor


Delay(500)

Deactiva Window XXX

Go Top
Browse Fields FIELD_ID,Field1,Field2,Field3 :H="start time"
 
Use

Set Reprocess To 0

Retu




**********
* library
**********



Function Auto_Id
**********************
* Automatic coding ID
**********************

Sele Max(Field_id) AS My_Id ;
     From Demo              ;
     Into Cursor Temp_Id

Sele Demo
IF RecCount()>0
  Xo_Auto_ID=Temp_Id.My_Id+1
Else
  Xo_Auto_ID=1
EndIF

Retu Xo_Auto_ID




Function Delay

Param _tDelay

Priva _lnstart

_lnstart=SECONDS()
Do While (SECONDS()-_lnstart)*1000 < _tDelay
EndDo

Retu .T.

 

[Chris Miller]

Integer Autoinc relaxes the semaphore or mutex lock problem you have without it extremely well, as it works flawless, even in networks that have the opprtunistic locking problem making DBFs on such networks problematic, overall. The nextvalue for autoinc is increased before it is used, so even concurrently inserting/appending clients never see and use the same next autoinc value, even if the autoinc id would only be used in a buffered view/workarea on a client and isn't yet stored in the centralized dbf file, the next autoinc already is.

 

[Piotr Czekalski]

You are right Chris. I use VFP 6.0 in some of my programs. How can I do autoincrement, are there any way ? There is no Integer(Autoinc) field type. I moved some programs to VFP9 but I left SQL code.