SQL equivalent Scripts?

[Lloyd709]

SQL equivalent Scritps?

 

I’ve recently moved to Mac and purchased Filemaker to manage my customer database. However, I’m finding it very difficult to work out how to do the bread-and-butter type data manipulations I used to easily do in Access SQL. I’ve searched on the web for information along the lines of ‘Filemaker for SQL users’ but can’t find anything.

 

For example, at the moment I want to create a new table from a subset of records from an existing table. The SQL for such an operation would be along the lines of:

 

CREATE TABLE AS

SELECT , , ….

FROM

WHERE = xyz;

 

The only way I seem to be able to do this is to export the selected records and then re-import them into a new table. If someone can help me achieve this using a script (without exporting and importing) I think it would give me a lot of clues how to achieve the more complicated stuff I used to do in SQL.

 

Thanks

[AHunter3]

Import directly into the database FROM ITSELF from the source table, creating new destination table.

 

Now, question: Why would you want to do that on any kind of regular basis? Creating a new table is like having a new baby. It's a nontrivial structural modification.

[Lloyd709]

Now, question: Why would you want to do that on any kind of regular basis? Creating a new table is like having a new baby. It's a nontrivial structural modification.

 

I appreciate the question but it's just the way I work - I come from an analysis background (involving large amounts of data i.e. census , not a programming background. I've always used databases as data manipulation tools. Even now, working on my own small marketing database, I'm not really creating a system. I'm gathering data from lots of sources, summarising it, matching it, cleaing it etc. Essentially massaging it into the most appropriate form which usually involves creating lots of tempoary tables.

 

Thanks for your reply to my question - I'll take a look.

[AHunter3]

OK that's reasonable. You may wish to export to a FileMaker Pro file which you can then play with (creating scripts, relationships, etc) instead of moving a subset of your data to a new table in the main/regular file, which is going to get cluttered up with "purpose du jour" tables that you only needed for temporary purposes. It's not like you'd have structure such as scripts, relationships, etc set up in the existing system, because you would not, none of those could reference the new table until you cause it to exist, so you're looking at creating analytical structure no matter what. In fact, if you export and then cleverly rename your table occurrence in the new file to an EXISTING name of a table occurrence in the original, you can then copy & paste scripts from the live system and they should "just work" in the exported dataset just because it uses the same table occurrence name.

[Lloyd709]

Thanks. I'll follow your advice.

 

I've just tested your FROM ITSELF tip and it nearly worked except I always end up with all the original fields - even if they are not populated - which makes it a bit messy.

 

As you have probably gathered, I'm a novis at Filemaker so please excuse the question, but is there anyway or writing scripts using the good old typing them out method rather than the point and click interface? I like to just bring up my code, modify it and re-run it - which I can't seem to be able to do with Filemaker scripts!

[AHunter3]

I spend 85% of my day in the ScriptMaker and go for long periods without touching the mouse.

 

You're on a Mac — good. The keystrokes are just plain easier on a Mac.

 

Create a new script. It opens blank on the right, menu of available script steps on the left.

 

Hit tab twice. That moves focus to the right, the menu of available scriptstep items.

 

Quickly type the first few chars of whatever script step you want. (If it stops at a similarly-named script step higher up the list, hit downarrow once and repeat the typing of first few chars) Once the correct item is selected, hit spacebar to add it to your script. If it requires elaboration ("Specify"), hit the spacebar to bring up the dialog box that lets you specify. If it has two elaboration options, as Set Field most famously does, use Ctrl-Option-Spacebar to open the second (lower) one, the Specify Calculated Value dialog. Within the Specify dialog you can switch focus using the tab key but more to the point, once you know your way around the FileMaker functions and your own db's structure you can just freehand everything in anyhow. (A good reason to avoid Naming Conventions From Hell that you could never tap out freehand). The OK button to accept your entry will generally respond to the keypad ENTER key. Tab twice to get back over to your menu of availbable script steps. Repeat process.

 

I am a QuicKeys user myself and have a few custom keystrokes, like Control-Option-D to duplicate an existing script step.

 

You don't need QuicKeys to reposition a script step within the list of script steps though: select it by arrowkeying your way up or down to it, then Command-downarrow it to move it down or Command-uparrow it to move it up. Delete key to get rid of a script step you do not want.

 

After awhile you get to the point you can bang out scripts faster than you can type their equiv here on the message board, and sometimes you don't even have to look at your screen.

[Lloyd709]

Thanks for taking the time to describe this - I followed everything through as you described and it does indeed work very well.