11 Replies Latest reply on May 15, 2012 2:05 PM by philmodjunk

    Help with a script step to remove the ID# from a list

    MarcMcCall

      Title

      Help with a script step to remove the ID# from a list

      Post

      hey guys,

       

      I have a script that moves a list of data from a global field to a local field on my T15_Products, so that I can use it in a diminishing value list that is related to that record only.  The list contains Id numbers that I collected in a search and entered into this global field.  Then when I goto my data entry layout to start to relate the products I have a button set up that moves the list to a field in that record.  What I want to do, is add a step in the script, that Identifies the ID_Product of the current record, and removes that number from the list, so I do not have to manually go through and remove it, or have to keep an eye out for it while I'm creating the related records.

        • 1. Re: Help with a script step to remove the ID# from a list
          philmodjunk

          Set Field [ YourTable::ListOfValuesField ; Substitute ( ¶ & YourTable::ListOfValuesField & ¶ ; ¶ & T15_Products::ID_Product & ¶ ; ¶ ) ]
          Set Field [ YourTable::ListOfValuesField ; Middle ( YourTable::ListOfValuesField ; 2 ; Length ( YourTable::ListOfValuesField ) - 2 ) ]

          The second step strips off the extra ¶'s added in the first step. It's also possible to do all of this in a single script step:
          Use this as the second parameter:

          Let ( [Lista = ¶ & YourTable::ListOfValuesField & ¶ ;
                   Listb = Substitute ( Lista ; ¶ & T15_Products::ID_Product & ¶ ; ¶ ) ] ;
                   Middle ( Listb ; 2 ; Length ( Listb ) - 2 )
                )

          • 2. Re: Help with a script step to remove the ID# from a list
            MarcMcCall

            It's working great with just the first part, except it's removing the first digit of the first line of data also.  It removes the Id # but also turns 8160 into 160 which is the very first vale of the list.

             

            I'm not sure what you ment by:

            Use this as the second parameter:

            Let ( [Lista = ¶ & YourTable::ListOfValuesField & ¶ ;
                     Listb = Substitute ( Lista ; ¶ & T15_Products::ID_Product & ¶ ; ¶ ) ] ;
                     Middle ( Listb ; 2 ; Length ( Listb ) - 2 )

            )

            • 3. Re: Help with a script step to remove the ID# from a list
              philmodjunk

              Set Field has two paramters. The target field and the calculated result. They are separated by the ; delimitter. That calculation was an afterthought that combines the two operations into a single calculation so that this all takes place in a single set field step should you use that version.

              Set Field [ YourTable::ListOfValuesField ;  Let ( [Lista = ¶ & YourTable::ListOfValuesField & ¶ ;
                                                                                  Listb = Substitute ( Lista ; ¶ & T15_Products::ID_Product & ¶ ; ¶ ) ] ;
                                                                                  Middle ( Listb ; 2 ; Length ( Listb ) - 2 )
                                                                               ) ]

              I've just tested the original script and it doesn't delete the first character for me. Check to make sure that all the ¶ are where they are supposed to be. It's possible that you have YourTable::ListOfValuesField & ¶ where you should have: ¶ & YourTable::ListOfValuesField & ¶.

              • 4. Re: Help with a script step to remove the ID# from a list
                MarcMcCall

                Ok, I was thinking that when you said paramiter, that would posibly be when I set the button up and selected the script and it has the parameter down at the bottom.  I did what you suggested, and now it is working great.

                 

                Thanks Phil.....

                • 5. Re: Help with a script step to remove the ID# from a list
                  MarcMcCall

                  I have another question, that relates to this.  Is it posible to have a script loop that creates the new records and ends when there are no more values in my list since it deminishes as they are used, and now that the current records ID will removed, it will not get an error that I can not relate a product to itself, since I have that set up as a calculated validation.  

                  • 6. Re: Help with a script step to remove the ID# from a list
                    philmodjunk

                    Which list provides the list of values to be entered into the new records?

                    The original value list or this "exclusion" list? (Assuming that I understand how you've implemented your diminishing value list here...)

                    You may want to post a link to the thread where you got help setting up a diminishing value list so I can understand your current set up...

                    • 7. Re: Help with a script step to remove the ID# from a list
                      MarcMcCall

                      I used a custom function from BrianDunning.com I fumbled my way through it till I got it working, there is no thread. 

                       

                      SubtractValues(list1;list2)

                      Let ([
                          list1 = list1 & Case ( Right ( list1 ; 1 ) ≠ ¶ ; ¶ );
                          list2 = list2 & Case ( Right ( list2 ; 1 ) ≠ ¶ ; ¶ );
                          list3 = Case( ValueCount ( list2 ) = 1 ;
                              Substitute ( ¶ & list1 ; ¶ & list2 ; ¶ )
                          ;
                              SubtractValues (
                                  Substitute ( ¶ & list1 ; ¶ & RightValues ( list2 ; 1 ) ; ¶ )
                              ;
                                  LeftValues ( list2 ; ValueCount ( list2 ) - 1 )
                              )
                          );
                          result = list3
                      ];
                          result
                      )

                       

                      My valueList is comprised from:    

                      Value List Name: Deminishing_Related_Products_ID   From Field   Field: T15_Products::Deminishing_Related_Products_ID_List_ID (Related Values Only)

                       

                      Field Name:  Deminishing_Product_ID_List_ID    Type:  Calculation   Options/Comments:  Unstored, From T15_Products,  SubtractValues (ValueList_ID_Product_Related ;  Compatible_Products::List_Compatible )

                       

                      Compatible part are related by:

                      T15_Products::ID_Product = Compatible_Products::ID_Product_1   and   T15_Products::ID_Product  =  Compatible_Products::ID_Product2

                      Right now, I have a portal based on Compatible_Products on a layout based on T15_Products.

                      This is the current script:

                      • Set Variable [ $$ID_Product; Value:T15_Products::ID_Product ]
                      • Go to Layout [ “Compatible_Products” (Compatible_Products) ]
                      • New Record/Request
                      • Set Field [ Compatible_Products::ID_Product_1; $$ID_Product ]
                      • Go to Layout [ original layout ]
                      • Go to Object [ Object Name: "RelatedPartInfo" ]
                      • Go to Object [ Object Name: "Relation Portal" ]
                      • Go to Portal Row [ Last ]
                      • Set Field [ Compatible_Products::ID_Product2 ] 
                      This puts the currsur in the field so all I have to do is select the first value for the field, then repeat till the value list is empty.  I'm glad you suggested in annother post that I create a join table, because I am running into parts that have over 50 related parts, it is quite time consuming so this is why I would like toi loop a script to create the new record till there are no more values, and save my wrist.....
                      Oh, and the Compatible_Products::ID_Product2 field on the portal is what the deminishing value list is attached to, and the script that you just helped me with move the list to the ValueList_ID_Product_Related field and this is where the current records ID # is removed. .


                      • 8. Re: Help with a script step to remove the ID# from a list
                        philmodjunk

                        Sorry, Thought I saw a post by you asking about diminishing value lists yesterday...

                        The custom function appears to do something similar to my single set field step, but also works for removing a list of values instead of one value at a time. So why did you need both?

                        I don't understand your relationship:

                        T15_Products::ID_Product = Compatible_Products::ID_Product_1   and
                        T15_Products::ID_Product = Compatible_Products::ID_Product2

                        Why does a single field in T15_Products match to two different fields in Compatible_Products?

                        I would think that you'd have:

                        T15_Products::ID_Product = Compatible_Products::ID_Product_1

                        T16_Products::ID_Product = Compatible_Products::ID_Product2

                        (T16) or whatever label works in your system to identify an additional occurrence of the Products data source table.)

                        Set Field [ Compatible_Products::ID_Product2 ]

                        BTW, does not put the cursor in any field and appears to be missing a parameter.

                        • 9. Re: Help with a script step to remove the ID# from a list
                          MarcMcCall

                          Sorry it I missed a number when typing.

                          T15_Products 2::ID_Product = Compatible_Products::ID_Product2

                           

                          Set Field [ Compatible_Products::ID_Product2 ] when I didn't have that step in, it wouldn't put my currsur in that field, after I did, it appears to work, so I left it there.

                           

                          Right now, that is the end of my script, taken directly from the DDR I did so I wouldnt have to type everything in this post.  I'm not sure even where to start in order to make the script loop using the data from the value list.  Right now everything from there is manual.

                           

                          The custom function seems to work good, it works in both directions, if I use a value, it is removed, and if I delete the record, or remove the value, it is added back to the list.

                          • 10. Re: Help with a script step to remove the ID# from a list
                            philmodjunk

                            What I don't know at this point is what field, value list, or set of related records contains the ID's you want to enter into the ID_Product2 field.

                            In all three cases, you can load the list into a variable and loop through it creating one new related record with each iteration.

                            Freeze window
                            Set Variable [$IDList ; //what you put here could be valuelist items, the name of a field or List ( Related Table::Field )]
                            Set Variable [ $ID_Product; Value:T15_Products::ID_Product ]
                            Go to Layout [ “Compatible_Products” (Compatible_Products) ]
                            Loop
                              New Record/Request
                              Set Variable [$K ; value: $K + 1]
                              Exit loop if [$K > ValueCount ( $IDList )
                              Set Field [ Compatible_Products::ID_Product_1; $ID_Product ]
                              Set Field [ Compatible_Products::ID_Product_2; GetValue ( $IDList ) ]
                            End Loop
                            Go To Layout [original layout]

                            • 11. Re: Help with a script step to remove the ID# from a list
                              philmodjunk

                              For an alternative approach to a diminishing value list for a join table, see this demo file: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                              It does not require a custom function or complex scripting to implement.