3 Replies Latest reply on Sep 16, 2010 10:04 AM by philmodjunk

    Need help for a script to increment highest number in table (FM11)

    JopUrsem

      Title

      Need help for a script to increment highest number in table (FM11)

      Post

      Hello,

      I've been looking for a solution to this problem for some time now and have not found an adequate answer.

      In my table, the first field is an ordernumber. This field is created using auto increment.  When the order is processed, I want to browse back to the relevant record, click a button and generate a second number, a billing number, in the other field. The billing number must equal the highest billing number in the table + 1.

      I've tried to make a script that does:

      - sort records (billing number)

      - select last

      - copy field billing number

      - go to current record (!?)

      - go to field billing number

      - paste value

      The one step that seems impossible is "go to current record", because filemaker has "forgotten" which record I started with.

      At the moment I'm using the demo and am planning to buy the software, but this step is crucial for my application. So if anyone has a clue, I would be extremely grateful.

        • 1. Re: Need help for a script to increment highest number in table (FM11)
          philmodjunk

          There are several issues with that script.

          Copy and paste is not the optimum choice here, set Field and Set variable can do the job without changing the contents of your user's clipboard and won't silently fail if the referenced field isn't on the current layout.

          Here's a simple approach for generating a serial number value "on demand" that also happens to work safely in a networked environment:

          Define a new table, BillingNumbers, with two number fields: OrderNumber, BillingNumber. Make BillingNumber an auto-entered serial number.

          Click the Relationships tab and link this new field to your Orders table by OrderNumber. Double click the relationship link and select "Allow creation of records via this relationship" for BillingNumbers.

          Create the following script and attach it to a button on your orders layout:

          Set Field [BillingNumbers::OrderNumber ; Orders::OrderNumber]

          Place the BillingNumbers::OrderNumber field on your layout. This field will initially appear empty, but when you click the button to perform the above single line script, a new BillingNumbers record is created, the next billing number in the series will automatically be assigned to it and this number will automatically appear in your field.

          • 2. Re: Need help for a script to increment highest number in table (FM11)
            JopUrsem

            Thank you for the swift response and the clear answer! This works great! I would have never thought of this.

            Just one minor issue. When records are deleted, there will be gaps in the sequence. But I'll just have to adjust the auto-numbering in the BillingNumbers table to fix this.

            But nevertheless, this is a great solution, thanks again!

            • 3. Re: Need help for a script to increment highest number in table (FM11)
              philmodjunk

              Generally, in invoicing situations one voids an invoice rather than deleting it and creating such a gap in the sequence. The gaps aren't a problem for the database, but they upset your auditors as it looks like data is missing (and in fact data is missing if you delete the invoice.)

              You can also use a script to delete an invoice that includes Set Next Serial value to reset the next value on a serial number field. That only eliminates gaps if you are deleting the most recent invoice to receive a billing number.