Advancing Invoice Number

[Leather Knight]

Is there a way to have a field "Invoice Number" to be blank when starting, and then have the user input the starting number, after which all subsequent records would increment by 1 and still allow the user to over-ride the data if needed?

[aldipalo]

Use 2 fields:

Invoice_Number //Auto Enter Calculation

Case(

IsEmpty(gInvoice_Number); 0; gInvoice_Number + 1

)

g_Invoice_Number //calculated field

Invoice_Number

Now whatever number is first entered in Invoice_Number gInvoice_Number will be set and when you create a new record Invoice_Number will increment by 1, but, still allow the user to change that number.

The problem is if the user changes the number to a lower value you will have duplicate invoice numbers.

[Leather Knight]

Good idea, but the result continues to remain "0" unless it is manually changed...

[LaRetta]

Kevin, I won't question why you want to reset a serial (which can be quite dangerous). But I think you know what you are doing so I would suggest using a global field. If the global field is empty and a User clicks 'reset' then it resets to 1 and continues from there. Otherwise, if reset is clicked, it sets to the global value and continues from there (see attached).

If you instead want to stop a user if the reset global is blank, you can script for that as well. It can be adjusted as you need it.

LaRetta :wink2:

InvoiceNumber.zip

Edited November 28, 2008 by Guest

[LaRetta]

Nope. Don't use this. It breaks unexpectedly. If you create several new records, leave your cursor there (or even if you leave it in another field), change the global amount then click 'Run Serial' it breaks at random and begins a different sequence than it should. Even with a Commit Records/Requests, it doesn't change properly EVERY time I request it.

Baffling.

Well the theory is okay. But I haven't often used Set Next Serial Value [ ] but I didn't expect it to break like this.

[LaRetta]

I believe I have isolated the issue to this: We must BEGIN with a Commit Records/Requests as well. Otherwise, if the cursor remains in the global field, FileMaker doesn't recognize the change to the global value.

So add a Commit Records/Requests also as first line of script. I can't make it break if this step is added.

[aldipalo]

Did I misunderstand?

"Invoice Number" to be blank when starting, and then have the user input the starting number, after which all subsequent records would increment by 1 and still allow the user to over-ride the data if needed?

Isn't that what it does? I guess instead of setting the number to "0" you could set it to"".

Anyway, I like LaRetta's approach better. It's much simpler, yet, more elegant.

[Leather Knight]

I may not being specific. If a new record is made it will increment the field Invoice Number by 1. It will start out empty if there were no records to begin with, like a clean database. But if 3 records in the user needs to change it from 103 to 2100, then the very next record will pick up from the 2100 and the new record will show 2101 instead of 104.

[aldipalo]

I must be the one that's confused. See my Signature.

Doesn't this do it. Anyway, BOL.

Al

invoicing.zip

[Leather Knight]

Ok that is exactly what I am looking for. However, when I try to replicate your solution, I get 125 starting out, created a new record and it says 126. I change it to 345 and the next record starts as 126 instead of 346. Can you show me my error?

Sales_Orders.zip

[Leather Knight]

BTW...Nice sig :)

[aldipalo]

You have to uncheck "Do not Replace Existing Value" on the AutoEnter calculation for gInvoicenumber

[Leather Knight]

Perfect! That is exactly what I wanted...Thanks.

[aldipalo]

I just want to reiterate again that the problem with this method is if the user changes the invoice number to something lower than the current number you can wind up with duplicates. That's why I said LaRetta's way was more elegant and gave you validation protection.

I've played around with it some, but, couldn't come up with a way to validate the number using this method. Perhaps, someone else has a way to do it?

[LaRetta]

Well, Al, I'm truly still not sure what Kevin wants nor why he wants it; we have been given no vision as to the purpose. So I feel we're stabbing in the dark and I would normally never suggest using anything other than straight auto-enter serials. I assume this time, it is manual entry from an outside source's invoice numbers and this allows a method of easing data-entry.

You are correct that the method offers no protections but neither did mine, ie, if invoice number was reset to 2 it would happily comply with no complaint. If Kevin says it's what he needs, who are we to say otherwise particularly since we don't really understand. Understand?

Kevin has been around enough to know that if he realizes there is still an issue with his method or what you have given him, he'll come back to keep working on it. It appears you've helped him and that's what counts. In meantime, :beer:

[aldipalo]

I agree. Perhaps this is simply an invoice number and not the actual record serial number. If it is just a number then I guess it really wouldn't matter one way or another.

[Leather Knight]

It is just an invoice number and the reason for this field being modifiable, is so the end user has the opportunity to set/start their invoice numbers from where they left off in a previous setup. Rather than having 2 copies of invoice 102. Not to mention the ability to hand enter previous invoices done on paper Just trying to allow for it to be easier.

[bruceR]

It is just an invoice number and the reason for this field being modifiable, is so the end user has the opportunity to set/start their invoice numbers from where they left off in a previous setup. Rather than having 2 copies of invoice 102. Not to mention the ability to hand enter previous invoices done on paper Just trying to allow for it to be easier.

But it's not easier. That's why we're still talking about it.

None of that is an adequate reason for doing what you're asking to do. Still looks to me like you should be using Filemaker built in numbering.

Why would you EVER want to hand enter a number? The explanation above really does not address this. Your explanation is circular. You're just saying you want to do it because you want to do it.