When you say "looked up value", that implies a value you want to copy from table B, to table A, for permanent storage.
An example of this is "current fuel price", if I bought 10 gallons of gas, I would do a lookup for the current rate. This way I could store the rate permanently in my "purchases" table, while the prices constantly fluctuate in the "rates" table.
So in your case, if you are only storing a value in "outcome field 1", then you are correct in thinking that the 4 other match fields would be part of a relationship. I don't know what your data is, but I can probably speculate that you do NOT need a join table between the two tables if you are just interested in populating a looked up value.
Another thing you could do is set a value in Table A using an ExecuteSQL() function, this requires NO relationships to table B, and possibly saves you fields in table A as well since no relationship is needed. It would look something like this:
ExecuteSQL("SELECT TheValueToBeLookedUp FROM TableB WHERE Parameter1 = ? AND Parameter2 = ? AND Parameter3 = ? AND Parameter4 = ?";"";"";TableA::Parameter1;TableA::Parameter2;TableA::Parameter3;TableA::Parameter4)
Thanks! Indeed, I'm interested in looking up a value for permanent storage.
I think I'll go the ExecuteSQL() way, then I don't have to bother controlling 4 relationships.
Thanks for giving me this direction.
To confirm, it was one relationship with 4 parameters for matching. Not 4 relationships.
Yep! it's a little like making an AND FIND with 4 criteria (from TableA, pushed into variables) and used in TableB's fields during the find request. Then with a "found record" in TableB, get the value that will be pushed back into TableA::Outcome field 1
Mike's query is so much easier.
At some point, consider using a remote data IDE for working with SQL, which uses a direct connection to your FM database.
With any SQL beyond the most basic, I find trying to get past the "?" you end up getting in the Data Viewer and then only seeing a few rows in the non-resizeable window is very frustrating. Plus, SQLExecute() only supports SQL SELECT.
Once I get the SQL working, it's a snap to move over to FMP (since the SQL you work on is actually connected to your actual FMP database) add SQL parameters, if needed, and the other SQLExecute options.
Plus in a remote IDE you can resize windows, control fonts, export data, take advantage of SQL SELECT, UPDATE, DELETE, and INSERT.
FileMaker includes the JDBC driver in the installation files. It's really good.
Thanks for thinking along. I'm very happy with this forum. I try not to abuse it by just lazily asking stuff, but only when I have no idea. It is often just a direction in which to best think, that I need. Like in this case.
Yes you're right. When you drag 2 relationships between two TOs it actally is one relationship, with 2 parameters. So after dragging between all 4 parameters in the opposite tables, the result is 1 relationship that forks on both ends to the respective 4 parameters.
This would be easy I guess, if the fields were perfectly matching. I think that then there would be only one single related record with my Outcome filed ready for the picking! But the reality is that some of the parameters first need to be calculated upon and are stored in variables.
With 'Query' you mean the ExecuteSQL() right? I've worked with it once and have no routine as yet, but I'm determined to learn! :-)
Thanks for the tip. I understand that the 'Remote IDE' is a form of external SQL script developer. I'll keep it in mind. But I don't plan to get beyond the really beginners level for now. So I work in the Script Workspace and Specify calculation window. Which I have full screen :-)
I actually just flipped back to the browser to find out how to exactly use ExecuteSQL() when I saw your messages :-)
'll keep you posted. T.
Yes, T! SQL = structured query lanquange
As my post mentioned, FIND could be done to get the necessary values by passing variables between tables. But the query (SQL statement using ExecuteSQL function) is much easier and more flexible.
Bev -- it's interesting to note that aside from the universal agreement that "SQL" means "Structured Query Language", it apparently didn't start out that way.
In Alan Taylor's (now ancient 1995) book "SQL For Dummies", he makes this point by showing the history of SQL and how it came to be and, most importantly, that SQL really didn't stand for ... anything.
It's all a moot point now, but just say'n.
Sort of like the "meaning" of PHP or JDBC. moot moot! LOL
-- sent from myPhone --
Moot moot good mood! Nice.
... I came up with this so far, but it just yields only questionmarks.
SELECT OutcomeField1 FROM TableB
WHERE Parameter1 = $Parameter1
AND Parameter2 = $Parameter2
AND Parameter3 = $Parameter3
AND Parameter4 = $Parameter4
"; "" ; "" )
(Before this scriptstep I set the variables $Parameter1, ...2, 3 and 4 of course).
Am I basically on the right way, and it is just one of those common date-text-number mismatch or so silly things, or am I doing it all wrong?
Mike's initial response had this long ;TableA::Parameter1;TableA::Parameter2;TableA::Parameter3;TableA::Parameter4 string behind it. That I can't seem to place, so I omitted it so far.
By the way, there is never a situation that 2 matches are found. Each parameter combination is unique.
It's tough to find tutorials or youtube vids that show this kind of beginner-level stuff.
I'll keep going.
Try this, the ? marks are placeholders for variables you pass at the end of the function. What you posted will not work.
SELECT OutcomeField1 FROM TableB
WHERE Parameter1 = ?
AND Parameter2 = ?
AND Parameter3 = ?
AND Parameter4 = ?
"; "" ; "" ; $Parameter1 ; $Parameter2 ; $Parameter3 ; $Parameter4 )
The ? marks are evaluated in order, so you can place as many question marks as you need and then pass in the variables in an unending semicolon delimited list at the end of the function.
The advantage towards passing in variables is that filemaker takes care of properly formatting the strings, EG if you have spaces, or date/times, it will auto-cast them into the proper SQL syntax for you. Nifty!
Yeeeeeey! It's working!!!
My day is totally good. Thanks to you all!
Have a VERY nice weekend!