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

 

[itflash]

...if i do a reccount() it totals everything in the contact table...even if it is a private data session.

 

[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?

I'm not sure what you need.
[ol][li]"Normally" if you use a unique id number, you use the function to create a record in the header table.[/li]
[li]If you need to create a record in the child table you would use the following syntax:
SELECT myChildTable
INSERT INTO myChildTable (uniqID) VALUES (myHeaderTable.uniqID)[/li]
[li]If you use a uniqID you should use the database's Referential Integrity function for the delete part where if you delete the header record, VFP will take care of deleting the children automatically for you.[/i][ol]


Mike Gagnon

 

[itflash]

got all that

its just when the user clicks save and there is a key violation, how can i change just the records that appear in my grid?

filter does not work, reccount ignores filter, etc...

 

[Mike Gagnon]

itflash

its just when the user clicks save and there is a key violation, how can i change just the records that appear in my grid?

"how can i change just the records that appear in my grid?"

To what? If there is a key violation, that would mean on the header table right? So before you create your header table record, store the RECNO() your are on and if there is a problem and you get a key violation go back to your RECNO() and thisform.refresh().

Mike Gagnon

 

[itflash]

thanks for the reply.

in my scenario:

on save, if there is a key violation, I generate another key using my rand function.

I can then easily change the header (company) record becuase I am on that particular record. So I just replace.

However, I do not know how to then change the contact records to have the new companyid.

 

[itflash]

...the contact table also has to have the companyid to link it.

 

[itflash]

basically, what dave s was trying to do with his loop - but that doesnt work

 

[Mike Gagnon]

Itflash

Ok, is the issue solved? Mike Gagnon

 

[itflash]

no, dave s soultion does not work

 

[Mike Gagnon]

ItFlash

no, dave s soultion does not work

It's not clear what you need.
1. We have established that your function for creating a unique ID will most likely never be a duplicate.
2. If you use the referential integrity of the database for both "delete" and "update", if there is ever a change in the header record's unique number, the children WILL be updated.

So could you please state your situation.
Mike Gagnon

 

[itflash]

OK, not to worry

thanks for the help though

 

[DSummZZZ]

If your 'contacts' table is linked to the company table, as soon as you change the id of the company table, you will no longer be referring to the proper records in the contacts table. So let me rearrange the logic of my prior post:

SELECT 0
USE contacts ORDER id
SELECT 0
USE company ORDER id

STORE company.id TO m.xOrgNum
STORE newid()    TO m.xNewId    &&... or whatever

SELECT contacts
DO WHILE SEEK(n.nOrgNum, 'contacts')
   REPLACE contacts.id WITH m.xNewId
ENDDO

*... Be sure to do next statement, because if EOF()in 
*... contacts, replace won't happen on non-selected table.
SELECT company  
REPLACE company.id WITH m.xNewId

Dave S.

 

[itflash]

thanks for the reply

but if it is a key violation - i tested this - then the contact records already exist.

Therefore, when I run your routine it will also change the records already in the table that caused the key violation.


What I need to do is just change the records that appear in the grid only - if this is possible.

I am stumped.

 

[DSummZZZ]

You shouldn't have even created the key values in the contact table, or at least assigned them yet either. The key should be created first, verified, then used in the customer table. After which the contacts table should be updated. It would seem that the logic needs to be a little more proactive than reactive for your scenario. In other words, you shouldn't have to go back and change data, it should be as clean aspossible before committing changes.
Dave S.

 

[itflash]

ok - I will run through it - maybe I am not explaining myself correct. This is add new company.

Part 1

1. my form init.
2. I append blank for company.
3. I get a random number
(because otherwise I waste numbers as discussed earlier)
4. I make that my companyid and replace it.


part 2

1. The form loads and shows the blank new company.
2. There is grid to allow for contacts(people) to be added.


Part 3

1. user enters company data
2. user creates contacts.


part 4

1. User saves company
2. If a key violation occurs (possible) then I need to
generate another random number to replace my number.
3. I can easily replace my company table key.
4. I cannot replace the contact key because records already
exist in the table with that ID and I cannot distinguish
them.


Xtra

Both tables are linked via companyid
i.e Company 12M Contact


Maybe I am doing something very wrong?

 

[Mike Gagnon]

ItFlash

1. Why do you create a blank record in the init?
2. Once you created your unique ID, do a search in your company table BEFORE you commit to the table.
3. I'll repeat this again: If you use the referential intergrety of the database and you change the company's unique ID it will automatically change the contacta table's unique ID.
Mike Gagnon

 

[DSummZZZ]

I understand your scenario but here are my thoughts summed up.
Forget about 'wasting' a number. There are a bazillion random number you can generate using the examples we have shown.
Don't worry about speed issues using a non-integer. Unless you are getting into millions of records, it won't be an issue. The table size and network throughput will be more of an issue.
Make sure you have a truly unique number before adding/saving the customer record. Then you will have one for contacts. If need be, take the 'newid' function out of the database stored procedures and create a stand-alone function for it:

FUNCTION newid
*... generate new id here
DO WHILE SEEK(m.NewId)
  *... get another id here
ENDDO
RETURN m.NewId

Add new customer record here....

My point is, yuo shouldn't have to backtrack and try to find a value that may be unknown in the contacts table. The contacts (child records) should not be created unless they are already properly referenced by the customer (parent) table.
Dave S.

 

[itflash]

Dave - I know u know what I mean. OK I will do it that way. A much simpler way, I suppose, is to seperate adding a company and contact at the same time. Its just that in MS Access, this would be so easy, yet I struggle in FoxPro - but I still like Foxpro.



Mike, thanks for the responses. The ref integrity does not work in this scenario. It is because a company record may be created whilst user creating a new record. So it never becomes orphaned.

Anyway, I will take another route as you both suggest.

I just will stay clear of creating new records for one to many relationships on the same form using bound controls.


Many thanks for your paitence