Creating and deleting invoices - maintain serial

[Lawlerdev]

I am trying to develop a method that will allow me to create an invoice in FM7 which has portal lines for the invoice items and will allow me to delete the whole record including related portal records if desired. However if the record is to be kept, it will assign the next correct invoice number to both the main record and the portal records to maintain the link.

I have most of this working based on the main record being created with the ID field being the unique record number. When portal records are created they use this ID. On saving, the system increments a number from a single file table, grabs this number and sets the portal records to it and finally the main record ID to it too.

This all works fine in single user situation, but in multi-user environment the main record ID for both records is the same. The serial number is not being set quick enough. I have even tried using a global filed in the main record as a temporary holding point for the number without success.

The revert function with a standard incrementing serial number is no good either as the serial number is set as soon as you click outside a field and it won

[transpower]

Set the serial number on commit, not on create; that will allow reversion.

[Lawlerdev]

I have tried that but if the serial no. is not comitted then you cannot create related portal records as it is the key.

[mdpres]

Stephen,

You could create a new invoice, then if you want to delete it use the Set Next Serial Value script to reset your ID.

Dean

[Lawlerdev]

Thanks for the suggestion,

Unfortunately this won't work in a network environment. If you try to maintain a sequence of numbers (audit trail) even if you reset your ID you will be left with unaccounted numbers. Imagine a number of users entering invoices and the number starts at 100;

User 1 is on 100

User 2 is on 101

User 3 is busy and has entered 102, 103 and 104

If user 1 decides to delete 100 resetting the serial number will either leave a gap in the numbers or start again at 100 (worse problem)

Any other ideas greatly appreciated!

[-Queue-]

Try incrementing the serial before setting any records with it.

Loop

Set Field [serial; serial + 1]

Exit Loop If [not Get(LastError)]

End Loop

Commit Records/Requests

Set Field [gserial; serial - 1]

Then use gserial as the id.

[hayesk]

Just one question: Why do the invoice numbers need to be sequential without any gaps?

It seems there is no real reason to require this, and it is causing more work. And as you get more and more records, the creation of the Invoice number will become slower.

[-Queue-]

One word: audit.

Also, if the 'next' number is stored in a preferences file (as it would necessarily be in my example), the time it takes for the number to increment won't increase with the size of the record set.

[hayesk]

What kind of audit requires sequential numbers on invoices? When you use paper invoices and you throw one away, there's no problem. It should be the same for electronic.

Anyway, that's not for me to decide. If it is for audit purposes then I wouldn't allow deletion of invoices at all. Just mark them void. When searching, script the inclusion to omit all voided invoices.

[raymanj]

I have been having the same problem with my invoice solution too. I have never been able solve this in Filemaker. A network with several users becomes hard as explained above.

Regarding the question about sequential numbers for invoices. Any company that has been audited by a tax official knows major fines will occur for each missing invoice number. Many years ago we were fined over $75000 for this very reason. At that time no computers were involved. All paper invoices.

I think this is a major part that filemaker is missing. Being able to control sequential serial numbers by the developer in a network environment.

What hayesk said above about VOIDing the invoice is the only answer I know. But this will only work if you are working with one set of sequential numbers. If you need to keep track of several sets for different departments or branches, then the voiding will not work.

This is my problem, multiple branches.

[Deep Thought II]

yes the sequential number is very important. missing numbers, although may not necessarily mean lost invoices, still create gaps among invoices... which is a bad idea if u have invoice numbers say,

IN0001, IN005, IN006, IN007, IN009...

u wouldn't even be able to know if the missing ones disappear by themselves or whatever happened to them...

assuming those missing ones are caused by such serial gaps all the time... wouldn't that make u insecure?

[crpatter]

You could logically delete an invoice if it was cancelled. Create a flagged that is marked when an invoice is "deleted" That way you can account for all invoice numbers.

[DykstrL]

I have a similar situation is a PO system where the auditors require tracking of every PO whether used or not.

I just do not allow deleting of records. I just include instructions for users to mark VOID on any POs not used. It gives us an audit trail that the audit office likes and it does not confuse the users - it tracks who created the PO and who voided it.