6 Replies Latest reply on Mar 30, 2009 8:13 AM by Sewlong

    Assign a series number



      Assign a series number


      I have 2 tables/layouts

      "Orders" & "Projects" (items) I have assigned a unique record ID for both (for reference and relationships)

      The "Projects" show up as individual lines in a portal in my "Orders" layout

      What I would like to know is how do I automatically assign a number so that every time I create/add a new "Order" and then add a new "Project" I would like a field in the "project" table/layout that assigns a new number starting with #1 for each new order? I.e... Order #356, has Projects 1-3 - Order #357 has Projects #1-7, etc.....


      Windows Vista, Filemaker pro V9


      Thanks in advance!

        • 1. Re: Assign a series number

          You can do this with a special type of relationship we call a "self Join". The method I'm describing should work well for modest numbers of Project records for a given Order.


          Use Manage Database|Relationships to create a self join linking the Project table to itself by your Order number field and name the new Table Instance "SameOrder"

          Click and drag OrderNumber in Projects out away from the table and then drag back to the same field.

          A Table Instance box will appear, enter "SameOrder" to name it.

          Define a new number field in Projects called ProjectNumber.


          Create a script: "New Project"


          Commit Records

          Go To Portal Row [Last]

          Set Field (Projects:: ProjectNumber, Max(SameOrder:: ProjectNumber) + 1


          If you had FMP 10 I'd suggest a script trigger that uses commit record on your portal records and leave out the Go To Portal Row step. Since you don't, create a button on your layout and name it "New Project". Set it to perform the script "New Project" when it's clicked.


          I realize that this solution creates new problems. You'll need to find a way to keep users from creating new records without clicking the button. It can be done, but the best approach depends a bit on your layout design. (How many fields besides Project name and number do you have in your portal that require the user to type in data?.


          I tried to get auto enter calculation to put this number in place but it didn't work so you wouldn't have those problems.


          Perhaps another poster can figure out a way to make that work.

          • 2. Re: Assign a series number

            Is there any way to do it with out a script? i.e. some sort of calculation?

            Because the way i add a new "Project" is by simply chosing the "Project/Project_ID" from a drop down list in the portal?


            I imagine the calculation would be somthing like (Get the number of related projects + 1) or get found count, etc....


            Any thoughts?


            Again, thank you for your reply!

            • 3. Re: Assign a series number

              That's why I tried to use the auto-enter option. Couldn't get it to work for you.


              A calculation, if you could get it to work, would give all the records the same number since "get all the related projects", (that's the Max() function in my example), would be the same value for all records in your portal.



              • 4. Re: Assign a series number


                Can I make a new field (calculation of found count of related projects?)  on my orders table? Then in the projects table/Field +1 to that?

                • 5. Re: Assign a series number



                  Tweaking your idea a little:


                  Define a calculation field, NextProject as If(Count(Projects:: OrderNumber)=0;1;Count(Projects:: OrderNumber)) in your Orders table.

                  Uncheck the Do not evaluate if all fields are empty check box.

                  Click OK


                  Now set the auto-enter option for Projects:: ProjectNumber to auto-enter the following calculation:




                  No scripts, No self-join relationships needed!


                  Sorry it took me so long to reply, Our internet router started acting up this week. This is the fourth attempt to post this information since Friday.

                  • 6. Re: Assign a series number

                     no problem in the delay,

                    I will give this a shot this afternoon and let you know how it works.

                    THANKS again for your help!