I'm confused by what you are trying to do with that script.
You create a new records in Invoice numbers. I would guess that Invoice numbers::invoice number is a field defined to auto-enter a serial number.
So far so good, but then you use that new number that should not exist before this point to search for records in a different table, Architects Invoices. Why on earth would that table have a number that you just created brand new in the Invoice numbers table?
I do know of a way to generate unique serial numbers "on demand" and without changing layouts if that is what you are trying to do here. Let me know if that's it and I'll make a more detailed post spelling out how to do it.
Thanks for the help. I have seperate tables for architects and builders (with hindsight should have had a single table).However as you surmise I need to create unique serial numbers on demand without changing layouts.
Say that you have this relationship:
MainTable::_fkInvoiceNumber = InvoiceNumbers::__pkInvoiceNumber
with "allow creation...." enabled for InvoiceNumbers and __pkInvoicNumber defined to auto enter a serial number.
Then the following one line script:
Set Field [InvoiceNumbers::AnyFieldButPKHere ; "some value here" ]
will create a new record in InvoiceNumbers and copy the value of __pkInvoiceNumber to _fkInvoiceNumber if no such related record exists. If, by some mischance, this scrpt step is executed again, no such new record is created in InvoiceNumbers, only the value of "anyFieldbutPKHere" is modified.
Here is the way I would usually do this. This video shows an invoice number restarting each month which I would never do. Most people like the number to restart every year but the technique is the same.
The method that I've shown here never restarts the invoice number, though this could be done if you had no choice in the matter.
As a person who processes invoices for a living, I do not like vendors who duplicate their numbers from one month, one year or whatever to the next. AP Modules are generally designed to prevent duplicated invoices. And to get around that "games" have to be played... add a -1 or -2. In my current position it is add a "D", the next time add "DD" and I have used "DDD" meaning the invoice number was re-used three times after the original.
Thanks Phil - easy when you know how !!!