Re: Index on collector tag collector unique

[Michael Madigan]

Thanks Ted,

I just realized that If I set deleted off, I get the correct results, so the 
first record with collector 31 must be deleted.   
I will just use the SQL statement you suggested to make sure it's always 
correct.  
Thank you.  



  From: Ted Roche <tedro...@gmail.com>
 To: "profox@leafe.com" <profox@leafe.com> 
 Sent: Monday, November 28, 2016 3:46 PM
 Subject: Re: Index on collector tag collector unique
  
Pretty weird. That's a bug. Or perhaps a corruption in the existing indexes.

SELECT DISTINCT collector in MyTable ORDER BY collector INTO CURSOR YourCursor

can give you the same thing, real quick if you already have an index
tag on collector, and even if you don't.



On Mon, Nov 28, 2016 at 3:30 PM, Michael Madigan <mmadi10...@yahoo.com> wrote:
> Strange behavior I've never seen before
> I have a 3 character field called collector.    I have many records with 
> collector= '31' .  When I index using the command "Index on collector tag 
> collector unique"  I get every one except collector 31.  I get collector 3, I 
> get collector 30, but I don't get collector 31.  There are over 2000 records 
> with collector = '31'
>
> I realize you have to reindex every time to make sure you have every 
> collector accounted for, so it's not that
>
> This is really odd.
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
>  text/plain (text body -- kept)
>  text/html
> ---
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/1894721230.1808438.1480368663...@mail.yahoo.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on collector tag collector unique

[Ted Roche]

Pretty weird. That's a bug. Or perhaps a corruption in the existing indexes.

SELECT DISTINCT collector in MyTable ORDER BY collector INTO CURSOR YourCursor

can give you the same thing, real quick if you already have an index
tag on collector, and even if you don't.



On Mon, Nov 28, 2016 at 3:30 PM, Michael Madigan  wrote:
> Strange behavior I've never seen before
> I have a 3 character field called collector.I have many records with 
> collector= '31' .   When I index using the command "Index on collector tag 
> collector unique"   I get every one except collector 31.   I get collector 3, 
> I get collector 30, but I don't get collector 31.   There are over 2000 
> records with collector = '31'
>
> I realize you have to reindex every time to make sure you have every 
> collector accounted for, so it's not that
>
> This is really odd.
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
>   text/plain (text body -- kept)
>   text/html
> ---
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CACW6n4tjJ2scWrT9cavOh9a7Ngsqs=lk_ymv98cnkafjter...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on collector tag collector unique

[Stephen the Cook]

Michael Madigan  wrote:
 Yep, you're right, I should have a collector database.
  This was a band-aid added quick and dirty.  Turned out to be too
 dirty. 

Why not create it via a select distinct into a table and then just normalize
your data?  Probably a lot of work after you hit all your input forms as
well as reports.

Stephen Russell
DBA / .Net Developer

Memphis TN 38115
901.246-0159

Our scientific power has outrun our spiritual power. We have guided
missiles and misguided men. Dr. Martin Luther King Jr.

http://spaces.msn.com/members/srussell/

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.16.14/636 - Release Date: 1/18/2007
4:00 AM
 



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on collector tag collector unique

[Rick Schummer]

Unique indexes are bad and the behavior you are observing is how they are 
designed. Direct from
HackFox:

A so-called unique index contains a key only for the first record that has a 
particular key value.
That is, once a key value occurs, no other records with that key value get 
added to the index.
There's no mechanism to enforce uniqueness here, just a way to find one of 
each. However, unique
indexes are not properly maintained. If you delete a record that's represented 
in the index, FoxPro
does not add the next record in the table that has the same key value. Don't 
ever use unique
indexes—there's always a better way to do it.

Use candidate index tags to get and enforce uniqueness or be prepared to 
rebuild the index each time
a record is deleted.


Rick
White Light Computing, Inc.

www.whitelightcomputing.com
www.rickschummer.com
586.254.2530 - office
586.254.2539 - fax
  


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael
Madigan
Sent: Wednesday, January 17, 2007 03:04 PM
To: [EMAIL PROTECTED]
Subject: Index on collector tag collector unique

OK, so I have a client database and on that database I have a collector ID.  I 
have a tag in the
compound index that is created by 

index on collector tag collector unique.  

This gives me a list of unique collectors using the system, each one assigned 
to one or more
clients.

I have a collector 5.  If I happen to delete the record which is in the unique 
index, there no
longer are any more '5's in the index.  Shouldn't it add another record with a 
collector='5' to that
index?

Am I understanding the way unique works?


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on collector tag collector unique

[Michael Madigan]

Thanks.  What do you mean by candidate?



