Unique Key Reference newid() 2

[itflash]

Hi all

My data entry form is for two tables.
Company and Contact.
Company are company details and Contact are that companies personal contacts.

My form is data bound with these two tables related by companyid
Each table has a unique id - companyid and contactid respectivley. The unique keys are created by newid() stored procedure (as in FoxPro Help Examples).

Now, if I issue an append blank at init and the user does not save the record, I have wasted an ID.
But I need the Company ID to put into the Contact records added by the user otherwise they do not relate.

How can I approach this?


Thanks
ITFlash

 

[Mike Gagnon]

Now, if I issue an append blank at init and the user does not save the record, I have wasted an ID.
But I need the Company ID to put into the Contact records added by the user otherwise they do not relate.

If your tables are "buffered", put the code that updates the ID table in the save button.
Mike Gagnon

 

[DSummZZZ]

In order to preserve the uniqueness of the index, you can't, as there could be more than one blank record if a unique id is not assigned for each new record. You could consider recycling the unused record if it is retained, or put the code in the save button instead.
Dave S.

 

[itflash]

Thanks for the replies.

I just wanted an easier way than having to code it myself.

My contacts appear as a grid and it would have been nice to code it this way.

In the past, I have had to copy from static fields into a blank record when the user clicks save.

This must be a common problem others have faced?

 

[itflash]

really dont wanna do static way - anyone any ideas?

 

[rgbean]

Well you can wait for VFP 8.0 - it's rumoured to include a new autoincrement field type, of course the table will need to be part of a database (not free). However, it probably won't be available until sometime next year.

In reality, primary keys shouldn't have ANY meaning and they don't need to be sequential or even increasing, just unique.

Rick

 

[itflash]

good point - I may use random instead.
know any good routines?

 

[Mike Gagnon]

ItFlash
If your volume is not too much you could use SYS(2015). Mike Gagnon

 

[itflash]

company table will be up to 50,000 records

so doubt that will be suitable - thanks anyway.

 

[Mike Gagnon]

Itflash

company table will be up to 50,000 records

I meant more in the sense of the traffic. In other words "What are that chances that two users could actually hit the button exactly at the same time and that both their computers are configured exactly the same". But if that could happen you could use an asciated function, that would handle that:

FUNCTION ukey
   PARAMETER lenkey
   IF PARAMETERS()=0
        lenkey=8
   ENDIF
   IF lenkey=1
        RETURN RIGHT(SYS(3),1)
   ENDIF
   len1=FLOOR(lenkey/2)
   len2=lenkey-len1
   key1= RIGHT(SYS(3),len1)
   key2= RIGHT(SYS(2015),len2)

   RETURN key1+key2
ENDFUNC

Mike Gagnon

 

[itflash]

mmmmm, still got a problem.

my routine would still need to generate the unique number on form init. So, if two users get the same random number (I dont like leaving things to probabilities), I would get an error.

Therefore, on save, I would have to alter the records programatically. But how do I know which ones to alter?



do I have to abandon my databound controls?

 

[Mike Gagnon]

itflash

So, if two users get the same random number (I dont like leaving things to probabilities)

I would be very surprised. And if you want a guaranteed unique ID number then use the suggestion in faq184-2393. But if I may suggest, is to maybe rethink your design a bit.
[ol][li]"Normally" you require a unique ID when you create a new record. [/li]
[li]You "normally" actually use that unique ID, once you are saving the record.[/li]
[li] Don't worry about wasting numbers (either with GUID or SYS2015), they are randomly generated, so you would never re-encounter the same anyways.[/li][/ol]
Mike Gagnon

 

[itflash]

Thanks for the replies Mike.

Get your point about sys(2015) - but arent indexes faster when integers? Thats why I always stayed away from this one.

If I wanted an Integer n(10) - would this be fine?

gnLower = 1
gnUpper = 9999999999

mynewid = INT((gnUpper - gnLower + 1) * RAND(-1) + gnLower)

Would this generate equally as well as sys(2015)?


Thanks

 

[itflash]

sorry, also, if there is a duplication, how can I change the many records that could have been added in the contacts table?

 

[DSummZZZ]

itflash,

I think if you combine thread184-230750 with SYS92015), you won't have a problem.
If by chance you need to change the contacts, you will need the original number:

SELECT company
STORE company.id TO n.nOrgNum
REPLACE company.id WITH newid() &&... or whatever

DO WHILE SEEK(n.nOrgNum, 'contacts')
REPLACE contacts.id WITH company.nOrgNum
ENDDO

Also keep in mind, with a table of 50k records, which really isn't that many, you aren't going to feel much of a performance hit using an index tag other than integer. Especially after the initial creation of the index.
Dave S.

 

[itflash]

Thanks for the replies

OK - I am going to use the RAND routine and just double check on save if there is a key violation. If there is, I am going to create another RAND number.

I have the company table working fine - because it is one record. This is below:


DO while !TABLEUPDATE(2, .F., "company", aErrLog)

gnLower = 1
gnUpper = 9999999999
mynewid =
INT((gnUpper - gnLower + 1) * RAND(-1) + gnLower)

SELECT company
REPLACE companyid WITH mynewid

ENDDO



However, the contacts table I cannot get working.
If I do a record count, it shows the number of the whole table. I just want to change what is shown in the grid on my form. How can I do this?


Thanks

 

[Mike Gagnon]

ItFlash

Get your point about sys(2015) - but arent indexes faster when integers?

As DSummZZZ states, you would need a few million records to notice a difference. My personal policy has always been "if you are not going to calculate it, don't use a numeric value, use a character"

Your suggestion for a random interger is also a good one.

sorry, also, if there is a duplication, how can I change the many records that could have been added in the contacts table?

Isn't the contact the header table? With a primary index on the newid number? If you have a duplicate, it won't save, it will through a message. But with your newid function I'd be surprised.
Mike Gagnon

 

[itflash]

the company is the head table and the contact the many table.

contact table has a companyid to relate it to the company table. For example, a company has many contacts(people).
the contact table will also have a unique id - contactid.

I just cannot seem to identify the records in the grid.

Thanks for the replies

 

[Mike Gagnon]

ItFlash

I just cannot seem to identify the records in the grid.

What do you mean? In the Dataenvironment of the form do you have a "visible " link between the master table id field to the child table index?
Mike Gagnon

 

[itflash]

the form has a denvironment with a link from company to contact.

there are fields for the one company record and a grid showing the contacts and all are bound.

from my code above, i can trap the error if a key violation and alter the company table. but how do i update the contact table?