I've been reading up on FM joins via execute SQL

I want to be able to roll back and forth through changes in my 2nd table, and only record the  fields listed in table 2.

I believe I can do that with an Outer join between Table A and Table B.

What is the proper ExecuteSQL command for this ?

This script will Trigger when ever a commited record on Table A is performed. ( i think this is the right way to have it work in webdirect, if not, please tell me which is the correct method)

Also, I want to Highlight the change between last record, This part I can figure out, i believe using a change since last record.

Ultimately, there will be a layout where the Current Version of record A on Table A is viewed where Record A on table B is in the portal, and if one desired it, The Row is brought into table A again.

 

My next  question so far is Can ExecuteSQL be used to copy Record A back into Table A as well or will i have to write a cumbersome script to roll back?

My other question is, when I delete my records in table A , how do they show up in table B in this method?

 

Edited October 21, 201510 yr by Yilbber Vargas

Please complete your Profile so we know what version of FileMaker, Operating System, Platform and your Skill level

 

You have access to this using the drop down list next to your picture and name at the top Right side of your browser window.

Edited October 21, 201510 yr by Lee Smith
added where to find the link for Profile

I was initially running under osx, but have switched to running on windows 10.

 

My next  question so far is Can ExecuteSQL be used to copy Record A back into Table A as well or will i have to write a cumbersome script to roll back?

 

 

ExecuteSQL() calls can only do SELECT, they can not do INSERT or UPDATE to create or write data.  So you would still need to use regular scripting techniques to create records and set fields.

 

I know i am doing something wrong, and I've wasted 2 days on this, What is the proper way of doing this style of script?. also FM14 seems to have supported more SQL commands since 12, but that's just fro their literature.

Actually, there are several demos of Execute SQL that you might want to download and watch the accompanying videos. Do a search for Execute SQL and you will get a list. Watch the videos and follow the links to the editors site.

There are example files in several places, this was one of the first ones if I recall

There is several others , start with these two, 

http://www.modularfilemaker.org/module/query-builder/

or do a search for others like this.

https://www.youtube.com/results?search_query=Execute+SQL++in+FileMaker

Lee

 

and this one says "12", but is still valid  - get the PDF and the sample files, not just read the blog:

Missing FM12 ExecuteSQL Reference

for information on the new clauses (FM13 & FM14) for ExecuteSQL:

Best of FM13 SQL

If, OTOH, you need information on xDBC functions in FM (as ODBC Source):

FileMaker 14 ODBC and JDBC Guide

and/or the script steps for Execute SQL (NOTE: this is the script step for INSERT, UPDATE, DELETE to FM as ODBC Source):

Execute SQL

Beverly

 

p.s. your above script does not appear to have any "SQL", perhaps this is the wrong forum topic? The "transactional" feature of FM has nothing to do with SQL, if that's what you are asking.

 

 

 

 

I ended up taking them out, in a attempt to Clear it, but I will check out these sources. Maybe I'll finally get the parlance right.

I used Seedcodeexplorer's SQL explorer is creating Code that i can use just fine.

I still need script portion that will Get the field data from the List C (Which is all the fields I want to copy from ) Table A to Table B.

before I could use get activefieldcontent but that isn't going to work anymore.

 

please post your script and query. What is in "List C" at this point? If you made a query that returns "many fields" all values are in a delimited list in a TEXT FIELD. You cannot take that list 'as is' without parsing to push the values into somewhere else. Perhaps that's the 'disconnect'?

I suggest you get the FileMaker Training Series - Advanced to see how they take a query and push it into a "Virtual List" temporary table. from there you can push into your "real table". Or study it enough to know how to get the values directly into your other table.

beverly

I've got the virtual table setup... but i'm somehow just not seeing how to turn the properly bult fields into the right loop structure to automate the data. I am getting lost on how to translate The fields in my table to useful information. I want the script to Grab the variable (now located in Virtual list utility (fields) under (events) and copy that field to BACKUP.)

commands like Getfield and getfieldname Just do not want to work here.

I am guessing I am missing a Set field by name, go to next field, loop, and deleted my previous attempts. how should i write this ?

I tend to use variables. Loop the records to "push" and set variables. Go to the layout where they are to added (new window), create new record, add from the variables, close window go back to the original layout, next record (repeat)... end loop.

HTH,

Beverly

Or use the create related record method.

BackupTest.fmp12.zip

absolutely! Bruce's example is another great way that I will use.

Thanks, I will take a look at this

 

I had this working before, but I need the entire record copied not just the last record.