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.
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)
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 ]
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 ]
# APPEND HERE:
Set Variable [ $myVar ; $myVar & $whatever ]
Exit Loop If [...]
Go to ... // however you get to the next value that should be appended
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.
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().
1 of 1 people found this helpful
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.
Set Variable [ $myVar ; $myVar & $myText & Char(13) ]
Set Variable [ $myVar ; List ( $myVar ; $myText ) ]
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.
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)
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.
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.
Reverse the append to be prepend:
Set Variable ( $x ; $x & newstuff & Char(13) // append )
Set Variable ( $x ; newstuff & Char(13) & $x // prepend)
Sent from miPhone
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
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
Invoice 1 Invoice Details
Record 1 -record1-record1
Invoice 2 record3- record3-record3
Seems to me that a portal can list this info with no script needed.
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
- field 4 should be a calculated field and the calculation would be applied to all records in the Invoice Details tables
- 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.
- 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.
- 2nd option is to add a list() calculation to the Invoice table and reference the Invoice Details table field 4
- 3rd option is if eSQL function to look up unrelated records, assuming a customer identifier is not a key in a relationship
- 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.