Need a field formula that inserts a unique and sequential number?

Hi,

This was answered via 1-1 consulation. For the benefit of the forum, please insert the follwing formula in "trigger on create" of the table:

let before := max((select Table_C).Record_Nr);
Record_Nr := before + 1;

 

Regards,

Sakshi- Ninox

I want to create a serial number in a very simple database. It is record of invoices received. There are only 5 fields - Serial number, Date of invoice, Amount, Invoice origin, Invoice type (eg for rent or transport or whatever).  I have no knowledge or coding or programming whatsoever and the recently posted formula

let before := max((select Table_C).Record_Nr);
Record_Nr := before + 1;

means nothing to me. I have very little idea of what it is telling me to do. I want the first invoice I receive in the tax year to be 1, the next 2 and so on. What do I do?

I think I am missing something that was probably shared in the 1 to 1 that I am not pulling out of the help message.  I am also trying to figure out what is meant by the answer.  Do we have to create another "table" to be nothing but the counting table and then the formula brings that value from the separate table into this field in the original table?  If so how and where is that created so that it links to the value column in the main database?

OR is this a value that is created in a column of the same data base that we are working in?  IF so where does the posted formula reside on either of the options, or is there a third option that I have missed.  I need a unique record identifier that never changes because it must be used to catalog documents that are kept in a separate document file, both digital and hard copy.

Thank you very much

 

Thank you for your assistance, love the program, and the helps/hints.

@k.f.kelly

@timtaylor.rrfan

this is easy.

You have a table "Invoices" and a field "Invoice No".

When you are in Edit Fields screen, paste this code to "Trigger on create" box:

 

let before := max((select Invoice).'Invoice No');
'Invoice No':= before + 1;

 

Now, the first invoice you create will be 1, the next 2 and so on.

 

Nick

Hey thanks that makes it easy.  I'll give it a go and let you know how it turns out.  Looks like though that the field it will reside in has to be a text field?  The number field allows me to utilize a year number prefix. such as 18- or Art-  and the the generated number.  Will explore more.

 

Tim

Whoops got confused right off.

The text typed into the message is different from the text shown in the snip.  You have both a text field named Invoice No. which you use in the typed message, and a number field named Number which is shown in the Trigger on Create snip but I cannot see the full formula to know exactly which does what.

Can you review and set me straight?  Thanks. Tim

 

Tim

I agree that would be the best solution. I followed the instruction, or at least, thought I did but it did not work for me. Clearly, I’m missing something (maybe a lot). I have posted the error message to Sakshi.

Guys, also take a look in to "Invoices" template from Ninox.

Has a field "Invoice No" and a trigger to insert the Year and a sequential number as Invoice No (e.g. 2018-005).

 

Nick

I had looked at that. It made even less sense to me than the instruction provided. I am completely new to this and these instructions are not really designed  for someone like me.

Got it to work. Thanks Nick. 

Playing with other features to see what else it will do. Will post full findings and how to in a couple of days. Have to build out a test bed db first. Not going to use my big one ',;~D

there are some quirks I want to iron out as well. Probably me not being totally familiar with the script. 

Here we go - Creating a Unique number with or without a prefix such as a specific letter code to indicate perhaps a location, or a numeric code to perhaps indicate a year.  You can only do one code however your prefix can contain both alpha and numeric characters except for a numeric 0.  You can put a 0 in the prefix however it will default as /0 so if you wanted the number 2018 as your invoice lead then it would become 2/018 with the unique number immediately following the 8 with no lead zeros.  Did not figure out how to add zeros that would be consumed once it became 10 or 100 so the generated numbers would always extend to the right of the prefix.  I did try inserting another symbol in place of the zero in 2018 and used a hyphen so it became 2-18 and it worked fine.

Alright, step by step then.  Please be advised that I am using the ^ symbol to indicate placing a space between characters or symbols so you do not wonder if that gap was a space or not.  When you see the ^ do not insert a ^ simply use the space bar instead, once for each ^.

Step 1: Generate a number field. 

If you already have a number field in the place you wish then you can work with that.  Be advised I did  try to reset the number field in an exististing table where I had generated a set of unique ID numbers manually.  Once I finished converting it to a auto generated number it reached back and made previous numbers unique and I was unable to manually change them.

Step 2: Edit Fields and choose your number field you wish to make unique from the left column titled Fields.

A grey pop-up dialogue box will appear titled 123 Number with 6 entry fields. (more if you activate more options which you will not need to do for this exercise)

Step 3: Enter your Filed name if you did not do so before. 

You cannot use the default field name of 123Number. 

