a simpler way...?

[Database Administrator]

Howdy,

Using P9 with XP...

I need to have a field in an MRO number entries in sequence.
That is, the first entry gets a "1" and the second gets a
"2" etc.

I don't want to use a autoincrement type because the field
is part of a key.

I could run a query, get a cMax, and increment by one, but
is there a simpler way to do the deed?

Many thanks,
--
Kenneth

If you email... Please remove the "SPAMLESS."

[Database Administrator]

Does the incremental part of the key revert back to 1 when the other
part changes? I.e. when the other part of the key changes to 4322, does
the sequence revert back to 43221, 43222, etc?

If that's the case, I would suggest a table with a record for every
generated key (4321, 4322 etc) being the primary key with a field
containing the last incremental number used for that main number.

Kenneth wrote:
>
> The value in the field in question is part of the key. The
> other part of the key is generated using code that is sound.
> (It takes a value from a table reserved for the purpose,
> adds one to that value, uses it as the first part of the
> key, and then puts the value back into the table.)
>
> I am trying to learn of the best way to assign the value of
> the field to a "1" or "2" etc. (so that the key generated
> would look something like 43211, 43212, 43213, 43214 etc.
> where the last digit is the one from the field I have
> described.
>
> Might you have ideas about that specific need?
>
> Many thanks,


--
Tom Krieg

---------------
http://www.krieg.com.au
Please use the contact page to contact me via email

[Database Administrator]

Hi Kenneth,

You mentioned that you were editing with a MRO,
and I understand that,
but I am not sure I understand the whole picture yet.

Is this a header / detail kind of relationship,
similar to an invoice with many lines,
or a production item with inventory items needed for production ?

Do you go thru a process where you have to pick the
header or master record,
and then you add the detail lines ?

If you do figure out a way that adds the 1,2,3, etc,
and then you renumber them later,
will the original key be located elsewhere and need to be changed ?

Do you do the data entry right in the original table ?
or do you keep empty tables ( or create temporary tables) for data entry,
and then "post" it to main table when user hits save ?

In some situations,
I have created empty temporary tables on the fly,
using the create like syntax,
but I do not key this table.
I then allow users to data entry into the temporary table,
if they hit cancel , I just empty temp table, or delete it,
and then if they hit save, then the fun begins.

If adding new item, then all rows need to be added elsewhere.
If editing an item, then I delete all items matching the header ,
and then add all the detail records.

For these detail records,
when the user clicks save,
I end the edit mode,
and then simply run a tcursor scan/endscan on that temp entry table,
to add sequencing number,
then close tcursor,
and then do table add or insert query as necessary
to move these records into approriate table.

Keying the temp data entry tables for me introduced headaches,
and since I was editing in an empty table,
I really didnt need it.
I just had proper code in my save routine.

I think this is better way if you are doing client / server also.
Sort of like creating little batches, that must deal with the server.

Not sure if that makes sense or is applicable in your case.

Still doing any linux,web, or mysql stuff ?
IIRC from other posts, you were doing some fun things with that.


Good luck
Robert Wiltshire

[Database Administrator]

On Sat, 08 Nov 2008 11:35:39 +1100, Tom Krieg <"Please use
the website contact form"> wrote:

>Does the incremental part of the key revert back to 1 when the other
>part changes? I.e. when the other part of the key changes to 4322, does
>the sequence revert back to 43221, 43222, etc?
>
>If that's the case, I would suggest a table with a record for every
>generated key (4321, 4322 etc) being the primary key with a field
>containing the last incremental number used for that main number.

Hi again,

Yes, the incremental part was to revert back to "1."

As it happens, I have another approach that has solved my
problem, but I am still curious about your suggestion above.

But, with regret, I do not understand your suggestion as
written.

Could you please describe the idea in that closing paragraph
another way?

Many thanks,
--
Kenneth

If you email... Please remove the "SPAMLESS."

[Database Administrator]

On Fri, 7 Nov 2008 20:51:50 -0500, "Robert Wiltshire"
wrote:

>Still doing any linux,web, or mysql stuff ?
>IIRC from other posts, you were doing some fun things with that.

Hi Robert,

I thank you for your comments, but suspect you are confusing
me with someone else.

All the best,
--
Kenneth

If you email... Please remove the "SPAMLESS."

[Database Administrator]

Let's split the number into 2 parts, the "fixed" part (i.e. 4321, 4322
etc) and the "increment" part (1,2,3,4).

My suggestion is to create a table with 2 fields, "Fixed" and "Last
Increment", where "fixed" is the primary key.

When you create a new key, create a new record in this table and set the
increment to 1. When you create another record for the same "fixed"
part, just qlocate the appropriate record using a tcursor and add 1 to
the increment.

Kenneth wrote:
> On Sat, 08 Nov 2008 11:35:39 +1100, Tom Krieg <"Please use
> the website contact form"> wrote:
>
>> Does the incremental part of the key revert back to 1 when the other
>> part changes? I.e. when the other part of the key changes to 4322, does
>> the sequence revert back to 43221, 43222, etc?
>>
>> If that's the case, I would suggest a table with a record for every
>> generated key (4321, 4322 etc) being the primary key with a field
>> containing the last incremental number used for that main number.
>
> Hi again,
>
> Yes, the incremental part was to revert back to "1."
>
> As it happens, I have another approach that has solved my
> problem, but I am still curious about your suggestion above.
>
> But, with regret, I do not understand your suggestion as
> written.
>
> Could you please describe the idea in that closing paragraph
> another way?
>
> Many thanks,


--
Tom Krieg

---------------
http://www.krieg.com.au
Please use the contact page to contact me via email

[Database Administrator]

On Sat, 08 Nov 2008 13:30:20 +1100, Tom Krieg <"Please use
the website contact form"> wrote:

>Let's split the number into 2 parts, the "fixed" part (i.e. 4321, 4322
>etc) and the "increment" part (1,2,3,4).
>
>My suggestion is to create a table with 2 fields, "Fixed" and "Last
>Increment", where "fixed" is the primary key.
>
>When you create a new key, create a new record in this table and set the
>increment to 1. When you create another record for the same "fixed"
>part, just qlocate the appropriate record using a tcursor and add 1 to
>the increment.
>

Hi again,

Many thanks...

All the best,
--
Kenneth

If you email... Please remove the "SPAMLESS."