Jump to content
Sign in to follow this  
Mel_WI

Executing SQL against tables

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

  • Similar Content

    • By Scott Pon
      Environment: FM13 with FM13 Server, mix of Windows 7 and 10.
      Is there a way to set a script trigger on if this portal row is new? IE, a script to run if this new child/portal record is new.  
      We have a parent record, and portal to Children records.  The children records have 2 fields: Profile Name and Process type.  Our user would like to enter a new child record (profile name and process type).  if the process type already exists, we will need to archive the existing record (matching the profile type).  There is more bI want to start with this first.
      I see script triggers to the layout "OnRecordCommit", but no similar script trigger for portals.  Any ideas on how to handle this?  Or am I going to have to add a button to go to another screen to accomplish this?
      Thanks.  I hope i gave enough info for you to help me.
       
    • By dav1089
      Hello,
      I want to create a script trigger which opens a layout based on account name ,create new record and put cursor in first field of the new record
      Now, I was able to direct user to specific layout and also created new record using New Record/Request function but somehow it doesn't put cursor in the first field , instead it selects whole record .
      Note: I also have script triggers attached to first and second field which runs on exiting the fields. (I don't think they affect anyways , but just mentioned to give idea)
    • By CRexen
      Hello everyone
       
      I am currently working on a database, which HR-Managers can access via WebDirect using login-information, to accede or resign their employees.
       
      I have made 6 possible option types for the HR-employees to enter data into, set by an unique ID, but with the use of the same fields.
       
      So...
       
      X fields with option type 1 (Accede)
      Y fields with option type 2 (Resign)
      Z fields with option type 3 (Maternity leave / leave)
      .. Etc. 6 option types in total.
       
      Now, based on my project description, a couple of the fields will have to be validated / not empty, which the HR-manager is prompted to enter before the record can be committed.
       
      Now, I am aware of FileMaker has it's own Field Validation, but I think the built in feature is bonkers..... (Sorry FileMaker..) 
       
      Therefore I was thinking of making a script to each of the different fields, which needs validation, and is this even a good solution
       
      Question one; how do I do this, the most easily? Is it possible?
      I have 8 required fields for validation, fields are global.
       
      Fields such as; Company Name, HR-manager name, HR-manager e-mail, employee SSN, employee name, employee surname etc. 
       
      I don't want the scripts to interfere with the other option types ( X, Y, Z ) which each is set by an unique ID, as well as fields, although the fields used in these option types are from the same table.
       
      Please see attached images of my setup.
       
      Hope you can help me, for the best possible solution.
      If you need more information, let me know.
       
      /Cheers
       
      P.S. The language/text on the layout is Danish, although shouldn't be considered an issue for understanding my problem.



    • By Dana G
      Purpose:  To capture the contents of a field before it is changed.  Compare the before to the after.  Highlight the after and screen capture then export.
       
      I can't get past the first sentence and it's getting frustrating because it seems extremely simple.
       
      1 Field:  OnDeck_t (text)  -  The OnDeck_t field data = On
      2 Field:  OnDeck_before (Where I want to capture the OnDeck_t before it's changed)
       
      I have an OnObjectEnter script trigger on the OnDeck_t field that runs this script:
      Set Field By Name [substitute (Get (ActiveFieldName); "_t"; "_Before"); Get (ActiveFieldContents)]
       
      If I do this it works fine.  (But I don't want it hard coded.  I will be using it for all the fields on the layout):
      Set Field [OnDeck_before; Get (ActiveFieldContents)]
       
       
      I have divided the Substitute (Get (ActiveFieldName); "_t"; "_Before") and Get (ActiveFieldContents) into separate lines in Data Viewer and both return expected results.  Why doesn't it work when it's part of a 'Set Field by Name' script step?
       
      Thanks.
    • By scoony
      Hi,
       
      I'm new to the forum so apologies if I have put this question in the wrong section.
       
      What I am attempting to do: I want to create a 'home page' that each consultant sees when the database opens on an iPad. The home page should have two pop-up menus, Client and the other Contact. For context, each Client with have multiple Contacts (generally employees and contractors) The consultants in the office want to be able to navigate to a Client they select from the pop-up menu on the 'home page'. The same process applies to the Contact pop-up menu. 
       
      Many thanks if anyone has ideas how to achieve this. 
×

Important Information

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