1 2 Previous Next 15 Replies Latest reply on Dec 1, 2009 11:41 AM by kirvis

    Script to automatically enter new records in an overview table.

    kirvis

      Title

      Script to automatically enter new records in an overview table.

      Post

      Hi all,

       

      I have a relational database system which works quite well now. Within the system I have a few overview tables, which get their data from other tables through calculations. Every time I enter a new product in the products table, I can add processes in the processes table, components in the components table, etc. The overview table serves as a purpose to combine all the values from the different steps in the value chain (tables) and to create a nice overview.

       

      The only problem is that I currently have to enter the productID manually into the overview tables. It then automatically retrieves all the calculated values from the other tables. What I would like to so is the following: I would like to create a script that automatically enters new productIDs when I select the layout.

       

      The script would look something like this (triggered by selecting the layout):

       

      Create new record

      Go to field "productID"

      select productID value from value list "products" that is not present in this table yet and enter it

      commit record

       

      this script should loop itself until every new productID is entered once.

       

      Is this possible, and if yes, what would the script look like exactly?

       

      Kirvis 

       

      N.B. The value list "products" is generated from table:products, field: productID 

        • 1. Re: Script to automatically enter new records in an overview table.
          comment_1
             What exactly is the difference between your Products table (one record for each product) and the Overview table (ostensibly the same thing, after running the script)? IOW, why don't create the overview from the point-of-view of the Products table?
          • 2. Re: Script to automatically enter new records in an overview table.
            kirvis
              

            Because I have more than one overview table (CO2, energy use, waste output) with the different steps of the supply chain, and multiple input tables (components, packaging, processes). All these tables are linked via the end products table.

             

            Does that make it different? 

            • 3. Re: Script to automatically enter new records in an overview table.
              comment_1
                

              kirvis wrote:
              Does that make it different? 

              I don't know. If (as it seems) the purpose of an overview table is to summarize data from the children of a specific product, then my question still stands.

              • 4. Re: Script to automatically enter new records in an overview table.
                philmodjunk
                   It would seem that your "end products" is the overview table and your "overview" tables are really the detail tables. :smileywink:
                • 5. Re: Script to automatically enter new records in an overview table.
                  kirvis
                    

                  Maybe things become clearer when somebody else looks at the file. I have been working on this database for so long, that maybe I have developed a bit of a tunnel vision.

                   

                  I have put the file on my MobileMe account. You can find it here.

                   

                  The thing is that the table end products has more attributes than just the name and the ID. Eventually more things will have to be added, but the central "break field" of the database (so to speak) is the productID. 

                   

                  • 6. Re: Script to automatically enter new records in an overview table.
                    comment_1
                       The problem with your file is that it shows what you have done - not what you wanted to accomplish.
                    • 7. Re: Script to automatically enter new records in an overview table.
                      kirvis
                        

                      Well, I want to accomplish that the layouts named "output supply chain GHG", "output supply chain energy use" and "output per indicator" are filled automatically with new records, based on what I enter in the connected tables.

                       

                      I do think the underlying architecture of the system is good. The system does what I want it to do via the relationships I defined, but the workflow could become a bit more streamlined (regardless the lack of layouts, yet). That is why I would like to create a script that automatically fills the output tables when something is added in one of the other tables. 

                      • 8. Re: Script to automatically enter new records in an overview table.
                        comment_1
                          

                        kirvis wrote:

                        Well, I want to accomplish that the layouts named "output supply chain GHG", "output supply chain energy use" and "output per indicator" are filled automatically with new records, based on what I enter in the connected tables.


                        No. You are describing a means, not the purpose. I have the feeling that you could accomplish what you're after using only two tables:

                         

                        Products -< Parts

                         

                        where a part can be a component, a process or packaging. A report from the Parts table, summarized by product and type, would probably be sufficient - but this is merely a guess.


                        • 9. Re: Script to automatically enter new records in an overview table.
                          philmodjunk
                            

                          Feel free to ignore this post if it isn't helpful...

                           

                          Perhaps you can post a specific example of what you need to do and then the results you need to see on your computer screen.

                          • 10. Re: Script to automatically enter new records in an overview table.
                            kirvis
                              

                            Ok, I'll try..

                             

                            Suppose I do not have any entries yet and I want to enter a new end product. 

                             

                             

                            • I enter a new end product in the table "end products", named "chicken nuggets".

                            • I enter a few ingredients in the table "gate products", named "chicken breast", "flour", "herbes", and "salt". (Values for GHG etc are standardized per kg)
                            • In the table "connect_table_gate_products", I combine the right amount of ingredients with the end product chicken nuggets.

                            • I enter a few packaging materials in the table "packaging materials", named "cardboard" and "plastic". (Values for GHG etc are standardized per kg)
                            • In the table "connect_table_processes", I combine the right amount of packaging materials with the end product chicken nuggets.

                            • I enter a few processes in the table "processes", named "cutting", "seasoning", and "packaging". (Values for GHG etc are standardized per hour)
                            • In the table "connect_table_processes", I combine the right duration of processes with the end product chicken nuggets.

                            • With all those materials and processes linked to the end product, I can go into the overview table for let's say GHG (Green House Gas), fill in the end product ID, and Filemaker will magically pull all the calculated values from the underlying tables. This will also be true for the other overview tables.

                            • Finally, I can go to the "output per indicator" table, fill in the end productID and Filemaker will pull the sums of all six indicators (GHG etc) and put them in a row.
                             
                            That is the end goal of this project. To be able to combine a lot of data, and display a calculated end result based on what is needed to make in this case one kilo of chicken nuggets. I would like to be able to see all the output indicators (GHG etc) per supply chain step, and define which has the most impact. I also want to be able to add product categories (meat, dairy, etc) to be able to filter on category and see which category of products has the most impact.
                             
                            The only question I had at the beginning of this post was if it is possible to automatically populate the overview tables with newly added end products (end product IDs) so that I will not have to do that manually. This will save me a lot of time in the future once the number of end products starts growing.
                             
                            Cheers,
                             
                            Kirvis
                             
                            N.B. Off to bed now.. 

                             

                            • 11. Re: Script to automatically enter new records in an overview table.
                              comment_1
                                

                              kirvis wrote:

                              • With all those materials and processes linked to the end product, I can go into the overview table for let's say GHG (Green House Gas), fill in the end product ID, and Filemaker will magically pull all the calculated values from the underlying tables. This will also be true for the other overview tables.


                              With all those materials and processes linked to the end product, Filemaker can pull all the calculated values from the underlying tables directly into the product record. I still see no value in replicating the same table as several "overview tables" - all it does is create a problem of how to keep the tables synchronized (which is basically your original question). I believe you just need more layouts of the Products table.

                               

                               

                              And I still see no reason why gate products, packaging materials and processes couldn't all be in the same table.


                              • 12. Re: Script to automatically enter new records in an overview table.
                                philmodjunk
                                  

                                I agree with Comment.

                                 

                                You might want to investigate Portals if you haven't already. Your description reads like a "bottom up" data entry procedure. You appear to start with details and gradually build the top level overview. Starting from the top and working down, using portals to fill in related details should simplify the process greatly.

                                 

                                Also keep in mind that you can enter lots of data for different output indicators in the same table, but use a text field to identify which records are GHG and which refer to CO2. Then finds and relationships can be used to work with specific subsets of related records.

                                • 13. Re: Script to automatically enter new records in an overview table.
                                  kirvis
                                    

                                  Yesterday I finally got what the two of you mean. What I did now is the following:

                                   

                                  I pasted all the fields from the overview tables into the end products table. This is now a huge table with 70 fields. However, nobody ever gets to see that because I created a couple of layouts to display the indicator specific information, and the final overview fields. I did not choose to go with one field for each indicator value with a label, as PhilModJunk suggested, because that would complicate data entry quite a lot and I do not see a problem with a table with 70 fields yet.

                                   

                                  Getting rid of all the overview tables indeed completely eliminated the problem I had in the beginning of the topic. Thanks for that!

                                   

                                  About putting processes and packaging material and gate products in one table, I am still thinking of that. It is a good suggestion, but I am afraid that I will get in trouble with the difference between "physical inputs" and "processal inputs". Physical inputs, like gate products and packaging materials, are measured in output per kilo. Processal inputs however, will have to be measured in output per kilo per hour, which poses a problem when it comes to calculation of the product specific outputs. That is why I am currently thinking of using two tables for the input; one for physical inputs and non-physical inputs. Does that make sense?

                                   

                                  Cheers,

                                   

                                  Kirvis 

                                   

                                  • 14. Re: Script to automatically enter new records in an overview table.
                                    philmodjunk
                                      

                                    To place physical and non-physical inputs into the same table you can set up fields like this.

                                     

                                    Measurement: (Number)

                                    Units: (text with value list for two values: Per Kg; Per Kg/Hr)

                                     

                                    Now your calculations can use either a case or IF function to compute value differently based on the value selected for Units:

                                     

                                    Case ( Units = "Per Kg"; /* put Kg based calc here */ ; Units = "Per Kg/Hr" ; /* Put Kg  per Hr based calc here */ )

                                     

                                    There are a number of options for even more sophisticated "smart" computations possible if needed. There are even ways to store the calculation expression in a text field and use the Evaluate function to compute values differently for different records.

                                    1 2 Previous Next