Count [ID] as 'next' =alternative to Auto-serial

[Leb i Sol]

Hello People!

I am looking for an altertive approach to "next serial" wihtout having to define the field as "auto-increment". Here is the scenario:

RecordID = text

c_TotalRecords = Count (RecordID)

c_NextRecord_ID = c_TotalRecords + 1

this works fine until the record is deleted....I was wondering if there is a way to grab the

'field value of very last record' + 1

to ensure increments even when a record "in the middle" is delted?

I attempted a short script as a part of new record creation:

- Show All Records

- Go to Record [last]

- Set Field [c_NextRecord_ID,RecorID] * set the value of the last

- New Record

- Set Field [RecordID,c_NextRecord_ID+1]

but no luck...

Again, due to setup I can not define 'RecordID' field to be auto serial.

Anyone has a trick?

Many thanx!

[Ender]

Why not auto-increment?

[DanBrill]

How about this:

Create a text field C. Set C to auto-enter as "1".

Create a self-join relationship, SJ, where c=c.

Then you can create a numeric ID that is an auto-entered calc, max(SJ::ID)+1

This will find the maximum used ID in the file and add one to it for the new ID.

Unfortunately, Max() and Min() are pretty to calculate...

Good luck,

Dan

[-Queue-]

It's easiest to use a self-relationship sorted by serial descending and selfrel::id + 1.

[DanBrill]

Scratch my idea -- it's causing a circular reference...

[-Queue-]

Oh yes, I forgot to include GetField in the picture. GetField( "selfrel::id" ) + 1

[DanBrill]

Here Leb i Sol, this works.

JT, could you flesh that out a bit? I'm not sure I follow...

Dan

test.zip

[-Queue-]

Using your constant self-relationship, sorted by serial descending, you can use an auto-enter calculation of GetField( "selfrel::id" ) + 1. If the file starts empty, then you can use Case( Status(CurrentRecordCount) = 1, 1, GetField( "_::id" ) + 1 ) to ensure the first record is '1'.

Since the relationship is sorted by serial in descending order selfrel::id is the largest number in the file. It's much faster than using Max or Last.

[DanBrill]

Ah, very cool. I don't even think you need the GetField. I think you can just use selfref::id. Eh?

This will help speed up some up my processes a great deal.

Thanks,

Dan

[-Queue-]

You don't need GetField if you're using a separate field. But if you're putting the calculation in the auto-enter options for the serial field itself, then you do need it or you'll receive the 'circular definition' error.

[DanBrill]

Hmmm. I can't get it to work from the serial field itself. I keep getting the 'circular def' error.

How are you defining your constant field? With a calc or an auto-enter? Could this make the difference?

[-Queue-]

The constant is a calculated field of 1. Are you using quotes around the GetField parameter?

[Leb i Sol]

Hello Dan & Queue!

Thanx for responding...for a second it looked like stupid question but I guess there is interest in this thread.

I recreated a simple sample of what you suggested and...not quite there.

eg.

records 2-6 have been deleted and yet the next number is not "highest value +1"

Mant Thanx (to both of you)!

qcat.zip

[DanBrill]

The quotes -- that was it. Thanks, this is going to really speed up some of my processes that relied on the Max() function.

(By the way, it works with either the constant as a calc or as auto entered text. I've come to like the auto-entered constant as finds work faster when the database gets large.)

Glad we finally know what your face realy looks like, JT.

Dan

[-Queue-]

Glad we finally know what your face realy looks like, JT.

ROTFL That's Ed Wynn! If you want to see me, go to my website from my profile and select Photos.

[-Queue-]

Hey Alen, your 'My_ID' field is text and therefore will not sort as a number. With a text sort, 9 comes after 10. Sort your records by My_ID descending and you'll see the problem.

[Leb i Sol]

....now it all makes sense to why a few first example worked...they were under single digit count and easly sorted "right"

Thanx, this is prety slick way of counting and , as Dan mention, Max is in the scripts world to what SUVs are in transportation on our "rugged freways" ...so having another way to things is always good.

Now, to expand this even more ....

can this be set up for a few fields that could "count/increment" independently of "overall record count" and each other?

Again, many thanx!

[-Queue-]

Can you elaborate on the independent incrementing idea?

[Leb i Sol]

Alright,

so far we wold have this field incrementing just great.(MY_ID)

On selection (eg. captured in some global field) of one of these IDs I would like to start incrementing for a "new level"

eg.

My_ID | my_secondID |

------------------------

1.........X

2.........X

3

4

5

8

9

12

so eg. I would have g_selectedID that would hold "my_ID selected"

the idea is to create a pattern and then SetField...

SetField [MY_ID, My_ID &"."& my_secondID]

"my_secondID - independent increment"

1.1

1.2

1.3

...

this will extend 3 levels deep where the 3d level would be nothing but:

SetField [MY_ID, my_secondID &"."& my_thirdID]

In other words: "increment my_secondfieldID independently for selection of each my_firstID"

I hope it makes sense

Thanx!

[Fitch]

For each level deep, you would need one field that works as previously described, and another to combine them.

[Leb i Sol]

Hi Fitch!

I am not sure I follow....repeatin the proces does not apply since 1st relationship is based on constant, so there is no need to "repeat" that part and all that would be left for "editing" is the actual secondID field

producing:

Case( Status(CurrentRecordCount) = 1, 1, GetField( "_::id2" ) + 1 )

whihc does not work...

simle addition of 1.1 + 1 wont work since desired result is 1.2 not 2.1

Perhaps I missread it all...could you elaborate?

Many thanx!

[-Queue-]

The second relationship would be based on the first id field to itself, sorted by second id descending. You wouldn't test the CurrentRecordCount, but whether there was at least one related record, possibly Case( IsEmpty(rel::constant), 1, GetField( "rel::id2" ) + .1 )

I haven't tested this yet.

[Leb i Sol]

Hi Queue!

ok ok I can see the logic now ....but, it failed.

[-Queue-]

Here's a sample. I'm not sure how you're assigning the sublevel ids, but this might help you figure out a pattern for it.

Text_ID.zip

[-Queue-]

Here's the modified file you sent me. It works fine, though I'm still confused how you're going to end up with more than just 1, 1.1, 1.1.1, etc. since you want 1.2, 1.1.2, etc.

One of your relationships wasn't sorted, one wasn't sorted correctly, and one had the wrong key fields. I changed the concatenated id fields to text, too, and removed the superfluous IDX fields.

Cat_jt2.zip

[Leb i Sol]

Hi J!

Many thanx!

I found some mistakes that you pointed out as well :0.

My "workaround" would be:

SCRIPT for "SubCategory"

New Record creation would be allowed if user has clicked (set g_SelectedID= L1 essentially single digit CatID)

much like u have "set it" on your sample with field gID1. -----visulaised on other layout

It will ensure that cID1_2 progresses as desired since L1 has be "re-entred" instead of incremented.....

increment, of course maing 'New Category' would be allowed if user has not selected one....=he/she wants to create a new Category not the SubCategory so L1 can continue to increment.

Now, I have test this!

Many thanx for all the help!

I will post the solution if it works out :|

Take care!