Step 4: Choose YES under the drop down named required

Step 5:  Leave default blank

Step 6:  You may choose to set a min max, don't know if that will have any bearing on the min since it auto generates based on the prior cell's number and if it is row one it auto knows that the prior cell is zero and it starts with 1

Step 7: Click in the white space below Number format

Step 8:  Another pop-up grey box appears  with 4 entry fields named Digits, Thousands separator, Prefix, and Unit. Leave the Digits at zero so you have whole numbers.  Your choice on the Thousands separator, Prefix will be covered in Step 9 and leave Unit blank.

Below the entry fiields are 10 grey boxes with different numeric formats including whole number, decimals, percentages and monetary choices.  Ignore the Greyed boxes we won't use them for this operation.  

Step 9:  If you wish a Prefix in your unique identifier then this type it into the box, be it a year, a location, or an alpha numeric combination.  Just remember that a zero will be preceded by a / you could use a capital letter O however it will look different that the numeric 0.  You can experiment with it and see what they look like and then come back and change it.   This prefix will not change, only the auto generated number at the end will increase by an increment of 1 from one entry to the next.

Step 10: Once you have entered your prefix (not required to have a prefix, and if you do not  wish one skip step 9 and go to Step 11) click anywhere in the first pop-up window (except on the red dellete field button) and the 2nd pop-up will go away.  You can view your new prefix in the now populated Number format box.  Your prefix will appear with an auto generated 0 at the end of it if you are working in row one or have not started entering items into your database. If you don't like the way it looks then click in the entry field,the second pop-up will reappear and you can change the prefix, experiment back and forth until you are happy with it then go to step 11. (you can always come back and change it once you are finished with all the steps.)

Step 11: At this point you will need to write down the name of the Table you are working in so that you will have it at hand spelled exactly as you have it in the box that shows up under the heading Table Name to the left of the pop-up box you have been working in.  I will use the name <Database> for the example of the Table Name in the next steps.  I will also use the field name <Catalog^ID> in the example that follows as the name of the field that will be Unique.  Again the < and > symbols are to indicate these names.  You do not use them when entering your field name or table name.  Also the ^ is to indicate a space.  You do not need to use an underscore _ in the names instead of a space.  You can have spaces in your filed and table names.

Step 12: Below the Table Name field are 6 rows of fields.  In the third row find the empty field titled 'Trigger after update' and click in it twice.  The first click will cause the pop-up window to disappear, the second click will take you to another window with the title of your Creation as the header.  Directly below the Title is a left right pair of option buttons,  Visual|Text.  If Text is not highlighted Blue, then click on Text.  This will take you to a plain white screen with a greyed sidebar on the left containing all your field names.

Step 13:  Now we will enter the Code.  You will notice between the greyed sidebar and the white window a lesser grey column with the number one at the top of the colume.  Click your cursor just to the right of the number 1

Step 14:  Type the following line of code.  Remember the <^> are not to be entered as code, they are smply indicators as explained in Step 11.  You may notice that there are hints provided in brown text above the grey sidebar.  Just proceed as follows but watch and read so that you will see how the program walks you through the process. 

Code line 1 as follows:

let^before^:=^max((select^<Database>).<Catalog^ID>);

Step 15:  Hit return, the cursor will move down one line and the number 2 will appear in the grey column to the left.

Step 16: Enter the following line of code.

Code line 2 as follows:

<Catalog^ID>^:=^before^+^1

Step 17:  move your cursor to the upper right of the title bar and click OK.

If you have mis coded the Trigger after update field will be red with an error message.  Click in the field clear the code lines and rewrite it.  Remember do not use < or > those are my way of saying type your Table name or field name here, and the ^ symbol means insert a space at this point.

If the Trigger box is black text on a white back ground -->  Congratulations you have just created a unique auto generated number field.

Best of Luck (it took me about 8 tries before I started getting the hang of it and a lot more playing with the options)  If you find some other things this will do with the prefix or changing it mid stream such as starting it over in the same table with the next year as the prefix and resetting the invoice number back to 1 (without changing all the previous prefixes to the new prefix).  Let everyone know that it can be done.  I just haven't done it yet since I don't plan on using it that way.

Tim

My bad on the spelling errors, it is almost 11pm here and waayyy past my bed time.  Filed is not filed it should be field.  Other missed mistakes on spelling, lack of punctuation, extra words, or a instead of an, and I am sure there were another one or two I will blame on my fingers not keeping up.  Hope this helps with your programming.  I am still impressed with this little gem of a database.

Tim

