Jump to content

ExecuteSQL Function to Duplicate records


This topic is 3056 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Yilbber Vargas
Link to comment
Share on other sites

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 by Lee Smith
added where to find the link for Profile
Link to comment
Share on other sites

 

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.

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

 

 

 

 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 ?

hell4.PNG

scripthell3.png

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 3056 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.