Is this one series of numbers or two?
Yes two sets of numbers like the SECOND set you ask
I was afraid of that. Such invoice numbers should NOT be used as a primary key in your invoice table. Care will need to be taken if this is a file accessed by multiple users that might be creating invoices at the same time.
Is this the only two values in the series or have you just given two as an example? I know of a way to generate serial numbers "on demand" that could be used to create two different serial number series, but it's not the option to use if there might be more than just the two invoice types. If there are (now or as a possible future) more than these two invoice types a self join by type can be set up as a way to assign the next value in the sequence.
- It is multi user on server13, 2 or 3 people, but not likely at same time.
- Yes only two values needed - 1. just serial number, and 2. serial number with prefix TE
Attached is screen shot of the old script that was used but always had problems.
1. problem when making new invoice it would not start with the most recent number, (maybe not knowing which set to use) had to click and click (sometimes 200 clicks) as it stepped thru existing numbers to get to the last one, then fm would start a new invoice with new number.
Other solution I was planning to do was to just make a new field adjacent to the invoice number field that auto populates with if statement: if customer is not from VA, from out-of-state, thus TE would appear on the record. Also used in a Report.
Thanks PhilModJunk for your help and ideas.
This method should work even if you have 50 people creating new invoices and running this script to assign serial numbers all at the same time.
The method requires 3 auto entered serial numbers in three tables. The first is the real primary key and can stay fully hidden from your users. The other two are the two you want for this purpose.
Your tables and relationships would be set up like this:
Invoices_::__pkInvoiceID = InvoiceNumbs::_fkInvoiceID
Invoices_::__pkInvoiceID = InvoiceNumbsTE::_fkInvoiceIDe
enable "allow creation of records..." for both the InvoiceNumbs tables.
Define a simple auto-entered serialnumber field in InvoiceNumbs called InvoiceNumber. Do the same for InvoiceNumbsTE but specify either TE0000001 as the next serial value or possibly 1TE if you want to put the "TE" on the end of the number instead of the beginning.
Now this script will generate the needed invoice number:
If [ Invoices_::Customer Location = "VA" ]
Set FIeld [ InvoiceNumbsTE::_fkInvoiceID ; Invoices_::__pkInvoiceID ]
Set Field [Invoices_::InvoiceNumber ; InvoiceNumbsTE::InvoiceNumber ]
Set FIeld [ InvoiceNumbs::_fkInvoiceID ; Invoices_::__pkInvoiceID ]
Set Field [Invoices_::InvoiceNumber ; InvoiceNumbs::InvoiceNumber ]
That should work. you might need to insert a Commit records between each pair of set field steps, but I don't think it's needed here.
How this works, The first set field step in each pair of set field steps generates a new record in the related table and thus the auto-entered serial number field, InvoiceNumber, will auto-enter a new unique serial number.
I got it - I understand!!
This is mega fabulooosooo PhilModJunk
Thank You Thank You
Whoops - a snag somewhere
Got this set up - just doesn’t put the new number into the field. Invoices_::Invoice Number.
See screen shots of script and chart.
What did I miss?
Please Advise, Thank YOU!!
OH Nevermind - I figured it out - forgot to put auto serial number in pkInvoice ID. Working now.
THANK YOU AGAIN
for the script!!!