5 Replies Latest reply on Sep 1, 2016 7:24 PM by philmodjunk

    Creating records in a table based on another table list

    MartinBishop

      I have a join table of skus which encompasses items which have different attributes(sizes).

      When assigning a new attribute from a join table, I would like a script to check if attribute already exist in the other table and ignore or copy the attributeid and parentid into other table. For example I might have 10 items with attribute MD, but I only want to copy that MD once. This way I can have another table of measurements that is tied to the Parent Table.

      Hope that makes sense.

        • 1. Re: Creating records in a table based on another table list
          philmodjunk

          If your relationship includes the attribute as one of the match field pairs, multiple instances of the same attribute will match to just one record in your related table of attributes and thus no duplicates in that table will be created.

          • 3. Re: Creating records in a table based on another table list
            philmodjunk

            The relationship between SKu and Attribute would match on two pairs of match fields instead of the usual 1. Given your example, you'd match on color and attribute so that for any given color, you'd only get one instance of each attribute.

             

            Some details are missing from your image.

             

            By what values will you match to link "Parent" to "Item"? There may need to be a modification of my general suggestion depending on what you use and whether you want a single instance of each attribute for each parent or a single attribute for each color in item.

            • 4. Re: Creating records in a table based on another table list
              MartinBishop

              I am using a selector script to assign sizes to an item though a sku join table.

              In the sku table you can see that the parent is the same, but with 2 items and 2 of each size.

              My goal is to create another record in a Chart table that is related to the Parent, but only 1 per Size.

              So if all items within a parent has 5 SM, 5MD, etc... This other table would show 1SM and 1MD record.

              This way I can encompass specifications that apply to all those specific sizes. How can I check if the size already exist to that parent and ignore or create new during this script? Hope that makes sense :-)

              image.jpg

              • 5. Re: Creating records in a table based on another table list
                philmodjunk

                So any given combination of ParentID  and attribute must be unique, correct?

                 

                If so, then the main problem is that ParentID and Attribute are values stored in two different tables.

                 

                A batch method for creating this table is to define a text field with this auto-enter calculation:

                 

                ParentID & "|" & Attribute

                 

                Then specify "unique values, validate always" for this field on the validation tab. You can then use import records to import this data and the validation rule will automatically prevent duplicates from being imported. A calculation field in SKU can copy the Parent ID so that both values can be imported from SKU.

                 

                On a more dynamic, record by record basis, you can define that same calculation field to copy the Parent ID into SKU and then use this cParentID field with Attribute to match to the parentID and attribute fields of your assigned attributes table.

                 

                SKU::Attribute = AssignedAttributes::Attribute AND
                SKU::cParentID = AssignedAttributes::ParentID

                 

                With allow creation enabled, a single set field step executed from SKU can create a new record in AssignedAttributes, but only if a matching record does not already exist.