6 Replies Latest reply on Feb 4, 2013 4:28 PM by jimb01

    How do I "empty" the fields on a Layout????


      I'm trying to copy a master list of things into a new table for specific customers. Once copied, the list can be altered for the individual client. I have a script that does something like this:


      Go to layout["masterlist" (masterselection items)]

      Preform Find[Restore]

      Go to Layout["Customer Selections" (table Customer Selections)]

      Import Records [No Dialog; "The file name";Add;Mac Roman)

      Replace Field Contents[No Dialog;table Customer Selections::CustomerID]




      So the very first time that I run the script it works fine. However, the second time, when the script get to the second layout, the fields are still filled with values from the last time through the script. When the "Replace Field Contents" script step runs the table ends up with two sets of data.


      If I run the script again, the table ends up with three sets of data.


      So how do I flush, clear, empty, reinitialize, the fields in the second layout so that I don't end up with multiple data sets??





        • 1. Re: How do I "empty" the fields on a Layout????

          Steve -


          I'm assuming you're using a "scratch" table, one used to hold temporary records for scripting. If that's the case, simply add these two script steps to your script after the "Go to Layout [ "Customer Selections" ]" step:


          Show All Records

          Delete All Records


          If you want to preserve the records in that table, though, then that's the wrong answer. In that case, more information about exactly what you're trying to accomplish is needed.   



          • 2. Re: How do I "empty" the fields on a Layout????

            Nope not a scratch table for scripting.


            I have a "master table" that holds a list of items that are all associated with categories via a key into the categories table. My intention is to copy the data in the master list into a table associated with customers. I can then add "selections" to the new customer list. So each customer can have a copy of the master list with selections. I have chosen to "import" the item list even though I now have dublicate lists in the file because I need to also edit each customer list, deleting some items and adding others without effecting the "master" list.


            So initially it looks like this:


            master list =  Category -----> Item List


            After import I want it to look like this


            customer list = category ----> Item list ------> selections list


            With custom item list edits and selection list edits.



            Is this enough information to answer the question or do you need more?


            Thanks for your help



            • 3. Re: How do I "empty" the fields on a Layout????

              So you want a set of records corresponding to each customer and each category, but you want to "flush" the records associated with each customer?


              I don't know what you mean by "selections". Do you mean there are fields in this list that each customer can edit? Or do you mean each customer only keeps a subset of the records involved?



              • 4. Re: How do I "empty" the fields on a Layout????

                No - perhaps I misled you.

                There are three fields in the list, a Category, an item, and a selection. There are many categories, many items and many selections but each selection is related to only one item and each item is related to only one category. Each customer's list has many categories. For example:


                John's list


                          Category "Books"


                                    Items "Books by Manny"


                                                   Seleection "Manny Title one"

                                                   Selection "Manny title two"



                                    Items "Books by Chris"


                                                   Selection "Chris Title one"

                                                   Selection "Chris Title two"


                                    Items "Books by Wendy"




                I need to print the entire list for each customer plus edit any of the three fields for each customer without effecting the "Master" list. So I want to change Selection "Chris Title one" to Selection "Chris Title ninety-nine"


                Initally the list is populated by copying the master list to a new table with the customer's ID key. My problem occurs when I want to create a new customer list - I get two entries instread of only one. So I get:


                Category Books


                               Item: Books by Chris


                                         Selection Chris Title one

                                         Selection Chris Title one

                                         Selection Chris Title two

                                         Selection Chris Title two



                If I try and create another new list for another customer, I get each entry three times. I only want it once in each customer file.


                Thanks steve

                • 5. Re: How do I "empty" the fields on a Layout????

                  I think I'm beginning to see what you're trying to do. But I'm not sure you actually have a problem.


                  If you have "Chris Selection 1" for customer A, and "Chris Selection 1" for customer B, those are, in fact, unique records. They're not duplicates. The problem isn't that you have too many records; the problem is the way the records are configured. You have a data modeling challenge.


                  You have what's known as a "many-to-many" join. A single selection can be associated with many customers, and a single customer can be associated with many selections. Hence, you have "many" customers associated with "many" selections. The way to implement this would be with a join table. Such a table contains, at a minimum, the unique keys for the selection and the customer. Often, that's all it contains. But the kicker is this: You create a record for each COMBINATION of customer and selection. That's what's unique.


                  If you have information that's unique to the combination of customer and selection (like, say, an order number or a checkout date or whatever), you can put that on the join record. Then, you can refer back to the parent record (either selection or customer) to get the information about that entity. If you put a portal on either one, you'll see only the information you need to.


                  I hope that makes sense.



                  • 6. Re: How do I "empty" the fields on a Layout????

                    If you have trouble reading this jpg, I can send you a larger copy.


                    The relational DB model eliminates the need to duplicate selected items for each customer and the problems associated with redundant data.

                    It will require understanding and using the relationships tab under Manage DB, and also the use of "Portals" to display "Child" records on a "Parent" based Layout,and/or the use of "Fields - from a "related-record" Parent.

                    Layouts based on Table 2 - can show all those customers that have selected specific Items as well as information about the Items Selected.


                    this is a very powerful concept and worth understanding.



                    Model of relational DB example.jpg