3 Replies Latest reply on Mar 3, 2013 2:23 PM by petermontague1

    How to set up a new category layout

    petermontague1

      I have categories separated by carriage returns in one field (category) in the category table. I can see this through a portal in my inventory table. The groups of categories are repeated in various combinations throughout the inventory. In all though there are only about 300 categories.

      category-category.jpg

      I would like to change the set up so that I can have only 300 (or whatever the exact number of categories is) categories, each in its own record in a new category table. I would like my portal in inventory to look at the various different category records that are linked to it. So my plan was to set up a temporary category table; export each of the values from the category fields into separate records; delete the duplicates from this new set of records; make this new set of individual categories join to the inventory with a join table in between. Is this the right idea?

      relationships.jpg

      My first step is to make a new record for each of the values I have in all the current category fields. I want to make a looping script. It goes something like this:

      Loop

      Loop

      Go to Layout [ temporary category ]

      Set Variable [ $V; Value:Get (RecordNumber) ]

      Exit Loop If [ $V > ValueCount ( Category::category )]

      New Record/Request

      Set Field [ temporary category::category ; LeftValues (Category::category ; Get ( RecordNumber ) ) ]

      Set Field [ temporary category::serial ; Category::serial ]

      End Loop

      Go to Layout [ Category ]

      Go to Record/RequestPage [Next; Exit after last]

      End Loop

      I tried getting this script to run. But it is not working when I get to the set field step. It is returning blank records to me. No error messages are showing in the script debugger. I substituted the calculated value with "1" and this worked. This suggests that maybe there is something wrong with the relationships which I set up between the table occurrences. Here is a picure of the relationships. The checkbox is ticked in the temporary category table to allow creation or records.

        • 1. Re: How to set up a new category layout
          comment

          When you create a new record in "temporary category", it is empty at first - therefore not related to any record in any other table. Therefore, the expression Category::category is empty at this point. In addition, your use of Get ( RecordNumber ) makes little sense to me.

           

          I am somewhat overwhelmed by your graph, so I can only guess you want to do something like:

           

          Go to Layout [ Category ]

          Show All Records

          Go to Record [ First ]

          Loop

              Set Variable [ $categories ; Category::category ]

              Go to Layout [ temporary category ]

              Set Variable [ $i ; "" ]

              Loop

                  Set Variable [ $i ; $i + 1 ]

                  Exit Loop If [ $i > ValueCount ( $categories )]

                  New Record/Request

                  Set Field [ temporary category::category ; GetValue ( $categories ; $i ) ]

              End Loop

              Go to Layout [ Category ]

              Go to Record [Next; Exit after last]

          End Loop

          • 2. Re: How to set up a new category layout
            petermontague1

            I tried that yesterday Michael and it worked with some adaptation. I would love to use a set field script to fill in temporary category::serial field. But this is the key field. How does one set field a key field?

            • 3. Re: How to set up a new category layout
              petermontague1

              Hi Michael,

              I adapted your script a while ago and only got around to testing out how it went. Unfortunately many of the categories did not copy over. Or if they did many of the matching product_ids did not. Here is a copy of my script. Can you identify any faults in it?

              Make individual categories in temporary category.jpg