11 Replies Latest reply on Sep 17, 2009 10:43 AM by philmodjunk

    Script for Field of Calculated Value



      Script for Field of Calculated Value




      If my project database are indexed such that every record has a Estimate/Quote no which is defined as a running serial number.

      Only a confirmed/commissioned job is defined by a job number in running order. 


      Since not every job quoted will be commissioned, I would like to have a script whereby it will calculate from Job No. of the last/latest record and add 1 to the numerical value.  I do wish to have the job no. to be a serial running number as it isn't applicable in this scenario.


      Can anyone help me out with the script please?


      Appreciate it lots.  Thank you. 

        • 2. Re: Script for Field of Calculated Value
             Cos I realised that the inputs do not help me furnish the right follow-up in my case so I created a new message with only jus one scenario.
          • 3. Re: Script for Field of Calculated Value

            Can anyone help me with a script that can calculate Job No. by


            1)  finding the last record (that has the latest Job No. cos not every record has a Job No.); and then

            2)  adding 1 to the numerical value for the current record that the user is working on?


            Which means the Job No. is a calculated field... unless you would advise otherwise. 


            Thank you. 

            • 4. Re: Script for Field of Calculated Value

              Wondering if there's any solution provider for this?  


              *stuck developer* 

              • 5. Re: Script for Field of Calculated Value

                You don't explain why it has to be a "a serial running number", but taking your word for it...


                If I had to do it this way, I'd create a separate table with an auto-numbered serial number field in it.

                Then, I'd have a button to click when the the Job is commissioned. That script would create a new record in this related table. Since a record is created in this table only when the Job is commissioned, commissioned jobs will be numbered in sequential order courtesy of this related table.

                • 6. Re: Script for Field of Calculated Value

                  Hi,.. yea I guess serial isn't the right type for this job no. field.


                  I wish to avoid creating another table that has a record for every record in the main table so as to simplify the whole database.


                  I've been thinking,... if i intend to create a global variable in the database.  This global variable will be read and incremented when required.  The new value will have to be updated back to this global variable and be entered into the Job No. field of a specific record in the main table.  How would you script this?


                  Thank you.


                  Novice developer 

                  • 7. Re: Script for Field of Calculated Value

                    Actually you would only have a record in the second table if a Job is commissioned if I read your first post correctly.


                    Using a global field may generate more problems than it solves. In a networked environment with multiple users it won't work at all as each user would see their own "virtual copy" of the global field and the value in the field reverts when they quit filemaker.

                    • 8. Re: Script for Field of Calculated Value

                      Thanks PhilModJunk... yes its true not every record/job is commissioned... I do wish to avoid having another table and simplify the current database.


                      With regards to the issue you raised about the global field, how about if I set the original value of the field?  Does it still revert when filemaker is quit?

                      If disregarding possibility of multiple users, how would the script be written? 


                      Alternative ideas are welcomed. 


                      Thanks also to everyone else who reads this....


                      • 9. Re: Script for Field of Calculated Value

                        I believe the resulting script would be a greater increase in complication than adding a simple utility table in the same file. The table and it's inclusion in the file would be very simple and utilizing it in scripts and layouts would be dirt simple.


                        In a multi-user environment only changes to the global field made on the host computer will "stick" after that session of the DB is closed. Further complicating things is the fact that if user B changes the value of a global field, only user B will see that change. Other users will only see changes they make to the global field. Thus, your counter for user A could show "5" while user B sees "6" and User C Logs on and sees "3". When they close the database, all values reset to "2" or whatever the value was originally set from a single user or host session.


                        Trying to generate a bullet proof script (even in a single user environment) for this is not an excersize I care to engage in. Perhaps another poster will take this on.

                        • 10. Re: Script for Field of Calculated Value

                          I'm convinced of your suggestion and I'd shall heed your advice.  Can you elaborate on how I can create the script where your solution is implemented?


                          Your feedback will be greatly appreciated.  Thank you. 

                          • 11. Re: Script for Field of Calculated Value

                            In your Job table you should already have a auto-enter serial number field. Let's call it JobID.


                            create a new table, Commissioned, with two fields: JobID (number) and CommID (serial number).


                            Establish a relationship between your two tables:


                            JobTable :: JobID = Commissioned :: JobID


                            Enable the "Allow creation of records via this relationship" option for the Commissioned side of this relationship.


                            To assign a CommID serial number, simply use a one line script:


                            Set Field [Commissioned :: JobID ; JobTable :: JobID]


                            a new sequential serial number will be assigned.


                            If you add the field Commissioned :: CommID to a layout. It will be empty untile the above script step generates a serial number for it.


                            Note that if two users trigger this script for the same Job record, both will get the same serial ID number. The first instance of the script will generate a new serial number and the second time the script is executed, no change occurs.