--- Rick Schummer [EMAIL PROTECTED]
wrote:

 Unique indexes are bad and the behavior you are
 observing is how they are designed. Direct from
 HackFox:
 
 A so-called unique index contains a key only for
 the first record that has a particular key value.
 That is, once a key value occurs, no other records
 with that key value get added to the index.
 There's no mechanism to enforce uniqueness here,
 just a way to find one of each. However, unique
 indexes are not properly maintained. If you delete a
 record that's represented in the index, FoxPro
 does not add the next record in the table that has
 the same key value. Don't ever use unique
 indexes—there's always a better way to do it.
 
 Use candidate index tags to get and enforce
 uniqueness or be prepared to rebuild the index each
 time
 a record is deleted.
 
 
 Rick
 White Light Computing, Inc.
 
 www.whitelightcomputing.com
 www.rickschummer.com
 586.254.2530 - office
 586.254.2539 - fax
   
 
 
 -Original Message-
 From: [EMAIL PROTECTED]
 [mailto:[EMAIL PROTECTED] On Behalf Of
 Michael
 Madigan
 Sent: Wednesday, January 17, 2007 03:04 PM
 To: [EMAIL PROTECTED]
 Subject: Index on collector tag collector unique
 
 OK, so I have a client database and on that database
 I have a collector ID.  I have a tag in the
 compound index that is created by 
 
 index on collector tag collector unique.  
 
 This gives me a list of unique collectors using the
 system, each one assigned to one or more
 clients.
 
 I have a collector 5.  If I happen to delete the
 record which is in the unique index, there no
 longer are any more '5's in the index.  Shouldn't it
 add another record with a collector='5' to that
 index?
 
 Am I understanding the way unique works?
 
 
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on collector tag collector unique

[MB Software Solutions]

Michael Madigan wrote:
 Thanks.  What do you mean by candidate?

   
Same as a Primary index, meaning that the values in that field 
(combination) are all unique, i.e., without duplicates, but that it is 
not the primary index (because something else is generally).


-- 
Michael J. Babcock, MCP
MB Software Solutions, LLC
http://mbsoftwaresolutions.com
http://fabmate.com
Work smarter, not harder, with MBSS custom software solutions!



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on collector tag collector unique

[MB Software Solutions]

MB Software Solutions wrote:
 Michael Madigan wrote:
   
 Thanks.  What do you mean by candidate?

   
 
 Same as a Primary index, meaning that the values in that field 
 (combination) are all unique, i.e., without duplicates, but that it is 
 not the primary index (because something else is generally).


   
Let me add this:  ...but a field that might be intelligible or 
human-understandable (like a Customer_ID), unlike a Primary Key field 
where it may simply be an autoincrementing field.

-- 
Michael J. Babcock, MCP
MB Software Solutions, LLC
http://mbsoftwaresolutions.com
http://fabmate.com
Work smarter, not harder, with MBSS custom software solutions!



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on collector tag collector unique

[Rick Schummer]

It is a type of index for VFP data, which enforces uniqueness and does not 
accept nulls (just like a
primary key, but you can only have one primary key per table). Check out the 
topic Visual FoxPro
Index Types in the VFP Help file. It clearly explains all the different index 
types (including a
tip on how to avoid using UNIQUE indexes). 


Rick
White Light Computing, Inc.

www.whitelightcomputing.com
www.rickschummer.com
586.254.2530 - office
586.254.2539 - fax
  


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael
Madigan
Sent: Wednesday, January 17, 2007 04:00 PM
To: [EMAIL PROTECTED]
Subject: RE: Index on collector tag collector unique

Thanks.  What do you mean by candidate?





___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on collector tag collector unique

[Eugene Vital]

Michael Madigan wrote:
 OK, so I have a client database and on that database I
 have a collector ID.  I have a tag in the compound
 index that is created by 

 index on collector tag collector unique.  

 This gives me a list of unique collectors using the
 system, each one assigned to one or more clients.

 I have a collector 5.  If I happen to delete the
 record which is in the unique index, there no longer
 are any more '5's in the index.  Shouldn't it add
 another record with a collector='5' to that index?

 Am I understanding the way unique works?
   
Yes but I would follow some of the other posts here and use candidate
instead.

This does make me ask the question. Wouldn't you be better off having
the collectors in their own parent table and just relate them into
the customer table?

then you could just use a query.


select distinct customer.coll_id, collector.coll_name ;
   from customer ;
   left outer join collector on customer.coll_id == collector.col_id ;
   where customer.coll_id 0

you could also easily get a count for each collector too

select distinct customer.coll_id, collector.coll_name, count(*) as
coll_count ;
   from customer ;
   left outer join collector on customer.coll_id == collector.col_id ;
   where customer.coll_id 0


Of course normal indexing rules would apply to optimize the query...

just a thought



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on collector tag collector unique

[Michael Madigan]

Yep, you're right, I should have a collector database.
 This was a band-aid added quick and dirty.  Turned
out to be too dirty.



--- Eugene Vital [EMAIL PROTECTED] wrote:

 Michael Madigan wrote:
  OK, so I have a client database and on that
 database I
  have a collector ID.  I have a tag in the compound
  index that is created by 
 
  index on collector tag collector unique.  
 
  This gives me a list of unique collectors using
 the
  system, each one assigned to one or more clients.
 
  I have a collector 5.  If I happen to delete the
  record which is in the unique index, there no
 longer
  are any more '5's in the index.  Shouldn't it add
  another record with a collector='5' to that index?
 
  Am I understanding the way unique works?

 Yes but I would follow some of the other posts here
 and use candidate
 instead.
 
 This does make me ask the question. Wouldn't you be
 better off having
 the collectors in their own parent table and just
 relate them into
 the customer table?
 
 then you could just use a query.
 
 
 select distinct customer.coll_id,
 collector.coll_name ;
from customer ;
left outer join collector on customer.coll_id ==
 collector.col_id ;
where customer.coll_id 0
 
 you could also easily get a count for each collector
 too
 
 select distinct customer.coll_id,
 collector.coll_name, count(*) as
 coll_count ;
from customer ;
left outer join collector on customer.coll_id ==
 collector.col_id ;
where customer.coll_id 0
 
 
 Of course normal indexing rules would apply to
 optimize the query...
 
 just a thought
 
 
 
 ___
 Post Messages to: ProFox@leafe.com
 Subscription Maintenance:
 http://leafe.com/mailman/listinfo/profox
 OT-free version of this list:
 http://leafe.com/mailman/listinfo/profoxtech
 ** All postings, unless explicitly stated otherwise,
 are the opinions of the author, and do not
 constitute legal or medical advice. This statement
 is added to the messages for those lawyers who are
 too stupid to see the obvious.
 



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.