tablemax vs autoincrement

[Sandy Garrett]

People.Pplno and People.Init are character fields. We need Pplno to default to tablemax + 1, and (Pplno + Init) to be unique.

Setting Pplno to autoincrement limits the adjustments we can make when 12001abc, 12001, and 12001xyz need reconciling.

str(val(tablemax("People",".t.","Pplno"))+1) misbehaves - because Pplno <>Pplno+1?

How can we use tablemax+1 for the default?

[Allen Klimeck]

Re: tablemax vs autoincrement

Lookup INCREMENT_VALUE()

[Ray in Capetown]

Re: tablemax vs autoincrement

Setting Pplno to autoincrement limits the adjustments we can make when 12001abc, 12001, and 12001xyz need reconciling.

Sandy
Other than not understanding this statement, my opinion is that autoincrement is useful for internal audit purposes only. It is too restrictive with handling these "12001abc, 12001, and 12001xyz" kind of things

For any viewable/reference purposes keep a single table, with one record.
Add as many fields as you need to keep sequences for other purposes in you app.

Referring to this table for the next increment value available and writing that back after applied (yes USING INCREMENT_VALUE() btw..,) is foolproof, resettable, and can function as an adjudicator for multi-user conflicts.

[Sandy Garrett]

Re: tablemax vs autoincrement

The problem I am trying to ameliorate is lack of a network or internet connection along with need for a unique identifier. For Accessions (items in our museum collection) I tell Agnes when to stay away from her keyboard. We have numbers from 10001 to 29999 and the other office owns 30001 on.

The plan with the People table is to keep the real entries to the 5-digit ID, Pplno. When users add a person she is identified by the local ID plus the user's initials. Periodically I reconcile any differences, delete the initials, and distribute the de-initialed records.

[Ray in Capetown]

Re: tablemax vs autoincrement

All the more reason to NOT use field rule auto-increment
A single table, with one record keeps the last pplno.
Refer to this table for the last value used, INCREMENT_VALUE() and write that back. Then you can control both the 5 digit number at source and alter the main table if necessary when reconciling.
Make sense?

[Al Buchholz]

Re: tablemax vs autoincrement

The problem I am trying to ameliorate is........

You definitely get the word of the day award... maybe the year...

[Sandy Garrett]

Re: tablemax vs autoincrement

Periodically we Copy recently added/changed records and Append them to the master system Unique, replace existing. Usually first_name-middle-last_name works for the Append Keys, but not always.

If the last # we all have is 12345 and two users add someone, there would be two 12346es and the survivor would depend on who was appended first. I want to tell one user to change her 12346 to 12347.

[Ray in Capetown]

Re: tablemax vs autoincrement

I know what Emiliorate is.
That happens when my friend Emilio answers any question.
Probably not the same?

[Mike Wilson]

Re: tablemax vs autoincrement

Frankly, this thread is irreconcilable.
Not really enough information to figure out what is the situation.

People.Pplno and People.Init are character fields.

Anyone try to make a field with that name..... ain't gunna happen.

str(val(tablemax("People",".t.","Pplno"))+1) misbehaves - because Pplno <>Pplno+1?

This statement make no sense to me. Is there a situation where Pplno = Pplno+1? Or are you just pointing out you cannot do math on a character field?

The plan with the People table is to keep the real entries to the 5-digit ID, Pplno.

We now are given a clue that the "people" in the first statement is a table pointer and the field name is actually "pplno"?

When users add a person she is identified by the local ID plus the user's initials.

Is this to imply that only women are entered into the people table?
At this point another variable..... local ID.... has been added to the mix. Is that local ID = the pplno number?

..... when 12001abc, 12001, and 12001xyz need reconciling.

If the unique identifier is a concatenated value that includes user initials, how would you ever get 12001 absent letters?

Color me confused or just too slow to catch on to what's happening here.

[Sandy Garrett]

Re: tablemax vs autoincrement

I always read my posts three times before sending to be sure they cannot be misunderstood, and people rarely admit to understanding even when a solution is found. It's a talent :-(

People.dbf is the table, pplno and init are fields therein. Init is C, Pplno has been both during this experiment.

str(val(tablemax("People",".t.","Pplno"))+1 was a failed attempt at default value for the pplno field for a new person.

As Girl Scouts we are often politically incorrect. You will usually find the Men's room (required by law) full of the janitor's equipment.

Init, aka local ID, is any sort of item to identify in which batch this person was entered. At the moment it is three initials the user chooses when she pushes the button that enables entering new records.

If the unique identifier is a concatenated value that includes user initials, how would you ever get 12001 absent letters?

The plan is to remove the initials when the conflicts are sorted. I can do that now by

1. saving (Copy) changed records to a dbf file (we do that anyhow)
2. adding the change table to the master workspace.
3. manually adjusting the Pplnos
4. deleting the Inits
5. saving the file
6. Appending the file from #4 to the master People table.

But if we just do 1 and 6 and then show the duplicates, I'll see where adjustments are needed easily, 4 would be last.

[Sandy Garrett]

Re: tablemax vs autoincrement

Here's the end result. The People table has a Pplno N field and an Init C field. The default value for Pplno is tablemax("People",".t.","Pplno")+1, Value of expression must be unique, PPLNO+(INIT).

The People form starts write protected. It has a button:
'Create an XDialog dialog box to prompt for initials.
DIM SHARED vinit as C
DIM SHARED varC_result as C
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
varC_result = ui_dlg_box("Mark new people",<<%dlg%
{region}
Initials for new people:| [.4vinit];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%)
if vinit <> ""
topparent.Restrict_enter = .f.
end if

The form also has an on enter event
'Set 'Value' property of 'Init' in Form 'People' .
parentform:Init.value = vinit

And there is a button to help reconciling:
tbl = table.current()
tbl.unmark_range()
tbl.mark_dups2("PPLNO",".T.")
topparent.QueryRun("marked()")
end