Forum Discussion

MattHardy's avatar
MattHardy
Qrew Trainee
3 years ago

Cross Table Sequential Number

We use QB to generate invoices across multiple tables within one app - how can I create a sequential numbering system that pulls the next number when the user clicks a button? We don't want the invoice # created when the record is made, we prefer to create it when it's ready to send.

I currently have something hacked together using pipelines but there is a small delay which can result in duplicate invoices numbers in rare occasions. 

I have no problem reading/writing to a field using the API but I'm struggling to come up with a clean solution. ​

------------------------------
Matt Hardy
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    How about this for an idea which is not subject to Pipeline delays or failures.

    Create a table called invoice number. Check what invoice number you were up to now and use an Excel import to burn off all of the invoice numbers that are already used up. 

    Then, set the Key field of this table to be a Custom Key in the format of XXX-Record ID# of the "Orders Table", where XXX is the table identifier and Record ID# is the Record ID# of the tale which needs the Invoice #.

    So for example if your orders table needs an invoice #,  then create a record with the Key field of ORD-1234 if the Record ID of the order is 1234.  Make a relationship back own to orders  and then lookup the Record ID# of the Invoices table down to Orders.

    So on any table where you need to grab the next invoice # you make a button to go API_AddRecord to create the next Invoice # and the lookup will give you the next Invoice Record ID#. ​​

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • MattHardy's avatar
      MattHardy
      Qrew Trainee
      3 years ago
      Great idea Mark, thanks!

      ------------------------------
      Matt Hardy
      ------------------------------