13 Replies Latest reply on Feb 1, 2012 2:25 PM by jbrad1968

    Help with a format...

    jbrad1968

      I have a database with a field named "CaseNumber" and I need it to format it like GC11-12-001 This represents "GC" as my county abbreviation and "11" as the year (2011) the "12" is the month (Dec.) and the "001" is the first case of that month. I need the field to increase by one for every new record. ie: GC11-12-001, GC11-12-002, and so on...And start over when the next month starts. But when the year changes the "GC11" needs to change to "GC12" and when the month changes I need it to change from "GC12-01-xxx" to "GC12-02-xxx" when it becomes Feburary in 2012. I cannot for the life of me figure out how to make FM pro 10 do this. I would appreciate any and all help...

       

      Example: In January of 2012 it should start GC12-01-001 then the next record would be GC12-01-002 and so on until Feburary then it should go to GC12-02-001...

       

      It would be the greatest if i could just type 001 in the field and it format it when you tab out....

       

      Thanks for any help....

        • 1. Re: Help with a format...
          ErikWegweiser

          Hi, jbrad:

           

          Sorry I don't have an immediate answer for you. Not yet. I got stuck with the question of county:

           

          - Does this database contain records for cases belonging to counties other than "CG?" If so, then CaseNumber would contain records beginning with a different county code.

          - Also if so, does each county require its own increment (001, 002...) within a given month. In other words, with counties CG and XY in Jan, 2012, would your CaseNumbers be

           

          A) CG12-01-001, CG12-01-002, CG12-01-003, XY12-01-001, CG12-01-004, XY12-01-002...

          or

          B) CG12-01-001, CG12-01-002, CG12-01-003, XY12-01-004, CG12-01-005, XY12-01-006...

          1 of 1 people found this helpful
          • 2. Re: Help with a format...
            jbrad1968

            Erik thanks for your quick response, the data will be only for Grayson County only there wont be any other counties, so it being "GC" all the time will be great..ie:

             

            GC12-01-001, GC12-01-002, GC12-01-003, GC12-01-004, GC12-01-005 and so on..Then Feb. the 1st it will go to GC12-02-001, GC12-02-002 and so on but i didnt want to confuse you with the last three numbers, they do not  represent days of the month. They represent record numbers. You probably already knew that but my post sounded a little confusing so i wanted to clarify..Thanks a ton.....

             

            jbrad

            • 3. Re: Help with a format...
              ErikWegweiser

              OK, so the county code is of no consequence here, and yes, it was clear the sequence numbers are not related to day of the month.

               

              My idea:

               

              In addition to the CaseNumber field, you likely (should) have a unique ID field that auto-increments. Good. Keep that, but also add a second auto-incrementing number field (we'll call it "Incrementer") for the purpose of determining the last three digits of CaseNumber. The basic concept is that it starts at 1 and every month is reset to 1, with CaseNumber being a non-modifyable auto-entry calculation, akin to

               

              "GC" & yearnumber_as_text & "-" & monthnumber_as_text & "-" & Incrementer_as_text

              ( "as_text" in this pseudo-code being shorthand for the number transformed via calculation into text with leading zeros)

               

              If your solution is server-based, you would be best to run a script on the server to Set Next Serial Number( Incrementer ) back to 1. The script would probably be set to run every day at 00:01, and check to see if "today" is the first of the month. If so, reset the serial number. This is so Incrementer is reset before anyone creates a new record the first day of the month.

               

              I'm inclined to use a "real" serial number field like this so FileMaker reliably creates the serial numbers upon record creation. Other solutions might involve global fields, relationships, scripts and more complex calculations to test every time a record is created what the next serial number for the month should be. My main concern is that with a multi-user solution, one needs to be very careful when using that method of incrementing that two records created at or about the same time aren't given the same calculated result, due to any lag time or other multi-user issues.

               

              Helpful?

               

               

              ------------------------------------------------

              Erik Wegweiser

              Intelligent Database

              Boston, MA

              http://intelligentdb.com

              1 of 1 people found this helpful
              • 4. Re: Help with a format...
                jbrad1968

                Erik, I just got your idea and it sounds good, and makes sense...its late tonight so i will implement it tommorrow and see what happens, I guess the only way to check ahead on this is to set my computer clock up to feb. and put a record or two in and see what happens....Thanks so much for the guidance...I have one more question i think you can answer and i will ask when i implement this one...and reply.....Thanks again,

                 

                Joe Brad

                • 5. Re: Help with a format...
                  jbrad1968

                  I am still workin on your solution but let me ask another question...I am trying to put record data on an avery label layout which is no problem, what i am having trouble with is when i enter a record i then need to put the info on a label for filing. the script puts the current records info on the upper left label, so if i print that on a page of avery labels then peel it off and put the label on a file folder the next record i enter at a later time trys to put the information in the same place on the label sheet, well that label is gone where i have removed it and placed it on a folder. there is 30 labels on a sheet, I need a dialog box to ask which label to place the information on and i guess give each label a number for fm to reference to, I have included two pics of what im talkin about, it would be fine if i was printing 30 labels at a time but im having to reuse the label sheet after they are removed one at a time. I hope i have explained what im talking about. you will notice in the picture that the information on the first pic is in one position then the other pic the inforrmation is in a different spot and i dont know how to make that happen...If you can help that would be great and i will let you know on the other solution.....If you dont completly understand i will try to make my self more clear....Thanks

                   

                  I thought if you could give each label a number then a dialog box pop up and ask what label number you would like to print on, then it would know what position on the label layout to put the data for print would be the best but i dont know if fm10 pro  will do that.....

                  ScreenHunter_11 Dec. 21 11.13.jpg

                   

                   

                  ScreenHunter_12 Dec. 21 11.13.jpg

                  • 6. Re: Help with a format...
                    Mike_Mitchell

                    Hello jbrad,

                     

                    In order to understand how this works, you'll need to grasp that each "label", in this case, is a FileMaker record. So, here's the trick:

                     

                    Create some number of records that equals the position of the label you want to print, minus one. Use whatever sort method necessary to place your desired record at the end of that set, then print. The blank records will create dead space that will push the desired record to the correct spot.

                     

                    HTH

                     

                    Mike

                    • 7. Re: Help with a format...
                      sporobolus

                      on 2011-12-21 10:46 jbrad1968 wrote

                      there is 30 labels on a sheet, I need a dialog box to ask which label to place the information on and i guess give each label a number for fm to reference to, I have included two pics of what im talkin about, it would be fine if i was printing 30 labels at a time but im having to reuse the label sheet after they are removed one at a time. I hope i have explained what im talking about. you will notice in the picture that the information on the first pic is in one position then the other pic the inforrmation is in a different spot and i dont know how to make that happen...If you can help that would be great and i will let you know on the other solution.....If you dont completly understand i will try to make my self more clear....Thanks

                       

                       

                      we had a discussion titled "printing on labels" with several solutions to this

                      problem in August; it's hard to work through a thread in the archives, but this

                      search URL should give you most of the posts:

                       

                      <https://fmdev.filemaker.com/search.jspa?peopleEnabled=true&userID=&containerType=&container=&spotlight=true&q=printingonlabels

                      • 8. Re: Help with a format...
                        jbrad1968

                        Erik I have tried every possible way to accomplish your case number solution...Is there any way you could write the syntax of the calculations and the scripts or provide step by step for me...Maybe in Filemaker for dummies format....lol...:-)...I am i guess just disgusted with it and have tried everything to get it to work properly...Would appreciate any help...Thanks

                        JBrad

                        • 9. Re: Help with a format...
                          aljeffrey

                          HI jbrad!

                           

                          I made a little file that will print lables, to an Avery 8660 (the Avery 30 up code in Canada), Sheet. Also prints to 10 up sheet, Avery 5163. You export your addresses to a tab file and call a script in my file. My file asks which label at which you wish to begin and prints the labels.

                          I've been using it for several years with no trouble.

                          Would you or asnyone else like a copy?

                           

                          Al Jeffrey

                          • 10. Re: Help with a format...
                            jbrad1968

                            Yes I would love a copy, If you dont mind....That would be great ...Thanks..

                            You can email if you would rather   jbrad1968@gmail.com

                            Thanks in advance

                            • 12. Re: Help with a format...
                              jbrad1968

                              If you sent the label program I didnt get it.....Just to let you know...If you dont care would you please resend...I seen you had replied after i told you i would like to have it...but i didnt see an attachment or file with the reply and i didnt see anything in my email....

                              Thanks

                              JBrad

                              • 13. Re: Help with a format...
                                jbrad1968

                                I didnt know if you ever sent the file you were talking about or did you attach it to a post somewhere in this forum.... Let me know if you dont care...