Auto Increment in two different tables?

[Tim Kiebert]

Re: Auto Increment in two different tables?

It sounds like your client wants his cake and he wants to eat as well. And if he decides he doesn't like it he wants to keep it for someone else to eat.;)

[Cheryl Lemire]

Re: Auto Increment in two different tables?

You are very correct Tim. I will also add that most people in general are like that. I gave him the options that I had available to me and he chose the ones that work best for him. I would have personally chosen to assign the number in the save changes event, he would still see the number, but not until after the record was saved and the form refreshed and he wanted to see the number up front. To each his own :)

[Stan Mathews]

Re: Auto Increment in two different tables?

Thanks Doug. That option was also given to the client, but he does not want any of the numbers 'cancelled' or 'voided' or anything like that. He wants every number to be used and actually have order details in it.

There's a nice post from Cal Locklin on this topic somewhere. I believe it goes along the lines


"If you want it that way, I can do it that way. Are you sure you want to pay what it will cost for me to make if work that way?"

[DaveM]

Re: Auto Increment in two different tables?

Cheryl,

Thank goodness he is your client. I woulda fired him.

Normally, I don't give options that don't also make me happy. In other words, leave out the bad stuff. Fortunately I do the car business and they have no idea what I am doing and are totally happy if it just works.

We have account numbers and stock numbers as well as work orders, parts tickets, etc. Fortunately the people are used to having to back out an order or a sale.

Vehicles are either active, pending or sold. Same with a lot of other stuff.

I don't see why a acct number can't be assigned and then removed completely. a search of missing numbers could find it for re-use?

Dave

Dave

[Cheryl Lemire]

Re: Auto Increment in two different tables?

Stan,

I love that comment. Any idea where the thread is? I would love to read it.

Dave,

You said something that just got me to thinking. I could possibly change things in a way that allows the user to cancel an order without actually cancelling it. I could save the number to another temp table. Then I could change the add new order to first look in the new temp table, if any records, retrieve the number and populate my new order number (then delete that record), if not, then call my increment function.

Looks like I may be able to allow my client to have his cake and eat it too along with everybody else's cake that they didn't eat.

As far as firing the client, that is not an option. He is a great client and as long as he is happy with a work around that seems clumsy to me, then I am happy. This particular issue has nothing to do with data integrity, etc, so there was no need to push him to doing things my way. I choose my battles, and when what the client wants is something that messes up the data integrity, then they are not given the other options :)

[jkletrovets]

Re: Auto Increment in two different tables?

Cheryl,

You said something that just got me to thinking. I could possibly change things in a way that allows the user to cancel an order without actually cancelling it. I could save the number to another temp table. Then I could change the add new order to first look in the new temp table, if any records, retrieve the number and populate my new order number (then delete that record), if not, then call my increment function.

That's what I was thinking as I was reading Dave's post as well. That could be an avenue to explore.

Along the lines of what Stan mentioned about Cal's post. My father told me something a long time ago that I never forgot....

"There is price, quality and service...you can have any two."

If you think about it - that saying is so very true.

Unfortunately, some customers want all three! :)

[MikeC]

Re: Auto Increment in two different tables?

Hi Cheryl,

I just wanted to give you yet another option for your playtime.

They then write down the number and edit that record the next time they need to process an order.

If this is how your client is going to do things then you could automate the process--I can see where writing the number down could easily mess things up in a very short time when someone forgets or writes down the incorrect number.

Why not create a flag (logical field) and variable that once this "incomplete" record is produced, Flag=.T. if incomplete and have the variable set to the number? Then once anyone goes back in to create a record the flag will bring up the incomplete record to be edited.

This way your customer does not have to do anything in regard to it--you could even have a message that comes up that explains that the record brought up is incomplete---truthfully I would "blank" the record's fields when cancelled too.

[Cheryl Lemire]

Re: Auto Increment in two different tables?

Mike,

playtime

