Yes, you need to find and save or export each group of records by area code. No, this shouldn't take 20 hours if you use a script to do this for you instead of doing it manually one group at a time.
But you just bought FileMaker. Are you ready to try your hand at a script yet?
yeah I'm ready. I originally wrote a similar program in PHP using MySql, but our server couldn't handle the massive data. My work purchased me a new iMac and I *was* going to use Access but that isn't available and someone told me about Filemaker. To be quite honest - so far it seems to be much, much more intuitive than Access is. And more powerful.
So - in regards to scripts - I'm *guessing* it uses a better scripting language than VB. (Hopefully. I know PHP. I hate VB)
I went into scripts, created a new one.
Some things I'm not sure about.
In PHP I could create an array for the area codes. In this - do i need to specify each area code the script will go through? or is there another way to do it?
Also - how would I tell it to save each file with a unique name?
This is a script that will cover a number of concepts. Please look these steps and functions up in help to learn more about them.
First to get that list of existing area codes:
We'll use a little trick--a value list can be defined that pulls its values from the index associated with your area code field. Hmmm, you might just have a simple phone number field and need to define a calculation field that returns the area code to set up a such a field. The format and content of your phone number field may make that simple or complex.
Once you have such a field, select Manage | Value Lists, create a new value list (I'll call it Area Codes) and select the Use Values from a field option. Select the Area code field as the field that will be the source of values for your value list. Since this type of value list will list one instance of each unique value in the field, we just need to get that list and loop through it to find and export our records by area code.
Set variable [$AreaCodes ; value: ValueListItems ( get ( FileName ) ; "Area Codes" ) ]
Set Variable [$K ; value: $K + 1 ]
Exit Loop If [$K > ValueCount ( $AreaCodes ) ]
Enter Find Mode  ---> Clear the pause check box
Set Field [ YourTable::AreaCode ; Getvalue ( $AreaCodes ; $K ) ]
Set Variable [$Path ; Value: "file:" & Get ( DesktopPath ) & YourTable::AreaCode & ".xls" ]
Export Records [No dialog ; $Path...
For more examples of scripted finds: Scripted Find Examples
For more about file paths in FileMaker: Exploring the use of a $Path Variable in Scripts
Thanks for that. I appreciate far more than you know.
I have a question though - it works fine, as in sorting all the data one step at a time. But a dialog keeps popping up that says, "No fields were selected for export". And there aren't any files saving.
Attached to this is a screenshot of the script.
Any idea on what I've done wrong here?
It sounds like you haven't specified all the needed options for the export records step.
When you click that step to select it, two buttons appear at the bottom right. I would guess that you need to click the bottom button: Specify Export Order and specify the fields that you want to export.
You also need to click the upper button and type in the name of your variable: $Path.
What do you mean type in the name of my variable $Path? Isn't $Path the name of that variable?
Yes, but you need to:
- Click the Export Records step to select it.
- Click the button next to "specify output file" and...
- Type $Path into the Specify Output File dialog that has now opened.
When you have done that, $Path will appear in the brackets to the right of Export Records---which is how I can tell that you have not yet done this.
Ahhh. I owe you a beer, chicken, tabasco sauce, milk or whatever floats your boat. I think I've had my long-awaited-epiphany.
Let me see...