5 Replies Latest reply on Jan 28, 2016 6:43 AM by BillisSaved

    Creating a 'Sets' system


      I need help creating an incremental set Id  for my inventory system. I am terrible at explaining this so I have attached below a screenshot of my current database to help explain.

      In the Set ID section I would like to create an individual set Id to link items which are part of the same set . At the moment this is entered manually. and I have to search back to find the last set Id used.


      I would like to create a system by which I press the button shown to create a new Set Id automatically 1 tot he last one used. However I would like to also be able to add Items to the previous set used.  For example if i book on 3 new items I create a new set Id on the first item and then on the next two just click add to set.

      I would also like to add items to the on call set and the Malvern set id with a button click.


      I had attempted to do this by creating a separate sets table with two fields. kf_ID _Inventory and K_Set_Id. I set the Set Id as an incremental serial number and though scripting have managed to get the buttons to do as I want . However because of the nature of the serial numbers there is a huge jump in set id numbers.

      i.e. if i create set 1 and then add 10 items to it and then try to create a new set that set umber is set as 11 not 2 as I would like.

      What I am asking is is there something really easy I have missed on . I'm a bit in the weeds with this so any help would be great.!

        • 1. Re: Creating a 'Sets' system

          you need to manually set a number since auto-enter will not work for you.


          Unfortunately, filemaker 11 doesn't have ExecuteSQL(), which would make the calculation very easy.


          For 11, you'll need to write a script that finds for the last set, gets the value, adds one to it, and then makes the new record.



          Enter Find Mode

          Set Field [ table::setNumber ; "*" ]

          Perform Find

          Sort Records [ table::setNumber ; Asc ]

          Go To Record [ Last ]

          Set Variable [ $set ; table::setNumber ]

          New Record

          Set Field [ table::setNumber ; $set + 1 ]

          This would allow you to only increment the set number when you want via a script, and not as an auto-entry.


          For kicks, here's how easy it is in FM14:

          New Record

          Set Field [ table::setNumber ; ExecuteSQL(SELECT MAX(setNumber) FROM table";"";"") + 1 ]

          • 2. Re: Creating a 'Sets' system

            Life would be easier with a third table, "SetComponents". 

            The single Set has a PK, a name, a creation date, a purpose and that's about it. It's linked to n SetComponents, where a SetComponent has a PK, a FK_Set and a FK_Inventory. You alter a set's composition by adding SetComponents. A new Set will increment correctly his PK and will be ready to have Inventory items added to it, via a record in the SetComponents.

            • 3. Re: Creating a 'Sets' system

              Good morning FM_DM,


              I hope your day is going well. I lack Mike's experience, and I'm sure his method would work, but I was wondering if this might do what you need:


              1. Create a table with 3 fields (i.e. primary key, serialized number, and description)
              2. Use a Cartesian - cross join - to relate it to your existing table
              3. Create a value list based on the serialized number field
              4. Add a drop-down list to your existing table, which would reference the value list you just created, enabling to select the desired ID number.


              I'm sure there are more elegant solutions you could implement to meet your needs, but we're all learning. Have a great day!


              God bless,




              • 4. Re: Creating a 'Sets' system

                Thanks Bill

                This worked great for what I need!. Its a much simpler solution than my own.

                • 5. Re: Creating a 'Sets' system

                  Good morning FM_DM,


                  Excellent! I'm glad you were able to get what you need. Have a great day!


                  God bless,