7 Replies Latest reply on Dec 5, 2013 4:09 PM by PrisonProfessor

    Increment Counter based on value of another field

    PrisonProfessor

      Title

      Increment Counter based on value of another field

      Post

           I'm building a code for a single field which involves concatenating 6 fields. 4 of those fields are counters if you include the primary key field serial counter.

           Field 1 = Count field which is record Id and is configured to increment by 1 on creation of each new record

           Field 2 = A 4 character text code

           Field 3 - Count field 2  increments by 1 based on value of field 2

           Field 4 = A 3 character text code

           Field 5 - Count field 3 increments by 1 based on value of field 4

           Field 6 = Count field 4 increments by 1 based on value of both fields 2 & 4

           Field 2 currently has a list of 5 codes with the possibility of additional codes in the future

           Field 4 currently has a list of 51 codes with the possibility of additional codes in the future

           I'd also like to have the counters have either 3 or 4 leading zeros 

           I'm figuring some type of script for each count field with a bunch of nested 'if' statements and if so perhaps an example of 3 levels for each would get me going. Hopefully there is a simpler way but I'm clueless or wondering if forget automations and just do each count field  manually

           Thanks much

            

            

        • 1. Re: Increment Counter based on value of another field
          philmodjunk

               I really hope you aren't planning on using this field as a primary key in your database! Field 1 should be all that you need for that purpose.

               The key to producing the needed values will be some relationships. These can be self join relationships to a second occurrence of the same table or you can set up a table with one record for every unique value in field 2 and another table with one record for every unique value in Field 4 and a third for each unique combination of the two text codes.

               I'll stick with a set of self joins for now, but there could be advantages to you to use the added tables as a way to better manage the values used for these codes.

               Main|SameFild4>-----Main--<Main|SameFld2
                                                     |
                                                     ^
                                             Main|SameField24

               Main::Field2 = Main|SameFld2::Field2

               Main::Field4 = Main|SameFld4::Field4

               Main::Field2 = Main|SameFld24::Field2 AND
               Main::Field4 = Main|SameFld24::Field4

               You can use Max ( MainSameField2::Field3 ) + 1 to assign the next value in the series for that counter. The other two counters work the same way but reference different fields and table occurrences.

               And you can add leading zeroes like this Right ( "000" & Field 3 ; 4 )

          • 2. Re: Increment Counter based on value of another field
            PrisonProfessor

                 If your talking about the concatenated field when you reference not using it s key, No it will not be a key, but thanks for making note of it.

                 I think I've got an idea of what you saying may take me a few tries though and not sure when I'll get to but thanks as always for quick respsonse.

                  

                  

            • 3. Re: Increment Counter based on value of another field
              PrisonProfessor

                    Well I'm getting there but have 2 UGHs

                     
              •           I'm getting this error message "An operator (e.g. +, -, *, …) is expected here" when I attempt to concatenate leading zeros before the counter The script is as follows

              Max ( HistorySOCPMClasses 2::HSC_CourseCount ) + 1
                   Right ( "000"  & HistorySOCPMClasses 2::HSC_CourseCount; 4 )

                   I've tried making the calculated result field "HSC_CourseCount" both text and number and get same error message

                     
              •           The third counter which counts the number of times a course has been taught at a facility is not incrementing. I have the following joins

              HistorySOCPMClasses::PC_CourseCode joins HistorySOCPMCLasses 4::PC_CourseCode
                   HistorySOCPMClasses::PF_FacilityCode joins HistorySOCPMCLasses 4::PF_FacilityCode

                   The script for HistorySOCPMClasses::HSC_CourseFacilityCount is as follows

              Max ( HistorySOCPMClasses 4::HSC_SOCPMFacilityCount ) + 1  and 
              Max ( HistorySOCPMClasses 4::HSC_CourseFaclityCount )  + 1
                    
                   Thanks, I'm a bit slow on the upload but you've been a great help
              • 4. Re: Increment Counter based on value of another field
                philmodjunk

                Max ( HistorySOCPMClasses 2::HSC_CourseCount ) + 1
                     Right ( "000"  & HistorySOCPMClasses 2::HSC_CourseCount; 4 )

                     Isn't a valid expression and isn't how you should set this up.

                     Max ( HistorySOCPMClasses 2::HSC_CourseCount ) + 1

                     Should be used to assign a value to HistorySOCPMClasses::HSC_CourseCount  (note that there is no "2")

                     Right ( "000"  & HistorySOCPMClasses 2::HSC_CourseCount; 4 )

                     Should be part of the concatenation calculation that combines each of the codes and counters into a single field. (A concatenation expression uses the ampersand (&) operator to "glue together" each field or expression to produce a single string of text characters.)

                • 5. Re: Increment Counter based on value of another field
                  PrisonProfessor

                       Sorry about the deja vu post. Somehow or other when attempting a new post that one was posted a 2nd time.

                       OK, I got all three counters working, but still no luck with the concatenating of "000" before the  count fields.  This is what I currently have in 'Specify Calculation' resulting in all counters working.

                  HSC_CourseCount = 
                       Max ( HistorySOCPMClasses 2::HSC_CourseCount ) + 1

                  HSC_SOCPMFacilityCount = 
                       Max ( HistorySOCPMClasses 3::HSC_SOCPMFacilityCount ) + 1

                  HSC_CourseFacilityCount = 
                       Max ( HistorySOCPMClasses 4::HSC_CourseFaclityCount )  + 1

                       These are not working and causes counters to malfunction

                  HSC_CourseCount = 
                       Max ( HistorySOCPMClasses 2::HSC_CourseCount ) + 1 and
                       Right ( "000" & HistorySOCPMClass 2::HSC_CourseCount; 4)

                  HSC_CourseCount = 
                       Max ( HistorySOCPMClasses 2::HSC_CourseCount ) + 1 and
                       Right ( "000" & HSC_CourseCount; 4)

                        

                       Thanks

                  • 6. Re: Increment Counter based on value of another field
                    philmodjunk

                         To repeat from my last post. you do not combine the expressions used to increment the counters with the function for padding the number with leading zeroes. That is part of the calculation for combining the various field values into one long identifier. And you do not use AND in those calculations, you use the & operator.

                         In your expression for concatenating values to produce the final code, you would use stuff like this:

                         Field 1 & Field 2 & Right ( "000" & Field 3 ; 4 ) & ...

                    • 7. Re: Increment Counter based on value of another field
                      PrisonProfessor

                           Finally got it Phil cheeky

                           Sorry if I frustrated you. I initially interpreted the counter and leading zeros as two separate command lines and used "AND" to do so when I saw it used in your example for incrementing the dual counters.

                           I bought a Kindle copy of "Filemaker 12 Developers Reference: Functions, Scripts, Commands and Grammars" hopefully iy will help reduce my posts

                           I do greatly appreciate your help thanks