8 Replies Latest reply on Jun 22, 2010 11:57 AM by philmodjunk

    Need Help with Library Catalog Numbers

    CRHawk

      Title

      Need Help with Library Catalog Numbers

      Post

      Hello All,

      My Setup:  Mac OS 10.6.4, Filemaker 11, Novice, No Network...Solo Macbook

      I am a band director trying to set up a catalog system for our sheet music library that groups the music on the shelf for easy browsing.  I would like to use a numbering system for each entry based on the following criteria:

      1) Category (Concert, Marches, Christmas, Jazz Band)
      2) Grade (anything from 0.5 to 5.0 in increments of .5)
      3) A sequential 3 digit number within each Category and Grade

      I would like the catalog number to formatted as XX-00-000 whereas the first two letters are the first two letters of the category, the 2nd two numbers are the grade (multiplied x 10 to remove the decimal) and the last three digits is automatically generated after Category and Grade are selected, so that each piece within each grade & within each category starts at 001.

      For example:

      Concert Pieces at grade 0.5 would be catalogued CO-05-001, CO-05-002, etc.
      Concert Pieces at grade 1.0 would be catalogued CO-10-001, CO-10-002, etc.
      Marches at grade 0.5 would be catalogued for MA-05-001, MA-05-002, etc.

      So, if I want to look at all of the Grade 3 Christmas music, they’re all together on the shelf.  If I order a new grade 3 Christmas piece, the new catalog no. will be in sequence with the other Grade 3 Christmas pieces.

      I am new to Filemaker and have very limited database experience.  I did manage to create the following fields:

      Category (with drop down menu)
      Grade (with drop down menu)
      Serial  (Auto-enter Serial)
      Cat. No (my best attempt) = Category & "-" & Grade * 10 & "-" &  SerialIncrement("000" ; Serial)  

      This obviously did not abbreviate the Category, and the serial was essentially just a 3 digit counter, but I wanted you to know the extent of my ignorance before you reply,  This whole "Specify Calculation" language is kinking my butt.

      I appreciate any help and...type SLOWLY. :smileywink:

      Chad















        • 1. Re: Need Help with Library Catalog Numbers
          Mystick

          What I would do...

           

          in the relationship window, create another table occurrence of your table.

          Relate it to your current one, with field category = category and grade = grade.

           

          your catno field should be text and have its calculation inside the auto-enter value, so when you create one record, it automatically create the catno.  the field should not itself be a calculation.

           

          calculation would be :  Left(Category; 2) & "-" & Grade * 10 & "-" & right("000" & ( max( right(secondtableoccurrence::catno; 3)) + 1) ; 3)

          • 2. Re: Need Help with Library Catalog Numbers
            CRHawk

            Okay.  I have questions about where to enter the calculation, but let's make sure I didn't miss a step with the relationship.

            In the relationship window, I have "Table 1" with all of my fields listed.  I created an identical table which I labled "Table 2".  As I clicked Table 1 Category and drug to Table 2 Category a line formed between the two and it moved to the top of each table.  I did the same with grade.

             

            For the "CatNo" field,  I changed the type to Text and hit Options & was presented with the Auto Enter tab.  Now within this window, where do i put the calcuation? under Data, Calculated Value, Looked Up Value?

             

            and should it read as follows:

             

            Left(Category; 2) & "-" & Grade * 10 & "-" & right("000" & ( max( right(Table 2::CatNo; 3)) + 1) ; 3)

             

            I tried it in the Calculated Value window and recieved the following error:

             

            In the function Average, Count, Extend, GetRepetition, Max, Min, NPV, StDev, Sum, GetSummary or GetNthRecord, an expression was found where a field alone is needed.

             

            right(Table 2::CatNo; 3) was highlighted.

             

            • 3. Re: Need Help with Library Catalog Numbers
              CRHawk

              After two solid days of research, reading the pertinent chapters from "the missing manual" and searching every forum post related to this topic, I have just about blown a fuse.

              Can somebody please give me some more direction?

              • 4. Re: Need Help with Library Catalog Numbers
                philmodjunk

                You're putting the calculation in the right place. It just has a bug that needs to be fixed.

                 

                Define a calculation field set to return "Number" as CatSerial:  right ( catno; 3 )

                 

                Now modify your expression as: 

                 

                 

                Left(Category; 2) & "-" & Grade * 10 & "-" & right("000" & ( max( Table 2::CatSerial )) + 1) ; 3)

                 

                • 5. Re: Need Help with Library Catalog Numbers
                  CRHawk

                  Thanks for responding PhilModJunk!

                   

                  I tried your suggestion and FM has apparently taken issue with the next to last parenthesis and said 

                  "There are too few parameters in this function"

                  • 6. Re: Need Help with Library Catalog Numbers
                    philmodjunk

                    That's because I left in an extra ) and a harmless extra ( ).

                     

                    Edit it to be: Left(Category; 2) & "-" & Grade * 10 & "-" & right("000" &  max( Table 2::CatSerial ) + 1 ; 3)

                    • 7. Re: Need Help with Library Catalog Numbers
                      CRHawk

                      Wow! It Worked!  I know that doesn't come as a surprise to you, but it shocked the hell out of me.

                       

                      Thank You! Thank You! Thank You!

                       

                      Hopefully, after some analysis, I can understand how it worked. 

                      • 8. Re: Need Help with Library Catalog Numbers
                        philmodjunk

                        Here's how it works:

                         

                        The second relationship, via the table 2 relationship matches to all records with the same category. The maximum function returns the largest serial number value of this set of matching records. Adding one to this maximum gives you the next number in the series for this category.

                         

                        Final note: While this is a useful item to put on your music folder labels, I wouldn't use it as the link between tables in relationships. I'd use a separate auto-entered serial number field for that purpose.