4 Replies Latest reply on Feb 14, 2013 6:43 AM by BruceHerbach

    Calculation not incrementing

    soberbob

      Can anyone tell me why this doesn't increment correctly? I'm trying to create a basic calculation from an if and let function without using a script. Also I do have a working script, but it fails to work properly on a mass import as I'm not performing the action on object modify I assume. Either way it seems this should work with a basic function that increments a variable.

       

      #Basically if user selects from a dropdown list "Staff" set the host to ABC-2000, ABC-2001, ect...Note that it does display properly, but it doesn't increment.

       

      If ( ID = "Staff"; Let($$ABC="ABC-"; Let($$host = 2000; $$ABC & ($$host+ 1))))

       

       

      Thanks,

       

      Rob

        • 1. Re: Calculation not incrementing
          Mike_Mitchell

          Whenever the calc evaluates, $$host gets set to 2000. It always resets itself. If you want it to increment, you'll need to set $$host somewhere else.

           

          Mike

          • 2. Re: Calculation not incrementing
            BruceHerbach

            Rob,

             

            Try this:

            Let([

            $$ABC = If ( ID = "Staff"; "ABC-"; "");  // Not sure what you want when ID does not = staff

            $$host = if( isempty($$host); 2000; $$host +1) ];

            $$ABC & $$host)

             

            Bruce

            • 3. Re: Calculation not incrementing
              soberbob

              Bruce,

               

              This pretty much does the job, except its always enters the next increment into the field no matter what.  I only want it to enter something when:

               

              if(ID = "Staff"; 

               

              If Staff is not selected it should leave the field blank and does not increment the variable.  For example if I enter 20 records that are not equal to staff it should pickup where it left off.  The above is instantly placing the value "2001" into the field upon record creation.  Then once I set ID to Staff it changes it to ABC-2002, so it seems to increment always then a second time if I select "Staff".

               

              Also is this the best way to even do something like this?  Once this works is there possibility of the variable being reset?  Is it best practice to use an alternate table that auto generates a serial number that does the ABC-2000, ABC-2001, ect?  As I do have the script working, but like I mentioned the script doesn't run if I import and you sorta get a slight delay.  Below is a link to where several members helped me get the script to work including you Bruce.

               

              https://fmdev.filemaker.com/message/103695#103695

               

              Thanks,

               

              Rob

              • 4. Re: Calculation not incrementing
                BruceHerbach

                Hi Rob,

                 

                Here is an updated Calc:

                 

                Let([

                $$ABC = "ABC-";  

                $$host = if( isempty($$host); 2000; $$host +1) ];

                If ( ID = "Staff"; $$ABC & $$host; ""))

                 

                Rob a variable is only available as long as the database is open.  If you close the data base or someone else opens it.  $$host will not exist until this calc is run for the first time.

                If you are using a script to import your data, after the import, the found set should be the records imported.  If you set the value for $$host before the import to the correct starting point.   You can add a Replace field contents script step at the end of the script that will update all of the imported records using the formula above.

                 

                Bruce