Executing SQL against tables

[Mel_WI]

My apologies if this is in the wrong area...I am very new to file maker but have extensive database/data modelling/reporting experience with other larger platforms.

I have a couple things that I am trying to do in File Maker, and not sure if they are possible, or how to do them:

1. I have a new related table that I would like to populate. In general I can do this quickly with a sql statement, but how do I execute this SQL statement against the File Maker database? I see that you can write a SQL to execute against an ODBC/JDBC source, but I just want to execute it in the database that I am working in. A sample of the SQL:

Insert into (new table) select column1 from existing table

2. What is the best way to prompt a user for inputs? Example:

Pull all the records from table where a date range is between start date and end date. Where the start date and end date are being input by the user.

3. Again...SQL...

How can I perform a delete...

Delete from TableA where column1 = "Delete"

Sorry if these are trivial. I have figured out some things on my own, but some of the things that I have done with other products...seem to not funtion the same way in FileMaker.

Thanks in advance for any input.

[mr_vodka]

Because FileMaker is so different than some other relational DBs it may be a steeper learning curve for you to "unwire" your normal thought process. FileMaker is very context based and therefore layouts and table occurrences play an important part in understanding FM.

1. I have a new related table that I would like to populate. In general I can do this quickly with a sql statement, but how do I execute this SQL statement against the File Maker database? I see that you can write a SQL to execute against an ODBC/JDBC source, but I just want to execute it in the database that I am working in. A sample of the SQL:

Insert into (new table) select column1 from existing table

This can be done by being on a layout of the orig table's table occurrence and then putting the child record field on the layout. If your relationship allows for creation of record on the child side, then all you have to do is type in a value into this related field and it will create the related child record for you.

2. What is the best way to prompt a user for inputs? Example:

Pull all the records from table where a date range is between start date and end date. Where the start date and end date are being input by the user.

A user can do a direct find in a layout that you create for the table. Have the user enter find mode. Then in the date field, they can enter a range such as 1/1/2009..4/23/2009. Next in a field which has a auto-enter or creation account name defined, they can put in the name(i.e. John). Now when the find is executed, it will return all records created by John between the first of this year and today the 23rd of April 2009.

BTW this find process can also be scripted.

3. Again...SQL...

How can I perform a delete...

Delete from TableA where column1 = "Delete"

Just do the find for "DElete" in colum1 and delete the record with the delete record command.

Again this can be scripted if need be.

Finally, this product may interest you.

http://www.myfmbutler.com/index.lasso?p=425

[Mel_WI]

Because FileMaker is so different than some other relational DBs it may be a steeper learning curve for you to "unwire" your normal thought process. FileMaker is very context based and therefore layouts and table occurrences play an important part in understanding FM.

[color:blue]Definitely Understand this!! It is a different way of thinking/developing...Looking forward to the challenge...

This can be done by being on a layout of the orig table's table occurrence and then putting the child record field on the layout. If your relationship allows for creation of record on the child side, then all you have to do is type in a value into this related field and it will create the related child record for you.

[color:blue]Does this mean you have to do this for each record. If you have 100 records in the existing table you have to add the field for all 100 child records?

A user can do a direct find in a layout that you create for the table. Have the user enter find mode. Then in the date field, they can enter a range such as 1/1/2009..4/23/2009. Next in a field which has a auto-enter or creation account name defined, they can put in the name(i.e. John). Now when the find is executed, it will return all records created by John between the first of this year and today the 23rd of April 2009.

BTW this find process can also be scripted.

[color:blue]Do you have an example of how this would be done in a script?

Just do the find for "DElete" in colum1 and delete the record with the delete record command.

Again this can be scripted if need be.

[color:blue]Do you have an example of how this find/delte would work in script?