I like that. As if any of us have time for such a thing :)

Your idea is a great option, however, there are two things that come to mind right off the bat.

1) Your option keeps the situation as is as far as the user NOT being able to CANCEL. I never liked removing the cancel option because it just seems 'unnatural'. We all want to be able to change our minds.

2) I would still have to modify my add new order ... except with your option I would be querying the current table (which will have tons of records in it) vs using a temp table to store the numbers which will have very few records in it at any given time.

I will, of course, have to think about this a bit more before making a final decision. So at this point, I am still taking any other options that all of the great developers here can offer :)

[Cheryl Lemire]

Re: Auto Increment in two different tables?

Jeff,

Your father has a great mind!!! I am one of those 'customers' that wants all three. For me, in most cases, I have been blessed with 'great' clients and if price ever becomes an issue ... I tend to bend towards the other two. I happen to be a perfectionist and if I can provide a better service to my client by making the quality of the process better, then I would probably eat the price. I know there are others that also do this in their business. Although this could cause some of us to go hungry one day because we worked for 'no pay', in the end (for me at least) a satisfied customer brings in new business and a quality application makes me look better.

[MikeC]

Re: Auto Increment in two different tables?

Cheryl,

Your Client can Cancel all he/she wants! :) The cancel would simply blank the fields and retain the record number so that it could be fietched when a new record was to be created. This would work to keep your increment sequence intact ...and what is nice it would work in a multi-user application as whoever wanted to create the new record would bring up the cancelled record first.

edit: The flag could also steer the fetch of the increment to a temp table/field/whatever so that a query would not have to be done on a ton of records.


But, like you, am curious as to how others would solve this client problem.

[martinwcole]

Re: Auto Increment in two different tables?

Gabe, I don't think you can use a global variable, because all variables are local

I use a counter table for storing the next number to use

in field rules,at the onenter event, I put 'TEMP" in the 'autoincrement' field
they see something that way
if they cancel the entry, no big deal

then in the cansaverecord event, I open the counter exclusive, increment it, and put its value in the field

I've been using basically the same script since 1999 in Alpha successfully in LANS and single user apps

[Tim Kiebert]

Re: Auto Increment in two different tables?

Cheryl,

I haven't seen Tom's script but In the case of a script like Jeff is using one could add one more field for each incremented value. ie two fields for each table. If the user cancels a record then write the value into the second field. (This means there is only one table to open and examine.) Have your normal auto increment function first check the canceled value field and if not blank then use that value and empty the field. If it is blank then continue as you do now. Somone else might think this through as well but as far as I can tell there should never be more than one canceled value because it always gets used first. Hmmm, may be there could be if two people have started a record and then they both cancel. Oh well, I thought I was on to something.

In all fairness to your client I like cake just as much as the next guy. ;)

[martinwcole]

Re: Auto Increment in two different tables?

Cheryl, rereading your post, it should be very simple
define what is a blank record
--like no client, maybe
then, when they push the 'new record' button, use a simple script to take them to the first record that meets that definition, if the record is locked, then to the next, etc.

some owners are paranoid about 'unaccounted' numbers for good reason - states often have a formula for penalties for lost numbers for invoicing when they audit - like your average invoice amount times X

if you are dealing with audits from the FAA, NTSB, etc., it often gets even more interesting.

[G Gabriel]

Re: Auto Increment in two different tables?

Gabe, I don't think you can use a global variable, because all variables are local

What do you mean all variables are local?

[G Gabriel]

Re: Auto Increment in two different tables?

Actually, come to think of it, you don't even need any variables. That was a quick response to Cheryl asking about multi-users environment. Somehow afterwards, in this thread, ensued a discussion that I didn't read much of it at all but if I am not mistaken, it had to do with skipping numbers.

Here is what you do where you do not have to use any variables and don't have to worry about skipping numbers:
Put the same expression I mentioned earlier in the OnSave event of the record.