Let's say some of the data details are stored in another filemaker file.
How can I query another filemaker file to lookup that data?
The first question should really be. Do you have a relationship between the two files?
If So, Read On.
To lookup data from another table you can set your field entry options.
You need to have a relationship between the two files. So like in the case of an Invoice Line Items table you might have ProductID that relates to a productID in your products table.
You select the table where you want the data to go and the the next table is where the data should come from. Once you have selected the table where the data will come from you can select the actual field that has the data you want to lookup. Hopefully this makes sense.
If there is no relationship, then you would need to create one using the Manage External Data Sources to allow you to connect to the external file. Then establish a relationship between the two files. Let me know if you would like further explanation on that front.
Not everyone uses "look up" the same way.
There are two basic approaches to such a "query". The first method shown here is what we normally call a "look up" and may be exactly what you need. Auto-enter calculations selected on the same auto-enter tab can also copy this data over. But both options copy the data from a record in one table into a record of another. If the original record is later updated, the copy thus created by this look up will not automatically update to match. That can be exactly what you need or it can be what you don't want.
The other method is to use the same relationship used for the classic "look up" shown above, but to simply add the fields from the related table occurrence to your first table's layout. This then dynamically displays a single copy of the data in potentially many different related records of the second table. Change this data in the original table and all these "views" of the data also update to show the new changes.
Which is the best approach depends on what you need to accomplish with this data.
Thank you, PeterCross and philmodjunk
Both of the replies are very helpful.
Can I also query the other file using script instead of just lookup? For example, if I want to find certain value from another file?
You are welcome nexgen. You could do a lookup by script instead of calculation. That way you could work in logic if there are multiple matches and such. The details of that would really depend on what you were wanting the behavior to do. For example you could have a script trigger run when a value is put in a field and if there is one match the data could be copied into the field. If there was more than one match the user could be prompted to make a selection. It depends on what you would like the script to do. All possible. though.
There are many ways. Depends on what you want to so with the results of your query.
This produces a found set of records. If you want to do something with that data back in the original context, your script may have to collect data into one or more variables to use back at the original layout. You can use Exist Script to pass this info back to the original file as a script result if you need to or you can use an external data source reference as recommended earlier and just use a variable as you are now staying all in the same file.
On the other hand, ExecuteSQL is a function that can be used to pull a bunch of data into a single field or variable.
ExecuteSQL is a great function to pull data. There is a bit of learning to do though. You will have to detect if you get valid data or one or more matches from your search and check to make sure you get a valid result.
you could write a statement like this
If Count = ? there is a problem with your query (either no results or a bad query). If Count =1 you have one Match so you can use the result of
and if you get more than one match you have to deal with that. Depends on how much you want to take on. I like the more FileMaker approach when learning as you get a better feel for how the app behaves.
If Count = ? there is a problem with your query (either no results or a bad query).
Actually, a question mark means that there's either a syntax error or your results are too large to fit in the field. (in the second case, if you click into the field, you see the question mark replaced by the value returned.)
No results will produce either 0 or null, I forget which.
ExecuteSQL does have a steep learning curve in FileMaker unless you are already familiar to you because you've used it in other systems and there are third party tools that can make building and testing your queries a bit easier. (I test most of my queries in the data viewer, but most of my queries are kept pretty simple too.)
Retrieving data ...