Hi @Kasia,

I’m happy to be corrected as we haven’t had to do this, but it should be as simple as this example…

CREATE SEQUENCE emp_sequence
      INCREMENT BY 1
      START WITH 1
      NOMAXVALUE
      NOCYCLE
      CACHE 10;

Thanks @GPIE  I have created a sequence on my db since. Trying to call it in custom field now.

I’m not sure why default values aren’t available in the application for numeric fields. Here’s a hack to use what’s already there.

To demonstrate this, here’s a custom LU with a single field:

The field is Persistent, Number, Unformatted, Insertable, Updateable, Searchable, LOV, Indexed, Public, Approved.

Before publishing, I created the sequence and did a hack against the custom field table to set an expression default value.

CREATE SEQUENCE c_demo_seq
   INCREMENT BY 1
   START WITH 1
   NOMAXVALUE
   NOCYCLE
   CACHE 10;

UPDATE custom_field_attributes_tab
   SET default_value_type = 'EXPRESSION',
       default_value = 'c_demo_seq.nextval'
 WHERE lu = 'CDemoSequence'
   AND attribute_name = 'ID';

COMMIT;

Published.

Created custom page, Table Window, and granted.

Now subsequently hitting F5 produces new records with numbers pulled from the sequence:

The answers above are good -

Just a thought - rather than creating a Sequence using oracle script, there is a built-in sequence generator in the document management module.  It’s called Number Counter in Document Basic.

You can use it for anything, it doesn’t have to affect document management.

This way you are using IFS functionality.  There are many customers using Cloud, for whom running an oracle script to generate a sequence would be unacceptable or at least difficult, whereas using Number Counter is simply a case of setting up IFS Data.

That sounds interesting. Can you give an example?

To create a number counter:

1. Open the Document Basic window and click the Number Counter tab.
2. Click New.
3. Enter a value in the ID1 field. This might be the name of a specific project or a specific type of drawings.
4. Enter a value in the ID2 field. If you plan to use only one number counter for the project or drawing type, enter a hyphen (-) as the value. If you plan to have subprojects or something similar, you might want to use this ID2 field to reflect this. If you do not know what to enter, enter a hyphen (-). 
5. Enter a starting value in the Start Value field. This number should be 1 or higher
6. Enter a value for the Length Shown field. The value 0 (zero) means that the number will use as much space as it needs. Using a value larger than this will cause the system to prefix the number with zeros if the number has less characters than the value you enter here. 
7. Optionally, enter a prefix to be added before the generated numbers.
8. Optionally, enter a suffix to be added after the number.
9. Enter a description in the Description field.
10. Click Save.

Excellent, thanks. Quick test...

DOC_NUMBER_COUNTER_API.FETCH_NEXT_NUMBER('C1', '1')

Works like a charm.

You are very welcome.

Is there any way to reset that Number Counter with every new year using IFS functionality?

Hi @jengo 

sure, should be possible to make a manual change once a year using document basic. 
if you want to get fancy you could create some kind of script - perhaps using data migration - and schedule it to run at midnight. 

Nice hack Paul! :)

It’s “abusing” Docman a little bit (or is it just reuse...), but there should be no harm more than possibly confusing other “owners” of number counters. So just make sure everyone that works with setting up number counters are aware of it. And you probably want to create your own one for this specific purpose rather than to use the ones that Docman uses.

Also, who knows what crazy ideas the R&D people might have for the number counter functionality in the future. Don’t be too surprised if something happens one sunny day. (no major change has been done in this area since this was introduced though...) :)

By the way, there is a sequence object already in Docman (DOC_NO_SEQ) that is used for the default number generation (and we use them in many other parts of IFS too, where we need unique numbers). If you just want a new number for… something, you could use that too.

Happy hacking!

I have scenario were the I want to generate sequence number based transaction i.e when when adding the line to PR sequence number to be generated automatically and for other PR sequence should again start from 1 again

Sounds like you need a customization/modification. Or possible a custom event can do it in some way.

I cannot help with this, but others might, but I think you might want to write down a few more details…