CODEWITHSUNDEEP
filemaker
Mike
Mike

Reputation: 909

How do I get a list of values?

I have a table Targets with unique ID number and field data. I want to get a list containing all values of number in Targets so this list (preferably comma separated) can end up as a JS array in a Web View.

In SQL, it would be as simple as 

SELECT number FROM Targets

But I can't find any reasonable method in FileMaker Pro Advanced 11. I think the List function may be it, but for some reason, it won't list all my rows.

Bonus points if you can do the equivalent of

SELECT number, data FROM Targets

Upvotes: 2

Views: 4323

Answers (3)

SWL
SWL

Reputation: 3864

The easiest way, which may seem like a hack but is not, is to make a valuelist. Make a valuelist set to display all values in "number". FileMaker will automatically consolidate all values into a return-separated list of unique values. Then you can get the list by running the calculation:

 ValueListItems( Get(FileName) ; "AND-PUT-YOUR-VALUELIST-NAME-HERE")

Upvotes: 0

Paul Hutton
Paul Hutton

Reputation: 236

On the Targets table, add a calculation field (say, ID Data Array) that looks like a javascript array literal:

"[" & number & "," & data & "]"

On a layout that has a cross-product relationship to a Targets table occurrence (say, Targets All), add a Web View component with something like the following calculated content:

"data:text/html,¶" &
"<!DOCTYPE html PUBLIC " & Quote ( "-//W3C//DTD XHTML 1.0 Strict//EN" ) & "¶" &
Quote ( "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd" ) & ">¶" &
"<html xmlns=" & Quote ( "http://www.w3.org/1999/xhtml" ) & ">¶" &
"<head>¶" &
"<meta http-equiv=" & Quote ( "content-type" ) & " content=" & Quote ( "text/html; charset=utf-8" ) & " />¶" &
"<title>Table Data</title>¶" &
"<script type=" & Quote ( "text/javascript" ) & " src=" & Quote ( "script.js" ) & " ) & ">¶" &
"var dataArray = [ " & Substitute ( List ( Targets All::ID Data Array ) ; "¶" ; "," ) & "];¶" &
"</script>¶" &
"</head>¶" &
"<body>¶" &

"</body>¶" &
"</html>"

The key component is this:

"var dataArray = [ " & Substitute ( List ( Targets All::ID Data Array ) ; "¶" ; "," ) & "];¶"

This uses the List function to collate all the related ID Data Array fields into one ¶-delimited list.

It then uses the Substitute function to replace the ¶ characters with commas so you get a nice comma-delimited list of arrays.

Finally, it wraps it in a javascript variable declaration so you will have the variable dataArray available in the rest of the page.

Upvotes: 1

andyknas
andyknas

Reputation: 1957

If you're in FileMaker Pro 11, use one of the SQL plugins to query itself. Or, upgrade to v12 and use the new SQL command.

Upvotes: 0

Related Questions