Jump to content

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


This topic is 6434 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Hello Dan & Queue!

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

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

Link to comment
Share on other sites

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. smile.gif

Dan

Link to comment
Share on other sites

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. wink.gif

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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?

smile.gif Again, many thanx!

Link to comment
Share on other sites

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 blush.gif

Thanx!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 6434 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.