11 Replies Latest reply on Sep 5, 2009 5:47 AM by disabled_menno

    create records automatically in a portal via drop down list

    Sidhu

      Title

      create records automatically in a portal via drop down list

      Post

      Hi,

       

      I have a 4 tables for Customers, Invoices, Line Items and Products

       

       

      I am using Line Itmes to link invoices and Products

       

      I am creating records in Line items via a portal from Invoices.

       

      On Invoices table i choose customer using foreign key and populate his details.

       

      and then in the Portal i choose the products that customer wants to buy and add the Quantity.

       

      Now most of my customer wants to buy all the products available. (15 products)

       

      So on my portal, i have a dropdown of my products (Foreign Key) where i choose my producst one by one,

       

      i would like to have a button with script, which fills all the products in the drop down list in my portal thus creating records automatically in Line Items.

       

       

      Could anyone help me please.

       

      Thanks in advance 

       

       

       

       

       

       

       

        • 1. Re: create records automatically in a portal via drop down list
            

          I'd create a script that would run in the following way where I assume that you already have created the invoicerecord so you have an invoice_id:

          So the context you start from is the invoice layout:

           

          # from invoice-layout; make sure record is commited

          freeze window

          set var $fkinvoice ; invoice_id
          # collect all possible product_id's
          go to layout "Products"
          find all records
          go to first record
          loop
            set variable $fkproduct ; $fkproduct & product_id & ¶
            set variable $count ; $count + 1
            goto record next [exit after last]
          end-loop
          # start creating the line-items
          go to layout "Line items"
          loop
            create new record
            set variable $value ; $value + 1
            set field invoice_id ; $fkinvoice_id
            set field product_id ; GetValue ( $fkproduct_id ; $value )
            commit record []
            exit-loop-if [$value=$count]
          end-loop
          go to layout "Invoice"

          exit-script[]

          #

           

          regards, Menno










          • 2. Re: create records automatically in a portal via drop down list
            Sidhu
              

            Thanks for your help Menno, it works great,

             

            i have one more question for you,

             

            I would like to have my products categorized.

             

            From Invoices Layout i would like to have a Global feild, depending on the value of Global list i will have my products drop down in the Portal.

             

            Now could you please help me with the script where i can create records will only the values displayed in the drop-down list.

             

            Thanks again 

            • 3. Re: create records automatically in a portal via drop down list
                

              I would solve that in the exactly same way:

               

              # In the product-table you must have a field "product_category"

               

               

              # from invoice-layout; make sure record is commited

              freeze window

              set var $fkinvoice ; invoice_id

              set var $category ;gCategory // put the value of the global into a variable

              # collect all possible product_id's
              go to layout "Products"
              find all records
              go to first record
              loop

                If [$category="" or $category=product_category] // now only product_id's for the products that are in the chosen product_category will be collected (or all if you haven't chosen a category)

                  set variable $fkproduct ; $fkproduct & product_id & ¶
                  set variable $count ; $count + 1

                end-if
                goto record next [exit after last]
              end-loop
              # start creating the line-items
              go to layout "Line items"
              loop
                create new record
                set variable $value ; $value + 1
                set field invoice_id ; $fkinvoice_id
                set field product_id ; GetValue ( $fkproduct_id ; $value )
                commit record []
                exit-loop-if [$value=$count]
              end-loop
              go to layout "Invoice"

              exit-script[]

              #

               

               

              regards, Menno








              • 4. Re: create records automatically in a portal via drop down list
                mrvodka
                  

                Wouldnt it make more sense to loop through the list of values from the value list instead of the table?

                 

                Should use: ValueListItems ( Get ( FileName ); "Product Value List" )

                 



                Set Variable [ $invoiceID ; invoice_id  ]

                Set Variable [ $productID; ValueListItems ( Get ( FileName ); "Product Value List" ) ]

                If [ not IsEmpty ( $productID ) ]

                  Set Variable [ $end; ValueCount ( $productID ) ]

                  Go to Layout [ LineItems ]

                  Loop

                     Set Variable [ $k; $k + 1 ]

                     Exit Loop If [ $k > $end ]

                     New Record

                     Set Field [ invoice_id ; $invoiceID ]

                     Set Field [ product_id ; GetValue ( $productID ; $k )]

                  End Loop

                  Go to Layout [ Invoice ]

                End If

                 


                • 5. Re: create records automatically in a portal via drop down list
                     There is always more than just one road that leads to Rome
                  • 6. Re: create records automatically in a portal via drop down list
                    mrvodka
                      

                    menno wrote:
                    There is always more than just one road that leads to Rome
                    • 7. Re: create records automatically in a portal via drop down list
                      Sidhu
                        

                      Hi Guys, 

                                Thanks for your help so far,

                                 both the scripts works fine,

                                   but if a user by mistakes runs the script twice, records are created in line items twice with same products. is there anyway i can stop it

                      Thanks in advance 

                      • 8. Re: create records automatically in a portal via drop down list
                        mrvodka
                          

                         Just do a find to see if already exists...

                         

                         

                        Set Variable [ $invoiceID ; invoice_id  ]

                        Set Variable [ $productID; ValueListItems ( Get ( FileName ); "Product Value List" ) ]

                        If [ not IsEmpty ( $productID ) ]

                          Set Variable [ $end; ValueCount ( $productID ) ]

                          Go to Layout [ LineItems ]

                          Loop

                             Set Variable [ $k; $k + 1 ]

                             Exit Loop If [ $k > $end ]

                             Enter Find Mode []

                             Set Field [ invoice_id ; $invoiceID ]

                             Set Field [ product_id ; GetValue ( $productID ; $k )]

                             Set Error Capture [On]

                             Perform Find []

                             Set Error Capture [Off]

                             If [ not Get ( FoundCount) ]

                                New Record

                                Set Field [ invoice_id ; $invoiceID ]

                                Set Field [ product_id ; GetValue ( $productID ; $k )]

                             End If

                          End Loop

                          Go to Layout [ Invoice ]

                        End If

                         

                        • 9. Re: create records automatically in a portal via drop down list
                            

                          This mistake probably happens during the same time the first list of line is created? If that is the exact situation you can reolve the issue differentl:

                           

                          Set Variable [ $invoiceID ; invoice_id  ]

                          Set Variable [ $productID; ValueListItems ( Get ( FileName ); "Product Value List" ) ]

                          Set Variable [ $lineitem_product_id;list (LineItem:: product_id) ]

                          If [ not IsEmpty ( $productID ) ]

                            Set Variable [ $end; ValueCount ( $productID ) ]

                            Go to Layout [ LineItems ]

                            Loop

                               Set Variable [ $k; $k + 1 ]

                               Exit Loop If [ $k > $end or patterncount ( $lineitem_product_id ; $productID ) ]

                               New Record

                               Set Field [ invoice_id ; $invoiceID ]

                               Set Field [ product_id ; GetValue ( $productID ; $k )]

                            End Loop

                            Go to Layout [ Invoice ]

                          End If

                           

                          Of coarse this solution assumes that the order of the articles in the linitems is the same as in the valuelist, but it is less code, as mr_vodka seems to be keen on.

                           

                          cheers

                          • 10. Re: create records automatically in a portal via drop down list
                            Sidhu
                              
                            Hi Guys,
                                       I am stuck again with one more problem,
                            I have a table for Results which is linked with foreign keys to Students and Subjects.
                             
                            In the results table i have, pkresult_ID, fkStudent_ID, fkSubjectI_D, results feild(which has Pass or Fail)
                             
                            I have one more table called Final and I created similar feilds fkStudentID, fkresultid, fksubjectid, result feild.
                             
                            i want to create records in final table, only for the students whose result is fail. 
                            fkStudentID, fkresultid, fksubjectid, result feild, (which has only fail).
                             
                            i wrote script which copies all the fail records to the new table. but everytime i run the script to update latest fail records, previos records gets duplicated, below is my script.
                             
                             In Students Layout

                            Freeze Window

                            Set Variable [$Student; Students::pkstudent_id]
                            set variable [$Result; Value: "Fail"]

                            #Collect all Subjects, failed by the student

                            Go to layout ["RESULT"(RESULT)]

                            Show all records

                            go to record [First]

                            Loop
                                If[$result = RESULT::result]
                                    Set Variable [$SubjectID; Value:$SubjectID & RESULT::fksubject_id & ¶ ]
                                    
                                    Set Variable [$ResultID; value: $ResultID & RESULT::pkresult_id & ¶ ]
                                 
                                    Set Variable [$Count; Value:$Count + 1]
                                   
                                 End If
                                 Go to Record [Next; Exit after last]
                            End Loop

                            #Create Records in NYC with student id and subject id

                            Go to Layout ["Final" (NYC)]

                            Loop
                              Set variable [$K; Value: $K+ 1]
                              
                              New Record
                              set Field [ Final::fkstudent_id; $StudentID]
                              
                              set Field [ Final::result; $result ]

                              Set Field [ Final::fkSubject_id; GetValue ($SubjectID ; $K)]

                              Set Field [ Final::fkresult_id;  GetValue ($ResultID; $K)]

                            Commit Records

                            Exit Loop If [ $Count = $K]

                            End Loop

                            Go to Layout["STUDENTS"
                             
                            Please help me guys.  
                             
                            • 11. Re: create records automatically in a portal via drop down list
                                

                              Hi Sidhu,

                               

                              you should go about this in the same way as your previous problem: In the first loop avoid picking up the records that you have already copied to the other table.

                               

                              In this case you could simply create an new table-occurrence for the final-table. The relationship should be from pkResultId to fkResultId in Final and then check if the relationship is true and don't copy that particular record if the relationship is true.

                               

                              Another way can be that when you copy a record enter a value is a extra field, so that next time when you gather the keys from the "fail" records again that particular record gets omitted because the extra field has a particular value.

                               

                              There are at least 2, 3 or even more nice ways that solve this problem, but you should think through thoroughly what you are trying to achieve ... after that is just implementing logical steps according to the flow-schema of checks and jobs you just thought up.

                               

                              Btw, sorry that i took so long to answer, i was on a vacation ;)