Hi, and welcome to technet,
I have created a sample file to demonstrate this sort of method. It's a bit trickier since you're using an intermediary table. This shows the schema needed to calculate a related value list from a selfjoin. However the calculation pulled through the selfjoin is incomplete (just showing all records now). You said you were familair with SQL server so you can probably write the rest of the SQL query from here.
You will need to update the ExecuteSQL() calculation field in the sample table to account for only showing the records you need. I didn't have time right now to work that out.
Here's a guide though about everything in ExecuteSQL()
There might be other alternatives as well to doing this. But this seemed like the lightest weight way for me to calculate what you need.
sample.fmp12.zip 12.7 K
Thank you. I will take a look at this and let you know if I have any more questions.
In looking at the guide about ExecuteSQL, is there a way to parse the results from a multi column query into distinct fields in a portal?
Yes, you can parse data however you see fit. What I normally do is set a custom field separator (by default it's a comma, but you can set it to be something like |*|.
From there, my ExecuteSQL "sqldata" would look like:
Then you could parse it out with a few calculations:
To get a row of data: GetValue( sqldata ; recordnumber)
To get a cell of data (IE field1): left( sqldata ; length(sqldata) - position(sqldata ; "|*|" ; 1 ; 1 ) )
The method most filemaker developers (I know of) are using for parsing temporary "scratch" data is the virtual list technique, where you store "sqldata" as a global variable (IE $$sqldata) and you have a table of scratch records that have fields calculating the values from $$sqldata based on the record ID.
GetValue( $$sqldata ; scratchtable::recordid )
Here's good coverage of virtual listing:
I'd highly recommend using a copy of filemaker advanced if you don't already, and running your queries through the data viewer to see what is returned. You can also use script debugger and data viewer in combination to see what variable values are returned by your scripts.
I wanted to take the time to say thank you.
Between your examples and the documents you suggested for reading. I was able to design the exact solution I was looking for.