Can If handle range of numbers
|
|
| 1 2 |
JimG_EasternMA
|
In reply to this post
by Tom Elliott
I have a file in which every record contains at least one name,
15-20% of the records contain two names, and 1-2% of them contain three names. There's some header info in each record along with the one, two, or three names, but to keep things simple for me (and preserve my investment in time so far) I'd rather keep this data structure than use a header/detail structure. I need to run through a set of records and export one record (i.e., one line) to a CSV file for each name that has a non-blank address field. So each source record will produce at least one output record, some will produce two, and a few will produce three. The CSV export file is the same for all of the names in all of the source records. I've tried doing this using ScriptMaker and the options in the Export Records dialog boxes, but not all names from each record are being exported. My script looks like this: Enter Browse Mode [] Go to Record/Request/Page [First] Loop If [PrPr::PR1Addr <> ""] Export Records [No dialog; "PrMergeExport.csv"; Unicode (UTF-16)] End If If [PrPr::PR2Addr <> ""] Export Records [No dialog; "PrMergeExport.csv"; Unicode (UTF-16)] End If If [PrPr::PR3Addr <> ""] Export Records [No dialog; "PrMergeExport.csv"; Unicode (UTF-16)] End If Go to Record/Request/Page [Next; Exit after last] End Loop It seems like it'd be better to set the output filename once, before the loop ... but how? Once again I find myself wanting to edit the code more directly, but I'm starting to realize that usually isn't possible. Any help or suggestions appreciated ... thanks in advance. Jim Guinness East Central Massachusetts, USA _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
Horton Tom
|
Jim,
I had a similar problem creating a import script for a solution so I could upgrade a client when a new version of the database was released. The export file name in the Exports Records script step can be defined in a calculation. So all you have to do is define a variable like $FileName once before the loop and reference it in each of the Export Records step's output file option. A Variable is listed as an option for defining a filename, it's at the bottom of the examples listed when you are prompted. Is that what you were looking for? Tom Tom Horton | Systems Engineer ____________________________ Silkwood Solutions Inc. 4130 SW 117th Ave. #171 Beaverton, OR 97005 p. 503.412.9967 f. 971.404.2728 e. [hidden email] w. www.silkwoodsolutions.com ichat. [hidden email] msngr. [hidden email] Apple Certified System Administrator Apple Certified Technical Coordinator Associate Level Member, FileMaker Solutions Alliance MacXprts Network Member On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: > I have a file in which every record contains at least one name, > 15-20% of the records contain two names, and 1-2% of them contain > three names. There's some header info in each record along with the > one, two, or three names, but to keep things simple for me (and > preserve my investment in time so far) I'd rather keep this data > structure than use a header/detail structure. > > I need to run through a set of records and export one record (i.e., > one line) to a CSV file for each name that has a non-blank address > field. So each source record will produce at least one output > record, some will produce two, and a few will produce three. The CSV > export file is the same for all of the names in all of the source > records. > > I've tried doing this using ScriptMaker and the options in the > Export Records dialog boxes, but not all names from each record are > being exported. My script looks like this: > > Enter Browse Mode [] > Go to Record/Request/Page [First] > Loop > If [PrPr::PR1Addr <> ""] > Export Records [No dialog; "PrMergeExport.csv"; > Unicode (UTF-16)] > End If > If [PrPr::PR2Addr <> ""] > Export Records [No dialog; "PrMergeExport.csv"; > Unicode (UTF-16)] > End If > If [PrPr::PR3Addr <> ""] > Export Records [No dialog; "PrMergeExport.csv"; > Unicode (UTF-16)] > End If > Go to Record/Request/Page [Next; Exit after last] > End Loop > > It seems like it'd be better to set the output filename once, before > the loop ... but how? Once again I find myself wanting to edit the > code more directly, but I'm starting to realize that usually isn't > possible. > > Any help or suggestions appreciated ... thanks in advance. > > Jim Guinness > East Central Massachusetts, USA > > _______________________________________________ > FMPexperts mailing list > [hidden email] > http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
JimG_EasternMA
|
Thanks, Tom. That helps, at least in that it speeds up execution
quite a bit. But it looks like defining the filename outside of the loop doesn't help my main problem. To reiterate my problem: I have a file with one, two, or three non-blank names in each record. I want to produce a line (record) in an outputted CSV file for each non-blank name. Each line will include data pertaining to that name as well as header data that's the same for each name. I'd rather not restructure this in a header-detail way since in other respects I think the costs of doing so would outweigh the benefits. My script below failed in a way that's rather too complicated for me to want to try to describe. Next I tried using a calculated field that consisted of the one, two or three sets of data for each record, separated by newlines. But FM's newline character didn't translate as expected in the output file. What I've done now is a bit kludgey, but I it works: Find all records with nonblank Name1, export their Name1 data to file A. Find all records with nonblank Name2, export their Name2 data to file B. Find all records with nonblank Name3, export their Name3 data to file C. Concatenate the three files together into the output file I need. Cheers, Jim Guinness At 8/7/2008 12:31 AM, you wrote: >So all you have to do is define a variable like >$FileName once before the loop and reference it in each of the Export >Records step's output file option. >On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: > >>I have a file in which every record contains at least one name, >>15-20% of the records contain two names, and 1-2% of them contain >>three names. There's some header info in each record along with the >>one, two, or three names, but to keep things simple for me (and >>preserve my investment in time so far) I'd rather keep this data >>structure than use a header/detail structure. >> >>I need to run through a set of records and export one record (i.e., >>one line) to a CSV file for each name that has a non-blank address >>field. So each source record will produce at least one output >>record, some will produce two, and a few will produce three. The CSV >>export file is the same for all of the names in all of the source >>records. >> >>I've tried doing this using ScriptMaker and the options in the >>Export Records dialog boxes, but not all names from each record are >>being exported. My script looks like this: >> >>Enter Browse Mode [] >>Go to Record/Request/Page [First] >>Loop >> If [PrPr::PR1Addr <> ""] >> Export Records [No dialog; "PrMergeExport.csv"; >>Unicode (UTF-16)] >> End If >> If [PrPr::PR2Addr <> ""] >> Export Records [No dialog; "PrMergeExport.csv"; >>Unicode (UTF-16)] >> End If >> If [PrPr::PR3Addr <> ""] >> Export Records [No dialog; "PrMergeExport.csv"; >>Unicode (UTF-16)] >> End If >> Go to Record/Request/Page [Next; Exit after last] >>End Loop >> >>It seems like it'd be better to set the output filename once, before >>the loop ... but how? Once again I find myself wanting to edit the >>code more directly, but I'm starting to realize that usually isn't >>possible. >> >>Any help or suggestions appreciated ... thanks in advance. >> >>Jim Guinness >>East Central Massachusetts, USA >> >>_______________________________________________ >>FMPexperts mailing list >>[hidden email] >>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > > > >_______________________________________________ >FMPexperts mailing list >[hidden email] >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
Bart Bartholomay
|
What version of FileMaker are you using?
Bart Bart Bartholomay HAB Marketing 3725 8th Lane Vero Beach, FL 32960 772.299.6352 http://HABMarketing.com On Aug 7, 2008, at 11:51 AM, Jim Guinness wrote: > Thanks, Tom. That helps, at least in that it speeds up execution > quite a bit. But it looks like defining the filename outside of the > loop doesn't help my main problem. > > To reiterate my problem: I have a file with one, two, or three non- > blank names in each record. I want to produce a line (record) in an > outputted CSV file for each non-blank name. Each line will include > data pertaining to that name as well as header data that's the same > for each name. I'd rather not restructure this in a header-detail > way since in other respects I think the costs of doing so would > outweigh the benefits. > > My script below failed in a way that's rather too complicated for me > to want to try to describe. > > Next I tried using a calculated field that consisted of the one, two > or three sets of data for each record, separated by newlines. But > FM's newline character didn't translate as expected in the output > file. > > What I've done now is a bit kludgey, but I it works: > Find all records with nonblank Name1, export their Name1 data to > file A. > Find all records with nonblank Name2, export their Name2 data to > file B. > Find all records with nonblank Name3, export their Name3 data to > file C. > Concatenate the three files together into the output file I need. > Cheers, > > Jim Guinness > > > At 8/7/2008 12:31 AM, you wrote: >> So all you have to do is define a variable like >> $FileName once before the loop and reference it in each of the Export >> Records step's output file option. >> On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: >> >>> I have a file in which every record contains at least one name, >>> 15-20% of the records contain two names, and 1-2% of them contain >>> three names. There's some header info in each record along with the >>> one, two, or three names, but to keep things simple for me (and >>> preserve my investment in time so far) I'd rather keep this data >>> structure than use a header/detail structure. >>> >>> I need to run through a set of records and export one record (i.e., >>> one line) to a CSV file for each name that has a non-blank address >>> field. So each source record will produce at least one output >>> record, some will produce two, and a few will produce three. The CSV >>> export file is the same for all of the names in all of the source >>> records. >>> >>> I've tried doing this using ScriptMaker and the options in the >>> Export Records dialog boxes, but not all names from each record are >>> being exported. My script looks like this: >>> >>> Enter Browse Mode [] >>> Go to Record/Request/Page [First] >>> Loop >>> If [PrPr::PR1Addr <> ""] >>> Export Records [No dialog; "PrMergeExport.csv"; >>> Unicode (UTF-16)] >>> End If >>> If [PrPr::PR2Addr <> ""] >>> Export Records [No dialog; "PrMergeExport.csv"; >>> Unicode (UTF-16)] >>> End If >>> If [PrPr::PR3Addr <> ""] >>> Export Records [No dialog; "PrMergeExport.csv"; >>> Unicode (UTF-16)] >>> End If >>> Go to Record/Request/Page [Next; Exit after last] >>> End Loop >>> >>> It seems like it'd be better to set the output filename once, before >>> the loop ... but how? Once again I find myself wanting to edit the >>> code more directly, but I'm starting to realize that usually isn't >>> possible. >>> >>> Any help or suggestions appreciated ... thanks in advance. >>> >>> Jim Guinness >>> East Central Massachusetts, USA >>> >>> _______________________________________________ >>> FMPexperts mailing list >>> [hidden email] >>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >> >> >> >> _______________________________________________ >> FMPexperts mailing list >> [hidden email] >> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > _______________________________________________ > FMPexperts mailing list > [hidden email] > http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
Lorne & Joan Walton
|
In reply to this post
by JimG_EasternMA
Jim, that one statement suggests that your structure is faulty.
Perhaps your task would be made easier if the multiple names resided in a related table? On Aug 7, 2008, at 8:51 AM, Jim Guinness wrote: > I have a file with one, two, or three non-blank names in each record. -- True terror is to wake up one morning and discover that your high school class is running the country. [Kurt Vonnegut] Lorne & Joan Walton, Maple Ridge, BC _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
Bart Bartholomay
|
In reply to this post
by JimG_EasternMA
There really needs to be more information. For example, is it your
intent to "replace" the set of records with this "new" concatenated set in the same table? Your kludgy solution below requires a manual concatenation outside of FileMaker. Do these three exported files "have" to be FileMaker files? If you can get away with using an Excel file (.csv) or Merge file (.mer), then it is possible to script the new set of records into a global field and export that to Excel. It will just be one record, but it will "look" and act like the full set, and by exporting to Excel it will open up as separate rows in a spreadsheet. It would be easy enough to bring that back into your FileMaker application as a new set of records. So you see, it would certainly help if you could be much more detailed in describing what you want to accomplish. Bart Bart Bartholomay HAB Marketing 3725 8th Lane Vero Beach, FL 32960 772.299.6352 http://HABMarketing.com On Aug 7, 2008, at 11:51 AM, Jim Guinness wrote: > Thanks, Tom. That helps, at least in that it speeds up execution > quite a bit. But it looks like defining the filename outside of the > loop doesn't help my main problem. > > To reiterate my problem: I have a file with one, two, or three non- > blank names in each record. I want to produce a line (record) in an > outputted CSV file for each non-blank name. Each line will include > data pertaining to that name as well as header data that's the same > for each name. I'd rather not restructure this in a header-detail > way since in other respects I think the costs of doing so would > outweigh the benefits. > > My script below failed in a way that's rather too complicated for me > to want to try to describe. > > Next I tried using a calculated field that consisted of the one, two > or three sets of data for each record, separated by newlines. But > FM's newline character didn't translate as expected in the output > file. > > What I've done now is a bit kludgey, but I it works: > Find all records with nonblank Name1, export their Name1 data to > file A. > Find all records with nonblank Name2, export their Name2 data to > file B. > Find all records with nonblank Name3, export their Name3 data to > file C. > Concatenate the three files together into the output file I need. > Cheers, > > Jim Guinness > > > At 8/7/2008 12:31 AM, you wrote: >> So all you have to do is define a variable like >> $FileName once before the loop and reference it in each of the Export >> Records step's output file option. >> On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: >> >>> I have a file in which every record contains at least one name, >>> 15-20% of the records contain two names, and 1-2% of them contain >>> three names. There's some header info in each record along with the >>> one, two, or three names, but to keep things simple for me (and >>> preserve my investment in time so far) I'd rather keep this data >>> structure than use a header/detail structure. >>> >>> I need to run through a set of records and export one record (i.e., >>> one line) to a CSV file for each name that has a non-blank address >>> field. So each source record will produce at least one output >>> record, some will produce two, and a few will produce three. The CSV >>> export file is the same for all of the names in all of the source >>> records. >>> >>> I've tried doing this using ScriptMaker and the options in the >>> Export Records dialog boxes, but not all names from each record are >>> being exported. My script looks like this: >>> >>> Enter Browse Mode [] >>> Go to Record/Request/Page [First] >>> Loop >>> If [PrPr::PR1Addr <> ""] >>> Export Records [No dialog; "PrMergeExport.csv"; >>> Unicode (UTF-16)] >>> End If >>> If [PrPr::PR2Addr <> ""] >>> Export Records [No dialog; "PrMergeExport.csv"; >>> Unicode (UTF-16)] >>> End If >>> If [PrPr::PR3Addr <> ""] >>> Export Records [No dialog; "PrMergeExport.csv"; >>> Unicode (UTF-16)] >>> End If >>> Go to Record/Request/Page [Next; Exit after last] >>> End Loop >>> >>> It seems like it'd be better to set the output filename once, before >>> the loop ... but how? Once again I find myself wanting to edit the >>> code more directly, but I'm starting to realize that usually isn't >>> possible. >>> >>> Any help or suggestions appreciated ... thanks in advance. >>> >>> Jim Guinness >>> East Central Massachusetts, USA >>> >>> _______________________________________________ >>> FMPexperts mailing list >>> [hidden email] >>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >> >> >> >> _______________________________________________ >> FMPexperts mailing list >> [hidden email] >> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > _______________________________________________ > FMPexperts mailing list > [hidden email] > http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
Richard DeShong
|
In reply to this post
by JimG_EasternMA
I use an "work" table in my solutions. It has a bunch of generic
text,date,num fields. To create a csv file, just use a single text field in each rcd. Loop thru your rcds and create 1-3 corresponding rcds in the work table - formatting the text field any way you want. When you're done looping, just export the single text field from your work table for all of your rcds. This technique allows you to create any line format you want - thus you're not limited to the export types provided by FM. It also doesn't matter what version of FM. In an FM6 solution, I use the techique to create "output.xml" files that can be opened directly in Excel. -- Richard DeShong, Athletic Study Center, UCBerkeley -----Original Message----- From: Jim Guinness Thanks, Tom. That helps, at least in that it speeds up execution quite a bit. But it looks like defining the filename outside of the loop doesn't help my main problem. To reiterate my problem: I have a file with one, two, or three non-blank names in each record. I want to produce a line (record) in an outputted CSV file for each non-blank name. Each line will include data pertaining to that name as well as header data that's the same for each name. I'd rather not restructure this in a header-detail way since in other respects I think the costs of doing so would outweigh the benefits. My script below failed in a way that's rather too complicated for me to want to try to describe. Next I tried using a calculated field that consisted of the one, two or three sets of data for each record, separated by newlines. But FM's newline character didn't translate as expected in the output file. What I've done now is a bit kludgey, but I it works: Find all records with nonblank Name1, export their Name1 data to file A. Find all records with nonblank Name2, export their Name2 data to file B. Find all records with nonblank Name3, export their Name3 data to file C. Concatenate the three files together into the output file I need. Cheers, Jim Guinness At 8/7/2008 12:31 AM, you wrote: >So all you have to do is define a variable like >$FileName once before the loop and reference it in each of the Export >Records step's output file option. >On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: > >>I have a file in which every record contains at least one name, >>15-20% of the records contain two names, and 1-2% of them contain >>three names. There's some header info in each record along with the >>one, two, or three names, but to keep things simple for me (and >>preserve my investment in time so far) I'd rather keep this data >>structure than use a header/detail structure. >> >>I need to run through a set of records and export one record (i.e., >>one line) to a CSV file for each name that has a non-blank address >>field. So each source record will produce at least one output >>record, some will produce two, and a few will produce three. The CSV >>export file is the same for all of the names in all of the source >>records. >> >>I've tried doing this using ScriptMaker and the options in the >>Export Records dialog boxes, but not all names from each record are >>being exported. My script looks like this: >> >>Enter Browse Mode [] >>Go to Record/Request/Page [First] >>Loop >> If [PrPr::PR1Addr <> ""] >> Export Records [No dialog; "PrMergeExport.csv"; >>Unicode (UTF-16)] >> End If >> If [PrPr::PR2Addr <> ""] >> Export Records [No dialog; "PrMergeExport.csv"; >>Unicode (UTF-16)] >> End If >> If [PrPr::PR3Addr <> ""] >> Export Records [No dialog; "PrMergeExport.csv"; >>Unicode (UTF-16)] >> End If >> Go to Record/Request/Page [Next; Exit after last] >>End Loop >> >>It seems like it'd be better to set the output filename once, before >>the loop ... but how? Once again I find myself wanting to edit the >>code more directly, but I'm starting to realize that usually isn't >>possible. >> >>Any help or suggestions appreciated ... thanks in advance. >> >>Jim Guinness >>East Central Massachusetts, USA >> >>_______________________________________________ >>FMPexperts mailing list >>[hidden email] >>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > > > >_______________________________________________ >FMPexperts mailing list >[hidden email] >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
Bart Bartholomay
|
Yep, that's where I was going, Richard. It can be done in a number of
ways, but essentially you're tricking FMP into exporting what would seem like multiple records when it may only be one or three, for example. Bart Bart Bartholomay HAB Marketing 3725 8th Lane Vero Beach, FL 32960 772.299.6352 http://HABMarketing.com On Aug 7, 2008, at 12:37 PM, Richard DeShong wrote: > I use an "work" table in my solutions. It has a bunch of generic > text,date,num fields. To create a csv file, just use a single text > field in > each rcd. Loop thru your rcds and create 1-3 corresponding rcds in > the work > table - formatting the text field any way you want. When you're done > looping, just export the single text field from your work table for > all of > your rcds. > > This technique allows you to create any line format you want - thus > you're > not limited to the export types provided by FM. It also doesn't > matter what > version of FM. In an FM6 solution, I use the techique to create > "output.xml" files that can be opened directly in Excel. > > -- > Richard DeShong, Athletic Study Center, UCBerkeley > > > -----Original Message----- > From: Jim Guinness > > Thanks, Tom. That helps, at least in that it speeds up execution > quite a bit. But it looks like defining the filename outside of the > loop doesn't help my main problem. > > To reiterate my problem: I have a file with one, two, or three > non-blank names in each record. I want to produce a line (record) in > an outputted CSV file for each non-blank name. Each line will include > data pertaining to that name as well as header data that's the same > for each name. I'd rather not restructure this in a header-detail way > since in other respects I think the costs of doing so would outweigh > the benefits. > > My script below failed in a way that's rather too complicated for me > to want to try to describe. > > Next I tried using a calculated field that consisted of the one, two > or three sets of data for each record, separated by newlines. But > FM's newline character didn't translate as expected in the output > file. > > What I've done now is a bit kludgey, but I it works: > Find all records with nonblank Name1, export their Name1 data to > file A. > Find all records with nonblank Name2, export their Name2 data to > file B. > Find all records with nonblank Name3, export their Name3 data to > file C. > Concatenate the three files together into the output file I need. > Cheers, > > Jim Guinness > > > At 8/7/2008 12:31 AM, you wrote: >> So all you have to do is define a variable like >> $FileName once before the loop and reference it in each of the Export >> Records step's output file option. >> On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: >> >>> I have a file in which every record contains at least one name, >>> 15-20% of the records contain two names, and 1-2% of them contain >>> three names. There's some header info in each record along with the >>> one, two, or three names, but to keep things simple for me (and >>> preserve my investment in time so far) I'd rather keep this data >>> structure than use a header/detail structure. >>> >>> I need to run through a set of records and export one record (i.e., >>> one line) to a CSV file for each name that has a non-blank address >>> field. So each source record will produce at least one output >>> record, some will produce two, and a few will produce three. The CSV >>> export file is the same for all of the names in all of the source >>> records. >>> >>> I've tried doing this using ScriptMaker and the options in the >>> Export Records dialog boxes, but not all names from each record are >>> being exported. My script looks like this: >>> >>> Enter Browse Mode [] >>> Go to Record/Request/Page [First] >>> Loop >>> If [PrPr::PR1Addr <> ""] >>> Export Records [No dialog; "PrMergeExport.csv"; >>> Unicode (UTF-16)] >>> End If >>> If [PrPr::PR2Addr <> ""] >>> Export Records [No dialog; "PrMergeExport.csv"; >>> Unicode (UTF-16)] >>> End If >>> If [PrPr::PR3Addr <> ""] >>> Export Records [No dialog; "PrMergeExport.csv"; >>> Unicode (UTF-16)] >>> End If >>> Go to Record/Request/Page [Next; Exit after last] >>> End Loop >>> >>> It seems like it'd be better to set the output filename once, before >>> the loop ... but how? Once again I find myself wanting to edit the >>> code more directly, but I'm starting to realize that usually isn't >>> possible. >>> >>> Any help or suggestions appreciated ... thanks in advance. >>> >>> Jim Guinness >>> East Central Massachusetts, USA >>> >>> _______________________________________________ >>> FMPexperts mailing list >>> [hidden email] >>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >> >> >> >> _______________________________________________ >> FMPexperts mailing list >> [hidden email] >> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > _______________________________________________ > FMPexperts mailing list > [hidden email] > http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > > > _______________________________________________ > FMPexperts mailing list > [hidden email] > http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
JimG_EasternMA
|
In reply to this post
by Lorne & Joan Walton
At 8/7/2008 12:10 PM, you wrote:
>Jim, that one statement suggests that your structure is faulty. >Perhaps your task would be made easier if the multiple names resided >in a related table? Yes that task would be, but everything else would be more difficult. >On Aug 7, 2008, at 8:51 AM, Jim Guinness wrote: > >>I have a file with one, two, or three non-blank names in each record. > >-- >True terror is to wake up one morning and discover that your high >school class is running the country. [Kurt Vonnegut] >Lorne & Joan Walton, Maple Ridge, BC > >_______________________________________________ >FMPexperts mailing list >[hidden email] >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
JimG_EasternMA
|
In reply to this post
by Bart Bartholomay
At 8/7/2008 12:28 PM, you wrote:
>There really needs to be more information. For example, is it your >intent to "replace" the set of records with this "new" concatenated >set in the same table? No, I need to create a CSV file. >Your kludgy solution below requires a manual >concatenation outside of FileMaker. Do these three exported files >"have" to be FileMaker files? Yes I thought I could do that from within the script. I'm used to Unix where that sort of thing is routine. >If you can get away with using an Excel >file (.csv) or Merge file (.mer), Yes that's what I want. >then it is possible to script the >new set of records into a global field and export that to Excel. Now THAT sounds interesting! Sounds like it could be just the thing. How is that done? >It >will just be one record, but it will "look" and act like the full set, >and by exporting to Excel it will open up as separate rows in a >spreadsheet. It would be easy enough to bring that back into your >FileMaker application as a new set of records. > >So you see, it would certainly help if you could be much more detailed >in describing what you want to accomplish. This was my most recent statement of the problem: To reiterate my problem: I have a file with one, two, or three non- blank names in each record. I want to produce a line (record) in an outputted CSV file for each non-blank name. Each line will include data pertaining to that name as well as header data that's the same for each name. I'd rather not restructure this in a header-detail way since in other respects I think the costs of doing so would outweigh the benefits. Thanks, Jim >Bart > >Bart Bartholomay >HAB Marketing >3725 8th Lane >Vero Beach, FL 32960 >772.299.6352 >http://HABMarketing.com > > > >On Aug 7, 2008, at 11:51 AM, Jim Guinness wrote: > >>Thanks, Tom. That helps, at least in that it speeds up execution >>quite a bit. But it looks like defining the filename outside of the >>loop doesn't help my main problem. >> >>To reiterate my problem: I have a file with one, two, or three non- >>blank names in each record. I want to produce a line (record) in an >>outputted CSV file for each non-blank name. Each line will include >>data pertaining to that name as well as header data that's the same >>for each name. I'd rather not restructure this in a header-detail >>way since in other respects I think the costs of doing so would >>outweigh the benefits. >> >>My script below failed in a way that's rather too complicated for me >>to want to try to describe. >> >>Next I tried using a calculated field that consisted of the one, two >>or three sets of data for each record, separated by newlines. But >>FM's newline character didn't translate as expected in the output >>file. >> >>What I've done now is a bit kludgey, but I it works: >>Find all records with nonblank Name1, export their Name1 data to >>file A. >>Find all records with nonblank Name2, export their Name2 data to >>file B. >>Find all records with nonblank Name3, export their Name3 data to >>file C. >>Concatenate the three files together into the output file I need. >>Cheers, >> >>Jim Guinness >> >> >>At 8/7/2008 12:31 AM, you wrote: >>>So all you have to do is define a variable like >>>$FileName once before the loop and reference it in each of the Export >>>Records step's output file option. >>>On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: >>> >>>>I have a file in which every record contains at least one name, >>>>15-20% of the records contain two names, and 1-2% of them contain >>>>three names. There's some header info in each record along with the >>>>one, two, or three names, but to keep things simple for me (and >>>>preserve my investment in time so far) I'd rather keep this data >>>>structure than use a header/detail structure. >>>> >>>>I need to run through a set of records and export one record (i.e., >>>>one line) to a CSV file for each name that has a non-blank address >>>>field. So each source record will produce at least one output >>>>record, some will produce two, and a few will produce three. The CSV >>>>export file is the same for all of the names in all of the source >>>>records. >>>> >>>>I've tried doing this using ScriptMaker and the options in the >>>>Export Records dialog boxes, but not all names from each record are >>>>being exported. My script looks like this: >>>> >>>>Enter Browse Mode [] >>>>Go to Record/Request/Page [First] >>>>Loop >>>> If [PrPr::PR1Addr <> ""] >>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>Unicode (UTF-16)] >>>> End If >>>> If [PrPr::PR2Addr <> ""] >>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>Unicode (UTF-16)] >>>> End If >>>> If [PrPr::PR3Addr <> ""] >>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>Unicode (UTF-16)] >>>> End If >>>> Go to Record/Request/Page [Next; Exit after last] >>>>End Loop >>>> >>>>It seems like it'd be better to set the output filename once, before >>>>the loop ... but how? Once again I find myself wanting to edit the >>>>code more directly, but I'm starting to realize that usually isn't >>>>possible. >>>> >>>>Any help or suggestions appreciated ... thanks in advance. >>>> >>>>Jim Guinness >>>>East Central Massachusetts, USA >>>> >>>>_______________________________________________ >>>>FMPexperts mailing list >>>>[hidden email] >>>>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>> >>> >>> >>>_______________________________________________ >>>FMPexperts mailing list >>>[hidden email] >>>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>_______________________________________________ >>FMPexperts mailing list >>[hidden email] >>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > >_______________________________________________ >FMPexperts mailing list >[hidden email] >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
Bart Bartholomay
|
You mention header information and that just doesn't jive with what
you're referring to. If you're talking about duplication of field names, that DOES make sense. "Each line will include data pertaining to that name as well as header data that's the same for each name. I'd rather not restructure this in a header-detail way since in other respects I think the costs of doing so would outweigh the benefits." i'm still trying to figure out what you mean by this. It makes no sense to me at all. Bart Bart Bartholomay HAB Marketing 3725 8th Lane Vero Beach, FL 32960 772.299.6352 http://HABMarketing.com On Aug 7, 2008, at 1:28 PM, Jim Guinness wrote: > At 8/7/2008 12:28 PM, you wrote: >> There really needs to be more information. For example, is it your >> intent to "replace" the set of records with this "new" concatenated >> set in the same table? > > No, I need to create a CSV file. > >> Your kludgy solution below requires a manual >> concatenation outside of FileMaker. Do these three exported files >> "have" to be FileMaker files? > > Yes I thought I could do that from within the script. I'm used to > Unix where that sort of thing is routine. > >> If you can get away with using an Excel >> file (.csv) or Merge file (.mer), > > Yes that's what I want. > >> then it is possible to script the >> new set of records into a global field and export that to Excel. > > Now THAT sounds interesting! Sounds like it could be just the thing. > How is that done? > >> It >> will just be one record, but it will "look" and act like the full >> set, >> and by exporting to Excel it will open up as separate rows in a >> spreadsheet. It would be easy enough to bring that back into your >> FileMaker application as a new set of records. >> >> So you see, it would certainly help if you could be much more >> detailed >> in describing what you want to accomplish. > > This was my most recent statement of the problem: > To reiterate my problem: I have a file with one, two, or three non- > blank names in each record. I want to produce a line (record) in an > outputted CSV file for each non-blank name. Each line will include > data pertaining to that name as well as header data that's the same > for each name. I'd rather not restructure this in a header-detail > way since in other respects I think the costs of doing so would > outweigh the benefits. > Thanks, > > Jim > > >> Bart >> >> Bart Bartholomay >> HAB Marketing >> 3725 8th Lane >> Vero Beach, FL 32960 >> 772.299.6352 >> http://HABMarketing.com >> >> >> >> On Aug 7, 2008, at 11:51 AM, Jim Guinness wrote: >> >>> Thanks, Tom. That helps, at least in that it speeds up execution >>> quite a bit. But it looks like defining the filename outside of the >>> loop doesn't help my main problem. >>> >>> To reiterate my problem: I have a file with one, two, or three >>> non- blank names in each record. I want to produce a line (record) >>> in an >>> outputted CSV file for each non-blank name. Each line will include >>> data pertaining to that name as well as header data that's the same >>> for each name. I'd rather not restructure this in a header-detail >>> way since in other respects I think the costs of doing so would >>> outweigh the benefits. >>> >>> My script below failed in a way that's rather too complicated for me >>> to want to try to describe. >>> >>> Next I tried using a calculated field that consisted of the one, two >>> or three sets of data for each record, separated by newlines. But >>> FM's newline character didn't translate as expected in the output >>> file. >>> >>> What I've done now is a bit kludgey, but I it works: >>> Find all records with nonblank Name1, export their Name1 data to >>> file A. >>> Find all records with nonblank Name2, export their Name2 data to >>> file B. >>> Find all records with nonblank Name3, export their Name3 data to >>> file C. >>> Concatenate the three files together into the output file I need. >>> Cheers, >>> >>> Jim Guinness >>> >>> >>> At 8/7/2008 12:31 AM, you wrote: >>>> So all you have to do is define a variable like >>>> $FileName once before the loop and reference it in each of the >>>> Export >>>> Records step's output file option. >>>> On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: >>>> >>>>> I have a file in which every record contains at least one name, >>>>> 15-20% of the records contain two names, and 1-2% of them contain >>>>> three names. There's some header info in each record along with >>>>> the >>>>> one, two, or three names, but to keep things simple for me (and >>>>> preserve my investment in time so far) I'd rather keep this data >>>>> structure than use a header/detail structure. >>>>> >>>>> I need to run through a set of records and export one record >>>>> (i.e., >>>>> one line) to a CSV file for each name that has a non-blank address >>>>> field. So each source record will produce at least one output >>>>> record, some will produce two, and a few will produce three. The >>>>> CSV >>>>> export file is the same for all of the names in all of the source >>>>> records. >>>>> >>>>> I've tried doing this using ScriptMaker and the options in the >>>>> Export Records dialog boxes, but not all names from each record >>>>> are >>>>> being exported. My script looks like this: >>>>> >>>>> Enter Browse Mode [] >>>>> Go to Record/Request/Page [First] >>>>> Loop >>>>> If [PrPr::PR1Addr <> ""] >>>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>> Unicode (UTF-16)] >>>>> End If >>>>> If [PrPr::PR2Addr <> ""] >>>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>> Unicode (UTF-16)] >>>>> End If >>>>> If [PrPr::PR3Addr <> ""] >>>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>> Unicode (UTF-16)] >>>>> End If >>>>> Go to Record/Request/Page [Next; Exit after last] >>>>> End Loop >>>>> >>>>> It seems like it'd be better to set the output filename once, >>>>> before >>>>> the loop ... but how? Once again I find myself wanting to edit the >>>>> code more directly, but I'm starting to realize that usually isn't >>>>> possible. >>>>> >>>>> Any help or suggestions appreciated ... thanks in advance. >>>>> >>>>> Jim Guinness >>>>> East Central Massachusetts, USA >>>>> >>>>> _______________________________________________ >>>>> FMPexperts mailing list >>>>> [hidden email] >>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>>> >>>> >>>> >>>> _______________________________________________ >>>> FMPexperts mailing list >>>> [hidden email] >>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts- >>>> ironclad.net.au >>> _______________________________________________ >>> FMPexperts mailing list >>> [hidden email] >>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >> >> _______________________________________________ >> FMPexperts mailing list >> [hidden email] >> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > _______________________________________________ > FMPexperts mailing list > [hidden email] > http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
JimG_EasternMA
|
In reply to this post
by Richard DeShong
At 8/7/2008 12:37 PM, you wrote:
>I use an "work" table in my solutions. It has a bunch of generic >text,date,num fields. To create a csv file, just use a single text field in >each rcd. Loop thru your rcds and create 1-3 corresponding rcds in the work >table - formatting the text field any way you want. That sounds very cool and useful. How would you do that? Something like the following?: Put all the CSV data for a given name, including commas, into a variable. Open the work table. Create a new record in it. Put the contents of the variable into the text field in the work table. I like this approach ... just need to figure out the pesky details, since I'm still pretty new to FM. It's the second step I'm not clear about. How to create a new record in a work table? For the third step it looks like one would just use "Set Field", yes? Thanks, Jim >When you're done >looping, just export the single text field from your work table for all of >your rcds. > >This technique allows you to create any line format you want - thus you're >not limited to the export types provided by FM. It also doesn't matter what >version of FM. In an FM6 solution, I use the techique to create >"output.xml" files that can be opened directly in Excel. > >-- >Richard DeShong, Athletic Study Center, UCBerkeley > > >-----Original Message----- >From: Jim Guinness > >Thanks, Tom. That helps, at least in that it speeds up execution >quite a bit. But it looks like defining the filename outside of the >loop doesn't help my main problem. > >To reiterate my problem: I have a file with one, two, or three >non-blank names in each record. I want to produce a line (record) in >an outputted CSV file for each non-blank name. Each line will include >data pertaining to that name as well as header data that's the same >for each name. I'd rather not restructure this in a header-detail way >since in other respects I think the costs of doing so would outweigh >the benefits. > >My script below failed in a way that's rather too complicated for me >to want to try to describe. > >Next I tried using a calculated field that consisted of the one, two >or three sets of data for each record, separated by newlines. But >FM's newline character didn't translate as expected in the output file. > >What I've done now is a bit kludgey, but I it works: >Find all records with nonblank Name1, export their Name1 data to file A. >Find all records with nonblank Name2, export their Name2 data to file B. >Find all records with nonblank Name3, export their Name3 data to file C. >Concatenate the three files together into the output file I need. >Cheers, > >Jim Guinness > > >At 8/7/2008 12:31 AM, you wrote: > >So all you have to do is define a variable like > >$FileName once before the loop and reference it in each of the Export > >Records step's output file option. > >On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: > > > >>I have a file in which every record contains at least one name, > >>15-20% of the records contain two names, and 1-2% of them contain > >>three names. There's some header info in each record along with the > >>one, two, or three names, but to keep things simple for me (and > >>preserve my investment in time so far) I'd rather keep this data > >>structure than use a header/detail structure. > >> > >>I need to run through a set of records and export one record (i.e., > >>one line) to a CSV file for each name that has a non-blank address > >>field. So each source record will produce at least one output > >>record, some will produce two, and a few will produce three. The CSV > >>export file is the same for all of the names in all of the source > >>records. > >> > >>I've tried doing this using ScriptMaker and the options in the > >>Export Records dialog boxes, but not all names from each record are > >>being exported. My script looks like this: > >> > >>Enter Browse Mode [] > >>Go to Record/Request/Page [First] > >>Loop > >> If [PrPr::PR1Addr <> ""] > >> Export Records [No dialog; "PrMergeExport.csv"; > >>Unicode (UTF-16)] > >> End If > >> If [PrPr::PR2Addr <> ""] > >> Export Records [No dialog; "PrMergeExport.csv"; > >>Unicode (UTF-16)] > >> End If > >> If [PrPr::PR3Addr <> ""] > >> Export Records [No dialog; "PrMergeExport.csv"; > >>Unicode (UTF-16)] > >> End If > >> Go to Record/Request/Page [Next; Exit after last] > >>End Loop > >> > >>It seems like it'd be better to set the output filename once, before > >>the loop ... but how? Once again I find myself wanting to edit the > >>code more directly, but I'm starting to realize that usually isn't > >>possible. > >> > >>Any help or suggestions appreciated ... thanks in advance. > >> > >>Jim Guinness > >>East Central Massachusetts, USA > >> > >>_______________________________________________ > >>FMPexperts mailing list > >>[hidden email] > >>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > > > > > > > >_______________________________________________ > >FMPexperts mailing list > >[hidden email] > >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >_______________________________________________ >FMPexperts mailing list >[hidden email] >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > > >_______________________________________________ >FMPexperts mailing list >[hidden email] >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
JimG_EasternMA
|
In reply to this post
by Bart Bartholomay
At 8/7/2008 01:36 PM, you wrote:
>You mention header information and that just doesn't jive with what >you're referring to. If you're talking about duplication of field >names, that DOES make sense. > >"Each line will include data pertaining to that name as well as header >data that's the same for each name. I'd rather not restructure this in >a header-detail way since in other respects I think the costs of doing >so would outweigh the benefits." > >i'm still trying to figure out what you mean by this. It makes no >sense to me at all. Sorry I wasn't clear, I'll try again. Imagine each record in the source table has the address of a house and the name(s) and birthday(s) of one, two, or three people who live there. For example: Record 1: 123 Main St, John Smith, 4/1/1980, Kristen Jones, 9/20/1988 [ 2 people ] Record 2: 99 Oak St., Bridgitte Baylor, 8/18/1990 [ 1 people ] Record 3: 880 Washington Ave., Joe Jacobs, 1/22/1978, Bethany Johnson, 3/24/1978, Paul Hendricks, 5/8/1984 [ 3 people ] From this I want to produce a CSV file with the following contents (the order of these doesn't matter): 123 Main St, John Smith, 4/1/1980 123 Main St, Kristen Jones, 9/20/1988 99 Oak St., Bridgitte Baylor, 8/18/1990 880 Washington Ave., Joe Jacobs, 1/22/1978 880 Washington Ave., Bethany Johnson, 3/24/1978 880 Washington Ave., Paul Hendricks, 5/8/1984 Is that better? Thanks, Jim >Bart > >Bart Bartholomay >HAB Marketing >3725 8th Lane >Vero Beach, FL 32960 >772.299.6352 >http://HABMarketing.com > > > >On Aug 7, 2008, at 1:28 PM, Jim Guinness wrote: > >>At 8/7/2008 12:28 PM, you wrote: >>>There really needs to be more information. For example, is it your >>>intent to "replace" the set of records with this "new" concatenated >>>set in the same table? >> >>No, I need to create a CSV file. >> >>>Your kludgy solution below requires a manual >>>concatenation outside of FileMaker. Do these three exported files >>>"have" to be FileMaker files? >> >>Yes I thought I could do that from within the script. I'm used to >>Unix where that sort of thing is routine. >> >>>If you can get away with using an Excel >>>file (.csv) or Merge file (.mer), >> >>Yes that's what I want. >> >>>then it is possible to script the >>>new set of records into a global field and export that to Excel. >> >>Now THAT sounds interesting! Sounds like it could be just the thing. >>How is that done? >> >>>It >>>will just be one record, but it will "look" and act like the full >>>set, >>>and by exporting to Excel it will open up as separate rows in a >>>spreadsheet. It would be easy enough to bring that back into your >>>FileMaker application as a new set of records. >>> >>>So you see, it would certainly help if you could be much more >>>detailed >>>in describing what you want to accomplish. >> >>This was my most recent statement of the problem: >>To reiterate my problem: I have a file with one, two, or three non- >>blank names in each record. I want to produce a line (record) in an >>outputted CSV file for each non-blank name. Each line will include >>data pertaining to that name as well as header data that's the same >>for each name. I'd rather not restructure this in a header-detail >>way since in other respects I think the costs of doing so would >>outweigh the benefits. >>Thanks, >> >>Jim >> >> >>>Bart >>> >>>Bart Bartholomay >>>HAB Marketing >>>3725 8th Lane >>>Vero Beach, FL 32960 >>>772.299.6352 >>>http://HABMarketing.com >>> >>> >>> >>>On Aug 7, 2008, at 11:51 AM, Jim Guinness wrote: >>> >>>>Thanks, Tom. That helps, at least in that it speeds up execution >>>>quite a bit. But it looks like defining the filename outside of the >>>>loop doesn't help my main problem. >>>> >>>>To reiterate my problem: I have a file with one, two, or three >>>>non- blank names in each record. I want to produce a line (record) >>>>in an >>>>outputted CSV file for each non-blank name. Each line will include >>>>data pertaining to that name as well as header data that's the same >>>>for each name. I'd rather not restructure this in a header-detail >>>>way since in other respects I think the costs of doing so would >>>>outweigh the benefits. >>>> >>>>My script below failed in a way that's rather too complicated for me >>>>to want to try to describe. >>>> >>>>Next I tried using a calculated field that consisted of the one, two >>>>or three sets of data for each record, separated by newlines. But >>>>FM's newline character didn't translate as expected in the output >>>>file. >>>> >>>>What I've done now is a bit kludgey, but I it works: >>>>Find all records with nonblank Name1, export their Name1 data to >>>>file A. >>>>Find all records with nonblank Name2, export their Name2 data to >>>>file B. >>>>Find all records with nonblank Name3, export their Name3 data to >>>>file C. >>>>Concatenate the three files together into the output file I need. >>>>Cheers, >>>> >>>>Jim Guinness >>>> >>>> >>>>At 8/7/2008 12:31 AM, you wrote: >>>>>So all you have to do is define a variable like >>>>>$FileName once before the loop and reference it in each of the >>>>>Export >>>>>Records step's output file option. >>>>>On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: >>>>> >>>>>>I have a file in which every record contains at least one name, >>>>>>15-20% of the records contain two names, and 1-2% of them contain >>>>>>three names. There's some header info in each record along with >>>>>>the >>>>>>one, two, or three names, but to keep things simple for me (and >>>>>>preserve my investment in time so far) I'd rather keep this data >>>>>>structure than use a header/detail structure. >>>>>> >>>>>>I need to run through a set of records and export one record >>>>>>(i.e., >>>>>>one line) to a CSV file for each name that has a non-blank address >>>>>>field. So each source record will produce at least one output >>>>>>record, some will produce two, and a few will produce three. The >>>>>>CSV >>>>>>export file is the same for all of the names in all of the source >>>>>>records. >>>>>> >>>>>>I've tried doing this using ScriptMaker and the options in the >>>>>>Export Records dialog boxes, but not all names from each record >>>>>>are >>>>>>being exported. My script looks like this: >>>>>> >>>>>>Enter Browse Mode [] >>>>>>Go to Record/Request/Page [First] >>>>>>Loop >>>>>> If [PrPr::PR1Addr <> ""] >>>>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>>>Unicode (UTF-16)] >>>>>> End If >>>>>> If [PrPr::PR2Addr <> ""] >>>>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>>>Unicode (UTF-16)] >>>>>> End If >>>>>> If [PrPr::PR3Addr <> ""] >>>>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>>>Unicode (UTF-16)] >>>>>> End If >>>>>> Go to Record/Request/Page [Next; Exit after last] >>>>>>End Loop >>>>>> >>>>>>It seems like it'd be better to set the output filename once, >>>>>>before >>>>>>the loop ... but how? Once again I find myself wanting to edit the >>>>>>code more directly, but I'm starting to realize that usually isn't >>>>>>possible. >>>>>> >>>>>>Any help or suggestions appreciated ... thanks in advance. >>>>>> >>>>>>Jim Guinness >>>>>>East Central Massachusetts, USA >>>>>> >>>>>>_______________________________________________ >>>>>>FMPexperts mailing list >>>>>>[hidden email] >>>>>>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>>>> >>>>> >>>>> >>>>>_______________________________________________ >>>>>FMPexperts mailing list >>>>>[hidden email] >>>>>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts- ironclad.net.au >>>>_______________________________________________ >>>>FMPexperts mailing list >>>>[hidden email] >>>>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>> >>>_______________________________________________ >>>FMPexperts mailing list >>>[hidden email] >>>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>_______________________________________________ >>FMPexperts mailing list >>[hidden email] >>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > >_______________________________________________ >FMPexperts mailing list >[hidden email] >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
bfr00
|
Take a look at my example file Anythingv1.fp7
It is a list processor. You pass it a data list and then you can manipulate the data in various ways including creating CSV exports. It is a work-in-progress technique demo. <http://www.concise-design.com/downloads/accessdemo.zip> > At 8/7/2008 01:36 PM, you wrote: >> You mention header information and that just doesn't jive with what >> you're referring to. If you're talking about duplication of field >> names, that DOES make sense. >> >> "Each line will include data pertaining to that name as well as header >> data that's the same for each name. I'd rather not restructure this in >> a header-detail way since in other respects I think the costs of doing >> so would outweigh the benefits." >> >> i'm still trying to figure out what you mean by this. It makes no >> sense to me at all. > > Sorry I wasn't clear, I'll try again. > > Imagine each record in the source table has the address of a house > and the name(s) and birthday(s) of one, two, or three people who live > there. For example: > > Record 1: 123 Main St, John Smith, 4/1/1980, Kristen Jones, > 9/20/1988 [ 2 people ] > Record 2: 99 Oak St., Bridgitte Baylor, 8/18/1990 [ 1 people ] > Record 3: 880 Washington Ave., Joe Jacobs, 1/22/1978, Bethany > Johnson, 3/24/1978, Paul Hendricks, 5/8/1984 [ 3 people ] > > From this I want to produce a CSV file with the following contents > (the order of these doesn't matter): > > 123 Main St, John Smith, 4/1/1980 > 123 Main St, Kristen Jones, 9/20/1988 > 99 Oak St., Bridgitte Baylor, 8/18/1990 > 880 Washington Ave., Joe Jacobs, 1/22/1978 > 880 Washington Ave., Bethany Johnson, 3/24/1978 > 880 Washington Ave., Paul Hendricks, 5/8/1984 > > Is that better? > > Thanks, > > Jim > > > >> Bart >> >> Bart Bartholomay >> HAB Marketing >> 3725 8th Lane >> Vero Beach, FL 32960 >> 772.299.6352 >> http://HABMarketing.com >> >> >> >> On Aug 7, 2008, at 1:28 PM, Jim Guinness wrote: >> >>> At 8/7/2008 12:28 PM, you wrote: >>>> There really needs to be more information. For example, is it your >>>> intent to "replace" the set of records with this "new" concatenated >>>> set in the same table? >>> >>> No, I need to create a CSV file. >>> >>>> Your kludgy solution below requires a manual >>>> concatenation outside of FileMaker. Do these three exported files >>>> "have" to be FileMaker files? >>> >>> Yes I thought I could do that from within the script. I'm used to >>> Unix where that sort of thing is routine. >>> >>>> If you can get away with using an Excel >>>> file (.csv) or Merge file (.mer), >>> >>> Yes that's what I want. >>> >>>> then it is possible to script the >>>> new set of records into a global field and export that to Excel. >>> >>> Now THAT sounds interesting! Sounds like it could be just the thing. >>> How is that done? >>> >>>> It >>>> will just be one record, but it will "look" and act like the full >>>> set, >>>> and by exporting to Excel it will open up as separate rows in a >>>> spreadsheet. It would be easy enough to bring that back into your >>>> FileMaker application as a new set of records. >>>> >>>> So you see, it would certainly help if you could be much more >>>> detailed >>>> in describing what you want to accomplish. >>> >>> This was my most recent statement of the problem: >>> To reiterate my problem: I have a file with one, two, or three non- >>> blank names in each record. I want to produce a line (record) in an >>> outputted CSV file for each non-blank name. Each line will include >>> data pertaining to that name as well as header data that's the same >>> for each name. I'd rather not restructure this in a header-detail >>> way since in other respects I think the costs of doing so would >>> outweigh the benefits. >>> Thanks, >>> >>> Jim >>> >>> >>>> Bart >>>> >>>> Bart Bartholomay >>>> HAB Marketing >>>> 3725 8th Lane >>>> Vero Beach, FL 32960 >>>> 772.299.6352 >>>> http://HABMarketing.com >>>> >>>> >>>> >>>> On Aug 7, 2008, at 11:51 AM, Jim Guinness wrote: >>>> >>>>> Thanks, Tom. That helps, at least in that it speeds up execution >>>>> quite a bit. But it looks like defining the filename outside of the >>>>> loop doesn't help my main problem. >>>>> >>>>> To reiterate my problem: I have a file with one, two, or three >>>>> non- blank names in each record. I want to produce a line (record) >>>>> in an >>>>> outputted CSV file for each non-blank name. Each line will include >>>>> data pertaining to that name as well as header data that's the same >>>>> for each name. I'd rather not restructure this in a header-detail >>>>> way since in other respects I think the costs of doing so would >>>>> outweigh the benefits. >>>>> >>>>> My script below failed in a way that's rather too complicated for me >>>>> to want to try to describe. >>>>> >>>>> Next I tried using a calculated field that consisted of the one, two >>>>> or three sets of data for each record, separated by newlines. But >>>>> FM's newline character didn't translate as expected in the output >>>>> file. >>>>> >>>>> What I've done now is a bit kludgey, but I it works: >>>>> Find all records with nonblank Name1, export their Name1 data to >>>>> file A. >>>>> Find all records with nonblank Name2, export their Name2 data to >>>>> file B. >>>>> Find all records with nonblank Name3, export their Name3 data to >>>>> file C. >>>>> Concatenate the three files together into the output file I need. >>>>> Cheers, >>>>> >>>>> Jim Guinness >>>>> >>>>> >>>>> At 8/7/2008 12:31 AM, you wrote: >>>>>> So all you have to do is define a variable like >>>>>> $FileName once before the loop and reference it in each of the >>>>>> Export >>>>>> Records step's output file option. >>>>>> On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: >>>>>> >>>>>>> I have a file in which every record contains at least one name, >>>>>>> 15-20% of the records contain two names, and 1-2% of them contain >>>>>>> three names. There's some header info in each record along with >>>>>>> the >>>>>>> one, two, or three names, but to keep things simple for me (and >>>>>>> preserve my investment in time so far) I'd rather keep this data >>>>>>> structure than use a header/detail structure. >>>>>>> >>>>>>> I need to run through a set of records and export one record >>>>>>> (i.e., >>>>>>> one line) to a CSV file for each name that has a non-blank address >>>>>>> field. So each source record will produce at least one output >>>>>>> record, some will produce two, and a few will produce three. The >>>>>>> CSV >>>>>>> export file is the same for all of the names in all of the source >>>>>>> records. >>>>>>> >>>>>>> I've tried doing this using ScriptMaker and the options in the >>>>>>> Export Records dialog boxes, but not all names from each record >>>>>>> are >>>>>>> being exported. My script looks like this: >>>>>>> >>>>>>> Enter Browse Mode [] >>>>>>> Go to Record/Request/Page [First] >>>>>>> Loop >>>>>>> If [PrPr::PR1Addr <> ""] >>>>>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>>>> Unicode (UTF-16)] >>>>>>> End If >>>>>>> If [PrPr::PR2Addr <> ""] >>>>>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>>>> Unicode (UTF-16)] >>>>>>> End If >>>>>>> If [PrPr::PR3Addr <> ""] >>>>>>> Export Records [No dialog; "PrMergeExport.csv"; >>>>>>> Unicode (UTF-16)] >>>>>>> End If >>>>>>> Go to Record/Request/Page [Next; Exit after last] >>>>>>> End Loop >>>>>>> >>>>>>> It seems like it'd be better to set the output filename once, >>>>>>> before >>>>>>> the loop ... but how? Once again I find myself wanting to edit the >>>>>>> code more directly, but I'm starting to realize that usually isn't >>>>>>> possible. >>>>>>> >>>>>>> Any help or suggestions appreciated ... thanks in advance. >>>>>>> >>>>>>> Jim Guinness >>>>>>> East Central Massachusetts, USA >>>>>>> >>>>>>> _______________________________________________ >>>>>>> FMPexperts mailing list >>>>>>> [hidden email] >>>>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>>>>> >>>>>> >>>>>> >>>>>> _______________________________________________ >>>>>> FMPexperts mailing list >>>>>> [hidden email] >>>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts- ironclad.net.au >>>>> _______________________________________________ >>>>> FMPexperts mailing list >>>>> [hidden email] >>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>>> >>>> _______________________________________________ >>>> FMPexperts mailing list >>>> [hidden email] >>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>> _______________________________________________ >>> FMPexperts mailing list >>> [hidden email] >>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >> >> _______________________________________________ >> FMPexperts mailing list >> [hidden email] >> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > _______________________________________________ > FMPexperts mailing list > [hidden email] > http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
Richard DeShong
|
In reply to this post
by JimG_EasternMA
Work table, how does that work?:
1) Create a Work table with, at least, the following 2 fields: Text1, a text field; gText1, a global text field. 2) For your Work table create a CreateRcd script: Create New Record Set Text1 to gText1 Commit New Record 3) From your Name table, create a "broken" global to global relationship to the Work table. This allows you to set global fields in the Work table without FM needing to find rcds. 4) From your Name table create a script that loops thru your rcds: Loop Start Set Work.gText to <line formatted with Name1> Perform Script Work.CreateRcd If Name2 <same as 1st two lines, with Name2> If Name3 <same as 1st two lines, with Name3> GoTo Next Record Loop End 5) Now change to Work, find all your rcds, and export using <tab> format. Bart Bartholomay was suggesting that this can be done using a global field (instead of creating rcds in another table). This works also. One caveat, at the end of the loop, you need a process to find one, and only one, record, and then export the global field. The separate table method allows you to maintain your current found set - sometimes significant, sometimes not. -- Richard DeShong, Athletic Study Center, UCBerkeley -----Original Message----- From: Jim Guinness At 8/7/2008 12:37 PM, you wrote: >I use an "work" table in my solutions. It has a bunch of generic >text,date,num fields. To create a csv file, just use a single text field in >each rcd. Loop thru your rcds and create 1-3 corresponding rcds in the work >table - formatting the text field any way you want. That sounds very cool and useful. How would you do that? Something like the following?: Put all the CSV data for a given name, including commas, into a variable. Open the work table. Create a new record in it. Put the contents of the variable into the text field in the work table. I like this approach ... just need to figure out the pesky details, since I'm still pretty new to FM. It's the second step I'm not clear about. How to create a new record in a work table? For the third step it looks like one would just use "Set Field", yes? Thanks, Jim >When you're done >looping, just export the single text field from your work table for all of >your rcds. > >This technique allows you to create any line format you want - thus you're >not limited to the export types provided by FM. It also doesn't matter what >version of FM. In an FM6 solution, I use the techique to create >"output.xml" files that can be opened directly in Excel. > >-- >Richard DeShong, Athletic Study Center, UCBerkeley > > >-----Original Message----- >From: Jim Guinness > >Thanks, Tom. That helps, at least in that it speeds up execution >quite a bit. But it looks like defining the filename outside of the >loop doesn't help my main problem. > >To reiterate my problem: I have a file with one, two, or three >non-blank names in each record. I want to produce a line (record) in >an outputted CSV file for each non-blank name. Each line will include >data pertaining to that name as well as header data that's the same >for each name. I'd rather not restructure this in a header-detail way >since in other respects I think the costs of doing so would outweigh >the benefits. > >My script below failed in a way that's rather too complicated for me >to want to try to describe. > >Next I tried using a calculated field that consisted of the one, two >or three sets of data for each record, separated by newlines. But >FM's newline character didn't translate as expected in the output file. > >What I've done now is a bit kludgey, but I it works: >Find all records with nonblank Name1, export their Name1 data to file A. >Find all records with nonblank Name2, export their Name2 data to file B. >Find all records with nonblank Name3, export their Name3 data to file C. >Concatenate the three files together into the output file I need. >Cheers, > >Jim Guinness > > >At 8/7/2008 12:31 AM, you wrote: > >So all you have to do is define a variable like > >$FileName once before the loop and reference it in each of the Export > >Records step's output file option. > >On Aug 6, 2008, at 9:18 PM, Jim Guinness wrote: > > > >>I have a file in which every record contains at least one name, > >>15-20% of the records contain two names, and 1-2% of them contain > >>three names. There's some header info in each record along with the > >>one, two, or three names, but to keep things simple for me (and > >>preserve my investment in time so far) I'd rather keep this data > >>structure than use a header/detail structure. > >> > >>I need to run through a set of records and export one record (i.e., > >>one line) to a CSV file for each name that has a non-blank address > >>field. So each source record will produce at least one output > >>record, some will produce two, and a few will produce three. The CSV > >>export file is the same for all of the names in all of the source > >>records. > >> > >>I've tried doing this using ScriptMaker and the options in the > >>Export Records dialog boxes, but not all names from each record are > >>being exported. My script looks like this: > >> > >>Enter Browse Mode [] > >>Go to Record/Request/Page [First] > >>Loop > >> If [PrPr::PR1Addr <> ""] > >> Export Records [No dialog; "PrMergeExport.csv"; > >>Unicode (UTF-16)] > >> End If > >> If [PrPr::PR2Addr <> ""] > >> Export Records [No dialog; "PrMergeExport.csv"; > >>Unicode (UTF-16)] > >> End If > >> If [PrPr::PR3Addr <> ""] > >> Export Records [No dialog; "PrMergeExport.csv"; > >>Unicode (UTF-16)] > >> End If > >> Go to Record/Request/Page [Next; Exit after last] > >>End Loop > >> > >>It seems like it'd be better to set the output filename once, before > >>the loop ... but how? Once again I find myself wanting to edit the > >>code more directly, but I'm starting to realize that usually isn't > >>possible. > >> > >>Any help or suggestions appreciated ... thanks in advance. > >> > >>Jim Guinness > >>East Central Massachusetts, USA > >> > >>_______________________________________________ > >>FMPexperts mailing list > >>[hidden email] > >>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > > > > > > > >_______________________________________________ > >FMPexperts mailing list > >[hidden email] > >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >_______________________________________________ >FMPexperts mailing list >[hidden email] >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > > >_______________________________________________ >FMPexperts mailing list >[hidden email] >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
| Free Embeddable Forum Powered by Nabble | Help |