Tim, thank you. I will follow your procedure and report back how I get on. Maybe in a day or two.

Tim, I had a couple of hours to play with the database today and as far as I know, followed your instructions exactly. No matter what I tried I experienced the same “Field not found” error. I’m now in contact directly with Ninox and hope to resolve the issue soon. I would like to thank you and the others who have offered help. I now have some grasp of what is going on so progress of a sort.

Kevin

Hi Kevin,

Would you mind sharing the two code lines you inserted?  It may help me see where I erred in my walk through in the above post.

Thanks

Tim,  as I typed in the code

let before := max((Table1).Invoice No.);
Invoice No. := before + 1 

Error returned   Field not found: Table1 at line 1, column 25

I tried this with and without (), '', and changing the Table name. I generated a few more exotic errors but "Field not found" seemed to be the main one. I suppose I am missing something obvious.

BW

Kevin

Nothing I didn't miss more than once.  Right after the double parentheses enter the command      select   with a space between the word select and the word Table.

In the name of your field Invoice No.  I am not sure if the .  Will be mistaken as a command since I see a . used in the code sequence.  If the addition of the command prompt select results in the Table being found but you then have another error then try renaming you field name to just Invoice No and remove the periods in the code statements.

On another note - I tried the code for creating a Unique number in the text box just like in Ninox's Invoice template.  Pretty doggone slick.  The way they do it auto creates the year in front of the invoice number and you can put as many zeros following the year as you wish, in front of the Unique number.  Not sure if they stay between the year and the Unique number or are "eaten" as the number increases to tens and hundreds.  Either way it gives you the ability to maintain an invoice prefix that is the current year and the year changes with the calendar date.  You do have to add a calendar and indicate the date the invoice is created.  If you don't want to use their Invoice template as is, you could try to copy verbatim the trigger codes (they do both trigger before and in the field a trigger after or two)

Tim

Tim, Some progress today. Nothing I tried with the original code made this work so I returned to the Ninox Invoice template as you suggested (as did someone else earlier). Now that I have a somewhat better grasp of what is going on, I modified my database to look like the model and modified the code to match. I can now get the database to generate serial numbers but of the form -001, -002 etc. For some reason the year does'nt show. This will work for me, the year is not  so important but I can now number the invoices as they come in. As you said earlier, the provison of a serial number field would have saved me a lot of time. I was almost at the point of returning to FileMaker Pro where this sort of thing is simple or even Excel where adding a serial number formula is straightforward. If you have any thoughts on the year  issue I would be interested to know but I am happy enough now. Thanks for all your help.

Kevin

Maybe I was a bit premature. If I create, say, 10 blank records the serial numbers count up as follows 001,001 then 002 to 010. If I then fill in the invoice information for the 10 records (I delete the duplicate 001) then create a new record I get a duplicate of 010! not 011. Sometimes it does'nt count at all. Sometimes if I create 1 record, complete the data fields  then create a new record I get 001 again,  then again 001, and again 001 etc. Back to my old FileMaker. This is really too much trouble for what should be simple operation.

Kevin

Hi Kevin,

 

Yes I suppose, however having grown up with computers since they were in rooms the size of my basement and guys with stacks of stiff cards individually punched would load that stack in and it would run a program based on where holes were ounces in th cards . .. . This is really a lot easier, it's just learning the ins and outs of the programmers language, without a dictionary ',;~D.  So I think that your doing well to get the invoice to number 1 to 10.  Sounds like were not able to connect to a calendar field which is where they were getting the date prefix from.  As to the counting issue that is something to try to recreate so I can address that.  Hadn't seen that happen yet in what I am doing.  Now I'll have to try that.  Unfortunately it will be a couple of weeks before I can have time to do that what with the Holiday weekend upon us.  Is there anyway you can use their Invoice template and leave the calendar/invoice number fields as they are and rename the other fields to fit your needs? (Of course check the trigger statements and make sure you don't rename any fields that are used in those programming codes.)

Best of Luck and I guess for the sake of unanimity I would add my voice to a call for both unique number and serial number fields to be pre-constructed. (But I do really like mucking around and making it do it myself)

Tim, 

I thought I would report on progress. Giving the problem some thought it seemed odd that  the code  from the Invoice template was creating a number on creation of the record - before any data was entered even though the code seemed (to me at any rate) to require at least one date field to be completed. I moved the code to the line 'Trigger after update' and immediately it began to work. As soon as I entered a date a serial number was  created and of the form '2018-001'. I have entered some data into a test file and it seems to work, even if I leave the program and then resume. So far, so good.

Kevin