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.
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.