1 of 1 people found this helpful
So here are the questions:
1: Do I need a "report" type layout, or a "list" type layout, or doesn't that matter?
2: I guess I base the layout on the communications table, then include fields from the linked Members table, right?
3: Once I have the layout designed and sorted / filtered, I'm guessing I can simply script the export, right?
1. In most cases, there's no difference … Anyway, it doesn't matter since you can explicitly specify to export the current record or the found set, regardless of the view you employ (form, list or table).
2. If you want to create one row per Comm record – yes.
3. Yes. I recommend you take some some long, hard looks at the available script steps* to get a feeling for what is scriptable in FileMaker (short answer: just about everything).
Not to forget the capability of executing shell scripts, and AppleScripts in OS X, which basically opens up much of the OS, and, at least on the Mac, many other applications. Not sure if you could maybe even execute VBA scripts to postprocess your Excel files. (Otherwise, get a Mac … )
* For example here: http://fmhelp.filemaker.com/fmphelp_13/en/html/help_script_cat.34.1.html#947319
Since you are apparently changing your work process you probably should also be asking whether you still need the export to Excel process, or whether you can achieve the same purpose within FM itself. If the export to Excel process was used as a convenient way of producing reports, then I doubt you need to do it at all now. You would get better value for your time learning how to produce FM reports than learning how to export, in my opinion.
Thanks, @jlamprecht. This does seem simple. I guess the challenge is to create a list-type layout that has the exact records and fields that I need, then go to it and export the records, via script steps.
Appreciate the answer.
Thanks, @keywords. I am getting fairly good at making FM reports. Unfortunately, in this case I still need the Excel export. This is for a club management database, where we are all volunteer labor. I am in charge of handling all membership tasks, and we have another member who handles all of the emailing to members. Every so often, when members have been added or deleted, or email addresses change, he needs a new list of members and addresses, both physical and email. He imports the Excel I give him into his Outlook. Therefore, things need to stay the same in that regard.
Thanks for the reply, however.
No problem. I said "you probably should also be asking whether you still need the export to Excel process". Clearly you do. All the best.
Thanks, @erolst. As usual, concise and to the point.
The last paragraph bears answering: As mentioned in my other answer, I do need to export an Excel workbook of the proper records. Other club members who use that Excel don't have access to my FMP application. I have started playing with both shell scripts and AppleScripts called by my FMP app on my Mac. In Access I used VBA to query a recordset out of my Access DB, then automate Excel to produce the output workbook file. As long as I can properly prepare the right records on a new list-type layout, the export script step should work.
I think all I need to do now is create some extra calculated fields, because the Member records have 2 sets of names, for member 1 and member 2. The related Communications table has multiple records, one for each person. There is a description field in there, containing the person's name. So I think my calculated name field needs to make a decision based on that description field (which usually contains the correct person's first name), so it knows which first name and last name fields to use from the related member table. This should be fairly simple.
Thanks for pointing me in the correct direction.