Hi,

I've written a SQL query that is returning a list of data for me. I've got more than one roq, seperated by a Return. I'd like to take the different fileds out into some fields in a table.

This bit I've managed to do with the use of left/right/middle/position.

 

So in the example below. $$LLData, I've gotten 13 and 1 out into 2 different fields. When I try take 2 out it is giving me 2 13|2|1 as it's not seeing the second 13|2|1 as a seperate record.

Can someone let me know howI can get Filemaker to see this as 2 rows so I can loop through the list to pull the data into a table.

TIA.

 

You'll need to write two loops.

Assuming you have $$LLData with ¶ as record delimiter, and | as field delimiter**, write a script along these lines:

Set Variable [ $rowCount ; ValueCount ( $rowCounter ) ) ]
Set Variable [ $rowCounter ; 0 ) ]
Loop
  # [ through rows/records ]
  Exit Loop if [ Let ( $rowCounter = $rowCounter + 1 ; $rowCounter > $rowCount ) ]
  Set Variable [ $currentRow ; GetValue ( $$LLData ; $rowCounter ) ]
  # [ convert current row into a list so you can use GetValue() ]
  Set Variable [ $currentFieldList ; Substitute ( $currentRow ; "|" ; ¶ ) ] 
  Set Variable [ $fieldCount ; ValueCount ( $currentFieldList ) ]
  # [ never forget this one! ]
  Set Variable [ $fieldCounter ; 0 ) ]
  Loop
    # [ through fields ]
    Exit Loop if [ Let ( $fieldCounter = $fieldCounter + 1 ; $fieldCounter > fieldCount ) ]
    Set Variable [ $currentFieldValue ; GetValue ( $currentFieldList ; $fieldCounter ) ]
    # [ do something with $currentFieldValue, e.g. Set Field[] if you created New Record[] in the outer loop, etc. ] 
  End Loop
End Loop

Knowing your Left(), Right() and Middle() is a good thing, but as you can see, there are sometimes easier methods for text parsing and extraction.

**Make sure you specify delimiters that don't appear in your result. You could also write a Custom Function like “SQLFieldDelimiter” that simply defines a string like "§§§§" to use (consistently) as field delimiter. Then write

ExecuteSQL ( query ; SQLFieldDelimiter ; ¶ /* default anyway */ )

and in the script

Set Variable [ $currentFieldList ; Substitute ( $currentRow ; SQLFieldDelimiter ; ¶ ) ]

Edited June 23, 201510 yr by eos

thank you so much Eos. That has solved my problem.

Depending on what you are doing with the data, this may be a good candidate for the Virtual List.