Search code examples
filemakerexecute-sql-taskvirtuallistview

Combining rows from multiple sources in Virtual list Filemaker

I am trying to make an Excel-like 'pivot table' in Filemaker using a Virtual List as the source of the data. The issue is I want to be able to have 'categories' down the first column that aren't fixed. The field names won't work.

My current thought is to have a table with a field of the layout name and the categories, that I would combine with the rest of the data via a ExecuteSQL (or other function).

I can get it to work with two ExecuteSQL statements, one for the categories and one for the 'bulk' of the data, using text for the WHERE in the categories eSQL, then I combine them into one and I'm set.

My issue is that I'd like to be able to get the categories using a get(LayoutName) function, making the script more flexible. Whenever I use the get(LayoutName) in the WHERE line of the SQL I get a ? for the result. I have also tried putting the layout name in a field using a get(LayoutName), then using that field as in the WHERE statement, but that also returns an error.

I admit I am a bit of a newbie at this, so the problem is likely between the keyboard and the chair with a simple syntax error. I have tried a bunch of different ways with quotes, no quotes, single quotes, etc.

Here is what I am using for pulling the categories...

Substitute ( ExecuteSQL ( "SELECT Category_List FROM Categories_VL WHERE Layout_Name = Get(LayoutName)" ; "" ; "") ; ", " ; $$delim )

All of the field names are correct, and if I change the LayoutName to a text that matches the Layout_Name field I want, it works fine.

I apologize if I have been too wordy, but I figure more info is better than answering a bunch of questions because I forgot something!

TIA!

Solution

  • I am struggling to understand your question. I hope I am addressing the correct issue.

    You cannot use Filemaker functions inside an SQL query. To find records where the Layout_Name field is equal to the current layout's name, your query should be:

    SELECT Category_List 
FROM Categories_VL 
WHERE Layout_Name = ?

    and then you would supply the current layout's name as an argument:

    ExecuteSQL ( "SELECT Category_List FROM Categories_VL WHERE Layout_Name = ?" ; "" ; "" ; Get ( LayoutName ) )

    Note that instead of substituting the default delimiter, you can specify your own field and row separators within the ExecuteSQL() function itself, for example:

    ExecuteSQL ( "SELECT Category_List FROM Categories_VL WHERE Layout_Name = ?" ; $$delim ; "" ; Get ( LayoutName ) )

    See: https://help.claris.com/en/pro-help/#page/FMP_Help%2Fexecutesql.html%23