Import both sets of records into the same table.
No... I don't want a reduntant data. I need only a report as a query in Access that show The merged transaction order by date.
If it's an Import, the second table has a constant/literal that would need to be supplied after the import:
But is it an import? is the data already pushed into FileMaker (from Access) and now a report is needed?
pagamento is an alias.
Its a new database (that exist now on Access).
from two tables orders and payments i need a report for a customer that merge transaction from both tables
FileMaker can't do a Union. It can use import records to combine data into one table for report purposes. The table can be one that is regularly purged if you want it only used for reporting, but if you want a Union type join, it's really the only way to do that with native FileMaker features.
OK. Let's clarify that!
1) FileMaker SQL plug-in can UNION
2) FileMaker function, ExecuteSQL() can UNION
3) FileMaker as ODBC source can UNION
4) FileMaker Import using SQL (external ODBC source) can UNION
But these may not help with Access external source...
thanks beverly... i've seen....
but I think now that filemaker isn't SQL-CENTRICO. for this purposes best is Microsoft Access and (of course) MY SQL, Microsoft SQL eccetera.
It is too difficult to have a simple UNION JOIN result that in Access I can have with a couple of query and sub-query
so sad ....
1 of 1 people found this helpful
I have lately faced the same problem. You are right, Fillemaker is not so much SQL friendly, at least for two reasons:
(1) native FM SQL engine within the filemaker DB itself is unable to alter table data, hence any query result is difficult to work with (ie. a separate script is needed to write up the results into a destination table)
(2) even if the previous point is worked around, the results need to be updated manually every time the DB changes (ie. no such automatic update exists as per with table occuarances)
For point (1) the easiest thing is to use this plugin
that enables you to use an extended SQL command set, so that you can not only read but also write table contents.
In practice this means that you can use the 'INSERT INTO' sql statement for your database and you can combine it with the 'SELECT' and 'UNION SELECT' statements. By the end of the day, within just two script lines you will be able to directly select the necessary records from any desired number of tables, join them AND write the results into a destination table.
(Be aware that the destination table needs to exist with the given structure suitable to treat the joined records. Once your destination table is generated, it is usually a good idea to run a 'DELETE' SQL statement on the table before you add the new records.)
If you need more help on how to carry out such SQL querying from a Filemaker script, the easiest thing to do is to read the help file on the plugin's home page.
For point (2) depending on the purpose of your 'joint' table, you would need to either fire the script if any record changes that results in a change in your destination table, or – more resource friendly – fire the script only if the report/query would need to be generated/run. E.g I manually fire the joining SQL script whenever the layout is opened that is based on the destination table in question.
Sorry if I was too lengthy but hope it made sense