13 Replies Latest reply on Nov 11, 2013 8:24 AM by philmodjunk

    Auto populate portal records based on parent table.

    AliSheikh

      Title

      Auto populate portal records based on parent table.

      Post

           Hello,

               I've had some help earlier in creating a database i'm working on from this site and for which i'm really thankful. Right now i've come across a roadblock where i have an invoice table with a portal to lines table. 

            

           Invoice ----< Lines table >-----parts>----category---->release>----project

            

            From the invoice table i selected a couple of conditional drop down fields which help me select only the selected parts from the lines table portal. All that goes fine, except that i want to find a way to automatically populate the lines table based on the selection i've made on the invoice table. For example i have fk fields to project, release and category on the invoice table and when i select the ones im looking for, then i'm able to choose only those parts that belong to that series of selections i've made in the fk fields. But this only happens one portal row at a time. I saw some scripts earlier but couldn't find a way to implement them into my issue. Can anyone guide me to create a script to do what i require. Thanks! 

        • 1. Re: Auto populate portal records based on parent table.
          philmodjunk

               Apologies, but I'm not sure I can picture what change you want to make. What exactly do you want to do differently?

               Do you want to select from the conditional value lists once for each invoice and then the value list for selecting a lineitem for the portal is always the same list of values for the entire invoice so that you don't have to select these values for every line item?

          • 2. Re: Auto populate portal records based on parent table.
            AliSheikh

                 No. That part you mentioned is already working at the moment exactly as you've stated. What i want is the portal to show all the parts in the lines table portal, instead of having to select on each row one by one. 

                  

                 Right now, I have three drop down fields on the invoice table:

                 Project

                 Release

                 Category 

                 When i select the project drop down , it filters the release field and only shows related fields, and so on with the category field too. Upon selection of the category field, i want the portal to line items on the invoice layout, to be filled or populated automatically with all the parts that relate to the selections made above. 

                  

                 At the moment i would have to go through each portal row one by one if there are 50 parts that i want to show belonging to the specific category, from the selected release and project. In order to make it more efficient, is there a way for the portal to populate line items with all the parts based on  the selections i would have made in the conditional value lists?

            • 3. Re: Auto populate portal records based on parent table.
              philmodjunk

                   Got it.

                   You can capture the values in the value list with a calculation and put it in a variable:

                   Set Variable [$PartsList ; value: List ( TableOccurrenceName::__pkPartID ) ]

                   You'd refer to the tableOccurrence from which your current conditional value list of parts lists values for the value list.

                   or

                   Set Variable [$PartsList ; value: ValueListItems ( Get ( FileName ) ; "YourvalueListNameGoesHere" ) ]

                   Either expression should put the same values into the $PartsList Variable.

                   Then your script can loop through the values in $PartsList to create the needed line items records:

                   Freeze Window
                   Set Variable [$InvoiceID ; Invoice::__pkInvoiceID ]
                   Go to  Layout ["LineItems" (LineItems) ]
                   Loop
                      Set Variable [$K ; value: $K + 1 ]
                      Exit Loop if [ $K > ValueCount ( $PartsList ) ]
                      New Record/Request
                      Set Field [LineItems::_fkPartID ; GetValue ( $PartsList ; $K ) ]
                      Set Field [LineItems::_fkInvoiceID ; $InvoiceID ]
                   End Loop
                   Go to Layout [original layout]

                   Edit Note: Missing text in red has been added to correct script error.

              • 4. Re: Auto populate portal records based on parent table.
                AliSheikh

                     Thanks, i will try it now. Just a couple of questions before i proceed. 

                     All the command lines go on a single script correct?

                     And is there a preferred way to execute this script? Such as upon objectmodify of the last conditional value ( category in this case). 

                • 5. Re: Auto populate portal records based on parent table.
                  philmodjunk

                       Yes. this is a single script, including a script step to assign the list to the variable.

                       You can perform this script from a button if you want to keep your current value list for cases where you don't want the full list or you can use a script trigger on the value list field to perform the script.

                  • 6. Re: Auto populate portal records based on parent table.
                    AliSheikh

                         seems to be working except that it is leaving the invoiceid_fk field empty in the lines table. Is there supposed to be a get value part for the setfield of invoice fk in the script?

                    • 7. Re: Auto populate portal records based on parent table.
                      AliSheikh

                           Ok i guess, $invoiceID was missing in the script for specified calculation , in third from last row of the script. Thanks Phil this is working PERFECT!! :) 

                      • 8. Re: Auto populate portal records based on parent table.
                        philmodjunk

                             Yes, $InvoiceID was missing from the script--a mistake on my part. I've edited the post to make a correction.

                        • 9. Re: Auto populate portal records based on parent table.
                          AliSheikh

                               Hello, 

                                      There's some additional info i would like. I was playing around with the script you showed above to tweak it further in order to create a duplicate Invoice record that would also duplicate the portal records but i'm being unsuccesful. :(

                               I would only like to duplicate the parent record along with the parts in the portal row. I tried a script which captures the Invoice ID and performs a goto lines record and then set variable on the part_fk and create a new record and so on but when i execute the script it shows a dialog box asking " go to record " and an edit box to type a number and it follows that about 4 or 5 times until the new record is created but it doesn't show the right parts that were there in the original invoice record's portal. 

                          • 10. Re: Auto populate portal records based on parent table.
                            philmodjunk

                                 The way that Duplicate record changes which record is the current record often results in problems when people try to script the duplication of a parent record and also it's set of related child records.

                                 See the example script in this thread for ideas. Duplicating Bill Of Materials (duplicating portal line items)

                            • 11. Re: Auto populate portal records based on parent table.
                              AliSheikh

                                   beautiful! worked perfect , thank you again! 

                              • 12. Re: Auto populate portal records based on parent table.
                                AliSheikh

                                     Hi phil, i had a question about the link u gave a while ago about duplicating a record with a portal:

                                 Duplicating Bill Of Materials (duplicating portal line items)

                                      

                                     I tried the script from the link, but when i duplicate the record for the first time , it will show all the proper number of portal records in the new parent record. If i duplicate the original record a second time, it creates a new record with double the same portal records as in the original. Is there a way to counter this problem ? i want to be able to duplicate from the parent record several times without having to add the same portal rows multiple times over again. 

                                • 13. Re: Auto populate portal records based on parent table.
                                  philmodjunk

                                       That should not occur.

                                       Did you use the modified script at the end of the thread or the one at the beginning? The first script example has a "bug" that is corrected in the second version of the script included near the end of the thread.