Create sequential row numbers in invoice print form

I've modified the Invoice Template to make invoices and "line sheets" for a jewelry company; see screen shots of Invoice tab and the printed form. However, the Record #'s are fixed, and after records are sorted by the far right column on 'Invoice Items', I must manually add sequential numbers for each item ('LineNo' column). Obviously this is not ideal with 80+ rows.

I've found 'on trigger' scripts that use count() +1, but this doesn't handle the intermittent  'Header Rows' that must be skipped. There must be a way of scripting a button to input numbers in 'LineNo' after the rows are sorted by 'colSort' --maybe a trigger action would work too.

I would appreciate thoughts on the right approach and code. I'm reasonable good at modifying code to fit, I'm not experienced enough to start from scratch. 

Many thanks in advance.

10 replies

    • Mel_Charles
    • 6 mths ago
    • Reported - view

    @bflack - If I understand you right I do something silzer to this to auto input line numbers in the child table(subform). My need was to input line numbers is steps ie 2.4.6 etc. This then allows me to input and in between number ie 3 so that when the is sorted by the line number my quotes etc appear as I want them. Ie you input 10 lines and relaise you needs to insert one at line 6. After sorting i use another button script to reindex the whole record.

    it's deffo not what you want but just might give you a clue ..

    I'm guessing your header rows are pretty common so I would of thought that should be easy(ish) to cater for and skip.

    Here is a quick screen shot- The button INDEX does the reindexing

    when I enter product the save button throw all the data into the child form along with the QLNum being added (as said I do steps of 2 but it can be anything.

    If you think useful as a starting point - I can load the scripts for each section. But apologies it won't be tonight as I now have to go out

    • bflack
    • 6 mths ago
    • Reported - view

    I would be very interested in understanding the scripts when you have some time. Thanks for taking some interest!

    • Mel_Charles
    • 6 mths ago
    • Reported - view

    Here you go

    Some screen shots to explain my logic flow (as i want it)

    Quote screen

    Add a product

    Pressing add a product button opens up the data entry pad for product entry

    Enter products and press save. This same the line onto the sub(child) Table record and leaves the data entry fields intact so a quick edit can be made for same qty break and price etc. Save and clear clears the fields fro a different description etc and I will let you guess what X button does

    Product line has been added to the bottom of the quote. However, in this example case I want to move it up to be in line 2. So I open the sub record and change the number of QLnum to 3. and Bob's your aunt Emma! - it shuffles the line up the page. 


    I could leave it like this but i can also chose the to press reindex to change all the QLnum lines in steps of 2 - Yes I could automate this process as part of above but i like to manually do this.



    On form open - trigger after new

    let before := max((select Quotes).QteNum);
    QteNum := before + 1;
    Choice := 1

    this gives me my next quotation number


    QLnun - Trigger after update - field on main form not same field on subform!

    QLnum := max(QuoteLines.QLnum) + 2


    Save Button

    et xCurrRec := Id;
    let xDesc := 'Product Description';
    let xQty := Qty;
    let xCostPrice := 'Cost Price';
    let xMkup := Mkup;
    let xSellPrice := 'Sell Price';
    let xVatRate := 'Vat Rate';
    let xSupplier := Supplier;
    let xQLnum := QLnum;
    let i := (create QuoteLines);
    i.(Quotes := xCurrRec);
    i.(Desc := xDesc);
    i.(Qty := xQty);
    i.('Cost Price' := xCostPrice);
    i.(Mkup := xMkup);
    i.('Sell Price' := xSellPrice);
    i.('Vat Rate' := xVatRate);
    i.(Supplier := xSupplier);
    i.(QLnum := xQLnum);
    QLnum := max(QuoteLines.QLnum) + 2


    Index Button

    let Zcnt := 2;
    for Zql in QuoteLines order by QLnum do
        Zql.(QLnum := Zcnt);
        Zcnt := Zcnt + 2

    As stated this does not answer your query but might be useful somewhere along the journey

    I think that is it. But if there is anything else that is relevant etc I will add another reply

    • bflack
    • 6 mths ago
    • Reported - view

    Thanks for the response and information. We're solving very similar problems, with differences that you've noted, but I find the Index Button most intriguing.

    Our product data process differs in that I assign an Item No (know as a SKU in retail) to each item, as well as a "Collection" (Mental Health, Rescue Meds, etc). These are merged into a sorting column "itemCatg", generating the order of the item rows. As I understand it, you assign a row number when inputting the product data, but then re-index the numbering. I would like to understand that last step/button script better.

    "for Zql in QuoteLines order by QLnum do" - what is the "Zql" reference? I understand that "QuoteLines" is the table and "QLnum" the assigned order.

    I'm also confused about the utility of re-indexing by 2's, especially if you are re-indexing anyway. I find a serial numbering helpful to reconcile to the number of product items --distinct from total quantity ordered. 

    Many thanks for indulging me in what may be obvious to many.

    • bflack
    • 6 mths ago
    • Reported - view

    Another question ... "Aunt Emma!", thought this was about "your uncle". Great expressions in the UK.

    • Mel_Charles
    • 6 mths ago
    • Reported - view

    bflack @bfalc

    Zql is simply the variable - I just called it that at time as I started my variables from A-Z an the "ql" simply means QuotesLine - I don't do this now as all my variables start with myXXXX and all my formula fields start with a small x - xCost etc

    Steps of two's is simply to give me a point to insert into.. ie if all my lines were sequencial then i would have to modify several lines before I could insert a line. whereas I can add in a line buy changing the suggested QLnum(main form) before pressing save button. Thus QLnum (mainform) acts as both and editable insertion point and automates the next suggested line number.

    My classic answer for this is as follows
    I do a quote for a customer
    Product1 x 72
    Product x 144
    Print screen

    Send it to the client. They come back and ask for product1 for 100 to be added to the quote - I need a quick way to insert it into the correct place/ or be able to shuffle the lines around to suit

     I only showed you one type of quote. We do have them with sku codes too. The principle is exactly the same - I do a dynamic lookup to get SKU info and auto fill in the other data entry description prices etc. QLnum is generated at the save button stage.

    ah yeah - the saying is "Bob's you uncle " for their you go/ hey presto etc. It is just I saw a variation of this saying many years go in a comedy stage production and it has stuck ever since.....

    You know what we brits are like !

    • bflack
    • 6 mths ago
    • Reported - view

    Thanks for more insight, especially on Aunt Emma. I'll fiddle more using the re-index, maybe next week. All the best!

      • Mel_Charles
      • 6 mths ago
      • Reported - view

       you are most welcome ! 👍

    • bflack
    • 6 mths ago
    • Reported - view


    Happy to report success, thanks to your examples.

    Some adjustments were needed for my model, sorting is done with colSort (a merger of several Article criteria), and importantly, values apply only to lines identified by the choice field: Article.itemCatg 1 or 2. There may be a more eloquent version, but this works for me; see below. 


    let Zcnt := 1;
    for Zql in 'Invoice Items'[Article.itemCatg = 1 or Article.itemCatg = 2] order by Article.colSort do
        Zql.(LineNo := if Article.itemCatg = 1 or Article.itemCatg = 2 then
        Zcnt := Zcnt + 1

    Again, thanks and it's been a pleasure. Regards to Aunt Emma.

    • Mel_Charles
    • 6 mths ago
    • Reported - view

    @! bflack - Excellent - Glad you are sorted !

Content aside

  • Status Answered
  • 6 mths agoLast active
  • 10Replies
  • 194Views
  • 2 Following