8 Replies Latest reply on Nov 9, 2012 10:44 AM by laguna92651

    Automatically Generating Records

    laguna92651

      If I had a new product with a base Product No of say DM12021, is there a way to automatically create six identical product records be added to an inventory list, except each record would have a different entry in its size field, 2, 4, 6, 8, 10 & 12 and the size entry would be concatenated to the base Product No.

       

      Ideally I would like to select a pre-determined sub-set of default values (maybe check or uncheck sizes to inculde in the generation). The default values could also be changed while making checkmark selections.

        • 1. Re: Automatically Generating Records
          filemaker_mmfg

          You could create a new table containing as many records as your default values, then create a script in the main database that duplicates the record as many times as the checked records in the new table and writing each time the value from the table to the desired field.

          • 2. Re: Automatically Generating Records
            laguna92651

            Thanks for the reply, I am brand new to FM 12 and to scripting. Can you give me a little more detail on how to go about this, I understand your basic suggestion. I assume I need to create some sort of batch operation that cycles through the template table, does some updating to the new record based on the values in the template table and then writes the new record, the batch operation would stop when all checked template records have been gone through. Unfortunately I don't have a clue how to do this in FM 12. Can you point me to some info on doing this. Thanks

            • 3. Re: Automatically Generating Records
              filemaker_mmfg

              I enclose some picture from a sample database basically doing what you want.

              Please note that there are two tables: the main_table and the size_table.

              There is a relationship between the line field in the main_table and the line field in the size_table.

              In the main_table the size_suffix is a lookup of the size in size_table based on the relationship.

              Activating the script "duplicate" in the main table, the selected record is duplicated and takes the size values existing in the size_table.

              The last picture is the result after running the script.

              Try to rebuild everything in your own database.

               

              Screen shot 2012-10-18 at 10.08.21.png

              Screen shot 2012-10-18 at 10.08.37.png

               

              Screen shot 2012-10-18 at 10.21.52.png

              Screen shot 2012-10-18 at 10.23.39.png

              Screen shot 2012-10-18 at 10.09.04.png

              Screen shot 2012-10-18 at 10.09.25.png

              • 4. Re: Automatically Generating Records
                laguna92651

                I got things running, except I'm missing something, the Main_table doesn't link to the Size_table, so I don't get the Size_Suffix in the newly created records. I need to automatically create line numbers in the Main_Table to be able to link to the Size_Table.

                Main Table.jpg

                 

                This is my Lookup dialog box.

                 

                Lookup Dialog.jpg

                 

                The script was a good little exercise for me, new to scripting. Question with the FoundCount, I had to type FoundCount into the Get() function, should the FoundCount be selectable from a list somewhere?

                 

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

                How would things change, if I added a check box to the Size_Table and only wanted to replicate the the checked items.

                Size Table.jpg

                 

                1. How do I get rid of the x next to the checkbox?

                2. Is there a way to center align the columns in task view?

                 

                Thanks for your help, Filemaker looks very flexible.

                • 5. Re: Automatically Generating Records
                  filemaker_mmfg

                  Q:

                  I got things running, except I'm missing something, the Main_table doesn't link to the Size_table, so I don't get the Size_Suffix in the newly created records. I need to automatically create line numbers in the Main_Table to be able to link to the Size_Table.

                   

                  A:

                  In the main_table one product is one record.

                  The script duplicates that record many times through a loop.

                  The script steps

                            Set Variable [$line; Value:$counter+1]

                            Set Field [main_table::line; $line]

                  set the value of the field line in every duplicated record.

                  When the field line has its value, then the size_suffix takes the value from the relationship.

                   

                  Q:

                  The script was a good little exercise for me, new to scripting. Question with the FoundCount, I had to type FoundCount into the Get() function, should the FoundCount be selectable from a list somewhere?

                   

                  A:

                  Get(FoundCount) is selectable in the functions popup, under Get functions group

                   

                  Q:

                  How would things change, if I added a check box to the Size_Table and only wanted to replicate the the checked items.

                   

                  A:

                  Using the checkbox to choose the desired sizes, you have to change the script, doing a find of selected sizes and then use not a simple counter variable, but a reference based on another relationship. My script was only a simple example to give you a guideline, but for working well it is necessary to re-write it in a robust and complete way.

                   

                  Q:

                  1. How do I get rid of the x next to the checkbox?

                   

                  A:

                  This can be accomplished by a more complex script

                   

                  Q:

                  2. Is there a way to center align the columns in task view?

                   

                  A:

                  In the Layout ode, you can select the field and change the text alignment in the Format menu

                   

                  Q:

                  Thanks for your help, Filemaker looks very flexible.

                   

                  A:

                  My advice is to practice more and more. Because FM is very intuitive, you will be able to build your own solution in a short time, improving it step by step.

                  • 6. Re: Automatically Generating Records
                    laguna92651

                    Thanks for the info. My problem was with the following line.

                     

                    • Set Field [main_table::line; $line]

                     

                    I used the repetition option instead of Calculated result and got.

                     

                    • Set Field [main_table::line[$line]]

                     

                    All is good now.

                     

                    Is it possible to use a left align field in Form View and use a center alignment on the same field in the Table View?

                     

                    Thanks Again

                    • 7. Re: Automatically Generating Records
                      filemaker_mmfg

                      Q:

                      Is it possible to use a left align field in Form View and use a center alignment on the same field in the Table View?

                       

                      A:

                      Not possible. Instead of two different views, you have to use two different layout, with different alignment settings.

                      • 8. Re: Automatically Generating Records
                        laguna92651

                        Follow up question on the generate records app. What changes do I need to make so a record is not created (duplicate record), if the record already exists. The records created would be of the form;

                         

                        DM12013-2

                        DM12013-4

                        DM12013-6

                        DM12013-8

                         

                        If DM12013-2 already exists I do not want to create a second copy of it. I would not want an error message, but the script to just go ahead and create the remaining records.

                        Thanks