1 2 Previous Next 16 Replies Latest reply on Jul 21, 2017 8:00 PM by disabled_brianb

    set field with carriage return issue

    m.mcdonell

      I am trying to take 3 fields and place them in a single line on a new field, then loop to a new record (same customer) and repeat but on a new line. Can not figure it out, it keeps replacing the data.

       

      Example: 3 fields - Product ID / Item / Price

       

      I set a script with the set field: $ProductID  & " - "   & $Item  & " - "  & $Price

       

      when it loops it keeps replacing the data. I tried set position to 1 but same problem.

        • 1. Re: set field with carriage return issue
          philmodjunk

          I suggest posting the complete script as a screen shot or a text paste after copying from a PDF or database design report. Best guess is that some error is failing to update your variables with each new record. For that matter it is not clear why you need to use variables.

          • 2. Re: set field with carriage return issue
            TomHays

            If you are assigning to NewField using the Set Field script step and you want to add a new calculated line to the existing value of NewField then you want to write something like the following as your calculation.

             

            List(NewField; $ProductID  & " - "   & $Item  & " - "  & $Price)

             

             

            -Tom

            • 3. Re: set field with carriage return issue
              beverly

              Howdy! it's the loop issue:

              1. Set a variable (or field)

              2. now append that variable (or field with new information) in each run through the loop

               

              "Append" is the key here. Call back a value, use the ampersand to concatenate the original with the new value.

              Set Variable [ $myVar ; "abc" // initial set ]

              Set Variable [ $myVar ; $myVar & "def" // concatenate with the ampersand to append ]

              or

              Set Field [ myTable::myField ; "abc" // initial set ]

              Set Field [ myTable::myField ; myTable::myField & "def" // concatenate with the ampersand to append ]

              Where you set this (in a loop for example) is also important.

              The first "Set" is the original value. So that to keep it "nice", I might set the first to "" (empty) and then I can append everytime inside the loop:

              Set Variable [ $myVar ; "" // clear at start of loop ]

              Loop

                   # APPEND HERE:

                   Set Variable [ $myVar ; $myVar & $whatever ]

                   Exit Loop If [...]

                   Go to ... // however you get to the next value that should be appended

              End Loop

              The List() function allows you to append, as well and includes the return between result values:

              NOTE, no ampersand needed!

              Set Variable [ $myVar ; List ( $myVar ;  $whatever ) ]

              List() is handy, because it only includes non-blank values.

              Beverly

              • 4. Re: set field with carriage return issue
                rgordon

                If you are pulling values from related records the List() function works great but in this case where a loop is involved the loop does the job of the List().

                • 5. Re: set field with carriage return issue
                  beverly

                  Yes, but an explicit Char(13) must also be appended where needed. List() takes care of the returns (and eliminates the blanks) even in a loop.

                  compare:

                  Set Variable [ $myVar ; $myVar & $myText & Char(13) ]

                  Set Variable [ $myVar ; List ( $myVar ; $myText ) ]

                   

                  Beverly

                  1 of 1 people found this helpful
                  • 6. Re: set field with carriage return issue
                    m.mcdonell

                    Sorry for the delays, I will review the threads in a minute as well. It is 2 tables that are related.

                    Invoice Data - 3 fields of data (Product ID, Item, Price)

                    Invoice - 1 field called Pricing where I hope to out the list from above

                    Both are related by Invoice iD (Invoice is parent)

                     

                    I am wanting a looped script that goes through each record on Invoice Data (multiple records could be attached to 1 invoice).

                    I have the following on a loop;

                    Set Variable ($ProductID; Value: Invoice Data:: Product ID

                    Set Variable ($Item; Value: Invoice Data:: Item

                    Set Variable ($Price; Value: Invoice Data:: Unit Price

                     

                    Set Field [Invoices::Lineitem Pricing; $ProductID & " - " & $Item & " - " & $Price

                    Go to Record/Request/Page [Next; Exit after last:On}

                     

                    My problem is that when multiple records are attached to the same invoice, it just overwrites the line.

                    • 8. Re: set field with carriage return issue
                      rgordon

                      No loop is needed. I think this step will give you what you want.

                       

                      Set Field [Invoices::Lineitem Pricing; Value: List( Invoice Data:: Product ID & "-"&Invoice Data:: Item & "-" &Invoice Data:: Unit Price)

                      • 9. Re: set field with carriage return issue
                        m.mcdonell

                        This works great for the first record but once I go to the next record (same it replaces the data). I need to insert data on the first line each time, thereby pushing existing data to the second line.

                        Ideas?

                        • 10. Re: set field with carriage return issue
                          karina

                          Hi,

                          Do you have a table Invoice and a table InvoiceLines and a table with the Products, or is the Data table the table you use for your InvoiceLines?

                           

                          If that's the case, you should also add an InvoiceLines table.

                           

                          Greetz,

                          Karina

                          • 11. Re: set field with carriage return issue
                            beverly

                            Reverse the append to be prepend:

                             

                            Set Variable ( $x ; $x & newstuff & Char(13) // append )

                            Set Variable ( $x ; newstuff & Char(13) & $x // prepend)

                             

                            HTH

                            Beverly

                             

                            Sent from miPhone

                            • 12. Re: set field with carriage return issue

                              If i understand correctly, the issue is not with the records/relationship calculation but with the layout display of pulling the related records into a "list" format for print?

                               

                              Is this correct?:   1) invoice::pkInvoice ID ----< Invoice ID:: Invoice details    WHERE

                                                          2) Invoice Details contains fields  Product, Item, Price

                                                          3) You want a new field that puts Product, Item, Price on a single line in order to  pull all related                                  invoice detail records into  an Invoice  table display summary?

                                                           4) The Invoice  layout/report is suppose to display a list of all Invoice detail records in a single "field"                                       such as:

                                                                      Invoice ID                 Invoice details

                                                                        123                           ProductA - ItemA - PriceA

                                                                                                         ProductB - ItemB - PriceB

                                                                                                         etc...

                               

                              How is the "same customer" identified and related in the described relationship. Is there a third table for Customer

                              Customer---< Invoice------< Invoice Details ?

                               

                              If so  does this final out put really mean

                               

                              Customer Name

                                             Invoice 1                             Invoice Details

                                                                                         Record 1 -record1-record1

                                                                                         record2-record2-record2

                                            

                                             Invoice 2                              record3- record3-record3

                              • 13. Re: set field with carriage return issue
                                philmodjunk

                                Seems to me that a portal can list this info with no script needed.

                                • 14. Re: set field with carriage return issue

                                  yeah thats where I am going with that.... If the OP can confirm  then maybe we can get this solved.

                                   

                                  After rereading the OP a few times the  terminology used may be in a different context and therefor misleading

                                   

                                  3 fields and place them into a single field

                                  =  Product_field  , Item Field , Price_Field

                                  1. field 4 should be a calculated field   and the calculation would be applied to all records in the Invoice Details tables
                                  2. However, you stated that this is not the case you (a few quotes below) and it just keeps replacing the data that is visible in your layout.   Makes me think the layout is only showing a single record in form mode.
                                  3. To see all related records you need to be in the 'parent' table and insert a portal into that layout. Then reference the invoice details table form within that portal.
                                  4. 2nd option is to add a  list() calculation to the Invoice table and reference the Invoice Details table field 4
                                  5. 3rd option is if  eSQL function  to look up unrelated records, assuming a customer identifier is not a key in a relationship
                                    1. executeSQL.... SELECT DISTINCT Field4 FROM Invoice Details... WHERE  CustomerID =

                                   

                                  then loop to a new record (same customer) and repeat but on a new line

                                  = Find other records where field 5 identifies  Same Customer. Depending on setup  it could be a scripted find or hopefully a relationship. But new record in this context means already existing record and pull the  Invoice Details::CombinedField

                                   

                                  How is the "same customer" identified and  is that within the Invoice Table , Invoice Details, OR a third Table?

                                   

                                  when it loops it keeps replacing the data. I tried set position to 1 but same problem.

                                  = I can only see the first record.  Either you are looking at the Invoice Details based on a Form layout and therefore only a single record is showing, or there is a broken relationship that is preventing pulling the related Invoice Details.

                                  1 2 Previous Next