Auto Increment in two different tables?

[martinwcole]

Re: Auto Increment in two different tables?

Gabe, I may have misunderstood, but I interpreted your comment to mean that you could use a global variable that 'all users' would thus have available - and I was pointing out that global variables only exist on each workstation, 'locally,' and are not available to other users

PS: in a multiuser environment, using the 'onsave' event is to be used very judiciously - i.e., very infrequently - as it will frequently cause undesired side effects, especially if you are using it to write back to itself.

[Ted Giles]

Re: Auto Increment in two different tables?

What about using Recno() + an off-set?
(Or am I howling at the moon again?)

[Cheryl Lemire]

Re: Auto Increment in two different tables?

Martin,

Your idea is similar to one earlier about using a logical field to flag the record. My only issue with this thought is that I have to run the query on the existing orders which is quite large. Whereas, writing the record to a temp table and running the query there would be a lot quicker since the record would be deleted once used.

As far as the OnSave side effects you mentioned, what kind of side effects? I have not used that event, mostly I use the CanSave, but you never know when I may have the need to use the OnSave :)

Ted,

Using recno() with an offset would give a unique number. However, what I do not know, perhaps somebody else does:

1) When is the recno() actually assigned to the record?
- If it is when the record is entered, is this saved prior to additional field data population so that in a multi-user environment, or is this the same as a 'probable' record number that could change in the OnSave event?

2) What happens to the recno() when the user decides to cancel?
- I could be wrong, but I think once a recno() is gone, it cannot be re-used.

[Cheryl Lemire]

Re: Auto Increment in two different tables?

I finally had some time for a little more research, and recno() would not work. I found this in the help files:

Note : Using the record number to create a unique record ID is not a good idea. The value returned by RECNO() is only a physical position in a table. It can change if you delete a record and then pack the table.

[G Gabriel]

Re: Auto Increment in two different tables?

recno() are recyclable, autoinc are not.

[Cheryl Lemire]

Re: Auto Increment in two different tables?

Hello again. Here is where I am at with my client that wants his cake and wants to eat it too :) He has been using a temp order process that we pulled from his DOS Alpha days and I have been in the process of programming the new and improved order process for him.

The new process uses a temp table to retrieve the data for the new order and then writes that data to the various tables for the order set. Since I am already using a temp table for the initial order data entry, I thought I could add a logical field to that table 'num_notused'. When the user decides to cancel the order, I can actually save the record populating only the logical field to true and blanking out all other data.

Then when the user wants to add a new load, I can first run a query on the form to see if there are any records in the table where that logical field is marked as true. Since I am deleting the record in the temp table when the user saves their data, this table should only have records in it if they cancel so there would never be more than a few records to query. If there are no matching records, then create a new record, populate my load_num, save it, put it in change mode and populate additional form field objects.

If there are matching records in the temp table, then fetch the first record, put it in change mode, and populate additional form field objects. I hope this makes sense :)

The following is what I have come up with to accomplish the above. Am I on the right track or do I need to do something else to determine if I am entering a new record or editing the first record in my query?

parentform.basequeryrun("num_notused = .t.", "", "", "")
qry_count = parentform.records_get()
if qry_count = 0
parentform.new_record()        'start new record
parentform:load_num.value = Incr_load_number( "" )  'populate load_num
parentform.commit()    'this will trigger UI LEVEL field rules
parentform.change_mode()
else
parentform.fetch_first()
parentform.change_mode()
end if

'continue with function retrieving data and populating addl form field objects

[jkletrovets]

Re: Auto Increment in two different tables?

Cheryl

Looks good. It makes sense to me. Sounds like you might be able to give your customer everything he wants.

Regards,

Jeff

[jkletrovets]

Re: Auto Increment in two different tables?

Martin

To follow up on Cheryl's question:

As far as the OnSave side effects you mentioned, what kind of side effects? I have not used that event, mostly I use the CanSave, but you never know when I may have the need to use the OnSave

I would be interested in hearing your experiences on this as well.

Regards,

Jeff

[Ted Giles]

Re: Auto Increment in two different tables?

Recno()

It seems to be allocated at the start of New Record Entry.
If the user cancels, the recno() is NOT written back, so is usable again.

However

If you delete a record which has been saved previously, the recno() becomes unusable intil you pack the table. Then it is available again.

When I've had a similar problem, actually getting the user to check and finally Commit the record save, I generally add a message box to the OnSave event which reminds them that the action is "irreversible".

New Thought.
We have an issue with the allocation of unique numbers to sensitive personnal data. This MUST be the only identifier throughout a very complex social care system. My current thinking is to create an ID generator which kicks out unique ID's which are then used on the social care record. At first everyone was hung up on using sequential numbering, but with 900,000 records and most being searched on Surname, First name and DoB, this became a smokescreen. It doesn't matter what the number is as long as it is unique.

Ted. Bright sunny bit a bit cool today.

[Cheryl Lemire]

Re: Auto Increment in two different tables?

Hi Ted,

Thanks for the info on recno(). As far as unique record identifiers, in all of my tables I use: REMSPECIAL(API_UUIDCREATE()).

Hope that helps.

[Ted Giles]

Re: Auto Increment in two different tables?

That looks a bit cool. How does it work?
Ted

[Cheryl Lemire]

Re: Auto Increment in two different tables?

Hi Ted,

It is a windows api that creates a kind of hash. It is 32 characters and I guess the number of possible combinations makes it nearly impossible to duplicate. I put that code in field rules, data entry, default simple expression, create at time of enter, and .t. for skip.

If you have any scripts/functions where your field rules do not take place, you would need to populate that id field there as well. I use it behind the scenes only for a linking field between tables and as a unique record identifier if ever needed. I normally have the field on my forms hidden unless I am using it as a lookup. If I am using it as the link in a lookup, I do not display it, I only populate the data and display say, the company name or whatever makes sense to the users for that particular lookup.

It is nice to know that you can have hundreds of thousands of records in hundreds of tables and not have any duplicates. It is great over a network as well. Even if you had 6 users on a network creating records in various tables, each UUID would be unique.

This link explains it a lot better than I ever could:

http://en.wikipedia.org/wiki/UUID

I did read in the microsoft knowledge db that there was a slight problem with the UUID only being unique to the local computer if a certain external device was attached to that pc. They do have a fix for that though if any of your networked pc's are using this device. Here is the info on the specific device:

http://support.microsoft.com/kb/932380

[Ted Giles]

Re: Auto Increment in two different tables?

Thanks for this Cheryl, I'll give it a go on the council proof of concept project.

I'm also using the attached link software to show a new approach to Application Help
Might be of interest if you haven't come accross it before.

http://www.jingproject.com/

Regards, Ted

[ken doucet]

Re: Auto Increment in two different tables?

Thanks for this Cheryl, I'll give it a go on the council proof of concept project.

I'm also using the attached link software to show a new approach to Application Help
Might be of interest if you haven't come accross it before.

http://www.jingproject.com/

Regards, Ted

Just came across this thread and went to url and was pleased to see it was from techsmith and cross platform (windows and mac) and free for now. Techsmith is a great company so this is worth exploring.

Ken