ExecuteSQL Function to Duplicate records

[Yilbber Vargas]

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, 2015 by Yilbber Vargas

[Lee Smith]

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, 2015 by Lee Smith
added where to find the link for Profile

[Yilbber Vargas]

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

[Wim Decorte]

 

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.

 

[Yilbber Vargas]

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.

[Lee Smith]

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

 

[beverly]

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.

 

 

 

 

[Yilbber Vargas]

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.

[Yilbber Vargas]

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.

 

[beverly]

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

[Yilbber Vargas]

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 ?

[beverly]

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

[bruceR]

Or use the create related record method.

BackupTest.fmp12.zip

[beverly]

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

[Yilbber Vargas]

Thanks, I will take a look at this

 

[Yilbber Vargas]

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