Reset line id to 1 with new invoice

bink inkel said:
3 Tables: HOME where I enter the date, INVOICE, where i enter customer info, and LINE ITEMS.

 That is an interesting idea. So you would enter in a record in HOME that would store the date. Then enter in as many invoices that you have for that day. Then as many invoice items you have per invoice.

I think most people would just have a date field in INVOICES.

On to your main question:

bink inkel said:
I would like  serial numbers like   "Home Id - Invoice Id"  or "000-000"

Just to be clear, the field "Id" is reserved by Ninox to create a unique record number that it uses to track records. It is not accessible by the user. You can create a new field called HomeID and then you can do anything you want with the data in that field. You would also create number fields in INVOICES.invoiceID and LINE ITEMS.itemID.

You can add to the Trigger after new record in HOME to:

let prevID := max((select Home).HomeID);
HomeID := prevID + 1

You can add to the Trigger after new record in INVOICES to:

let t := this;
let starting := (select INVOICES where HOME = t.HOME);
invoiceID := max(starting.invoiceID) + 1

You can use the format() command when you need to display your IDs. For example you can create a formula field called trackingID and put this in it:

format(HOME.homeID, "000") + "-" + format(invoiceID, "000")

bink inkel

I have a variation of this with my quotes (but the same would apply to invoices)

Just for information for you to show what else is possible

So my quote have a header form and quite rightly a sub (child)form for the x number of quote lines

this is my process

• I fill in the data for the sub line from my main form and use a button to write this detail line directly into the sub form (saves me opening the sub form to enter data).
• I do use the system ID for the line numbers -
• I use an additional number field that I step in 2's etc. This means I can adjust this number so that sub lines can be shuffled around fit in place in additional lines.

So the basic entry is as follows (the full presentaion quote is shown on the FULL quote Tab) 

So this entry part - shows the Main item section . Pressing save save the info the sub grid/from ( this leave the info in place on entry pad to add a similar product ie same desc but different qty etc.

save a clear button does what it says

x but just clears the entry pad.

Changing the QLnum (Quote Line Number) allows me the move the lines on the final quote

Index button at top of form re indexes all lines in steps of 2