Jump to content
Sign in to follow this  

Creating and deleting invoices - maintain serial

Recommended Posts

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

Share this post

Link to post
Share on other sites

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

Share this post

Link to post
Share on other sites

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

Share this post

Link to post
Share on other sites


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


Share this post

Link to post
Share on other sites

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!

Share this post

Link to post
Share on other sites

Try incrementing the serial before setting any records with it.


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.

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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? tongue.gif

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Similar Content

    • By Tony Morosco
      I'm a botanist, and the tables I am working with are for tracking botanical garden collections. The data represents plants in the garden, and the plants are tagged and show up in the database.  The tables I am working with were created in FMP 7, and I'd like to open them up in FMP 11 (or later.)  The system hasn't been used in years, but still has valuable information.
      One of the tables is giving me problems using the FMP convert and recover commands.
      These tables are all inter-related.  The main table is the Accessions table, which contains records for all of one kind of plant, from the same source, received on the same date.  It is basically a museum standard.
      The other tables are related to each other through this one main table.  The Species table is related to the locations table through the Accessions table. 
      (i.e.  table A relates to table C through the table B, the intermediary)  
      From the Locations table, we can't see the the species information unless the accessions table is present.
      When issuing the open command on the main table to convert the database to FMP 11, I get the message:
      "Accessions.fmp7" is damaged and cannot be opened.  Use the Recover command to recover this file. When using the Recover command from v. 11, I get another message:
      WARNING: problems were detected while recovering the database.  Please review the Recover.log file to see where problems were found and their severity.  The recovered file should NOT be used going forward; copy only the most recent work from it into a backup copy of the original file. Recovery results:   File blocks: scanned and rebuilt 563 blocks, dropped 214 invalid data blocks.   Schema: scanned fields and tables, 1 items modified   Structure: scanned; 1 items modified   Field indexes: rebuilt  
      Opening the recovered database, there are only three records present.  There should be hundreds.  So obviously I am looking on how to wrangle this database open.
      I've attached the log file here, as well as the database structure map.  
      The other files have converted just fine.  But since the main table won't open, we are kind of stuck.
      I can share the files with you through Dropbox or whatever, if needed.
      Please let me know any thoughts you have, either basic or advanced.  And ask for any clarifications or additional questions.   :-)  

    • By Tumma K
      Hello, All!

      I am an aspiring developer for Filemaker. The company I work with is stuck in the past working off of Filemaker Pro 4.1

      I was given the task of bringing us up to Filemaker Pro/Server 13. So far my conversion prototypes are successful but we recently had a layout issue that can only be fixed in versions 3-6 (as the file is an .fp3) I work off of a macbook while our network is all Windows 7. In order for me to repair the layouts without tampering our active database, I decided the best option is to repair a copy of our solutions off the network. Unfortunately, when I go to download the trial version of Filemaker Pro 6 off of the respected website, the file is corrupt! I've tried multiple times, with different extraction apps and in different directories.

      My question is;

      Does anyone know a place where I could obtain version 6 (or better yet, 4.0) for an OSX computer? I've looked everywhere!
      Thank you for your time,
      Tumma K.
    • By MrEddByrnes
      I'm hoping my question can have a happy ending. In the mid-90's, I purchased Filemaker 3. When Filemaker 5.5 Pro was released, I bought the update CD, which requires the user to either have FM 3 installed or to have the installation CD for FM 3. I've used it all these years, most recently with Windows XP Pro, and it has worked just fine. The databases I began with were long ago converted to FM Pro 5.5 databases.
      I'm still using FM Pro 5.5 on a laptop with WinXP Pro, but in 2013, I purchased a PC with Windows 8. I haven't been able to install FM 3 on it, therefore can't install FM Pro 5.5. I am retired and rarely use Filemaker, but I have a few Filemaker databases I'd like to add to my Win 8 machine. I don't feel it's worth upgrading FM for the sake of using a couple of databases.
      Has anyone else run into this situation and/or have a (possible) solution? Is there perhaps any other software that can read FM 5.5 databases? Thanks in advance for your help.
    • By bmill
      I am using a custom filemaker solution for medical office billing written with fp5 running on a mac with snow leopard. In addition, I have a patient management db (which I wrote) that is linked through pt. ID number to the billing program allowing transfer of some demographic information (name, DOB, etc).
      Other than being limited by hardware restrictions, the billing program serves our needs for now and upgrading to fp12 will take some time (and money).  In the meantime, I am upgrading my pt. management program to fp13 and would like to move new patient demographic information from the billing program ( fp5 running on snow leopard through Parallels) and the new pt management program ( fp13 running on OS X 10.9) on the same mac.   
      Ideally, demographic information would be entered once into fp5 and then a scipt would make the data available for fp13.
      Any ideas on how to make this work?
    • By randyinla
      Hi, can anyone tell me why my on-line database might have stopped allowing me to delete records?  All of my access privileges and passwords are correct.
  • Who Viewed the Topic


Important Information

By using this site, you agree to our Terms of Use.