If you want to go from File A to File B, why not just do that directly instead of doing the export? I'm missing something here ...
I'm not understanding your comment either Mike.
I have a scripted export process. I'm using the same record set, exporting the records mulitple times with a different revenue month assigned.
IE: Widget exists for 6 months at x value.. This record is exported 6 times, once for each of the 6 months is exists with 1/6 of the total value.
As I export Month one, then I update the revenue month, export again, the prior file is overwritten.
The goal is to get all of these exported records into one file so some reporting can be done.
The report is temporary snapshot, I don;t need to warehouse the data moreso than to do the initial report.
What would be perfect is to append all the data into an excel document but it doesn't appear any export formats are appendable.
I hope this clarifies what I am trying to accomplish.
Do it in the other direction. Make your changes in File A, make your found set be the rcords you want to "export", then run a script from File B that imports from File A. No middle files needed. From File B you can directly import the records in File A's table's found set. Instead of it being an "appending export" from File A, it's a standard import into File B
You don't have to warehouse the data. It can be temporary. You just send all those records to a single FileMaker table (modifying the necessary value each time) and, once they're in the destination, export once to Excel from there. This will accomplish the "append" for you.
Thanks PalmDBS and Mike for your comments. I can't replicate all the revenue months in File A, I would be duplicating several records over and over again in a live system and it would otherwise skew reporting if users happen to run some. Testing the script I have so far, I start with 53 records in File A, but after exporting the same records over and over again, once for each revenue month they exist, I had 102 records total. Each export, I had to exported into a new file, I had 10 total, and I imported the later 9 into the first one. the process acheived the goal of having all these in single file ready for export but its not functional to script out that way.
Over 15 yrs of FM development, I've never used the Copy Record Or Copy All Records script step. Reading up on it this morning, using a test DB with a single table, copied the table, pasted it into the file so I had a blank copy of the same table. Using COPY RECORD option (right click), I move to table 2 layour I have open in another window, I cannot seem to paste it into the new empty table. I create a blank record, try paste, that doesn't work, entering the first field, I paste, I get a tab delimited paste of all the records in that first field though.
Mike, If I create a temp table to store these records inside the solution to replicate the records as needed, how is it that I "send all those records" to this new table. I've read up on the copy/record / request and copy All script steps. What I don't see is how you paste them into a different table. I assume you mean I could copy the found set of records and paste them all into this temp table some how. I can do the export/import process into the temp table work with that.
I develop in a vacuum and I feel a bit silly that this is a bit perplexing for me.
This is a hosted solution on FM13 server win box, I'm using FMPA 13 win.
Do what PalmDBS suggested. Create a "scratch" table with all the necessary fields. This can be inside your hosted solution or in a separate file; doesn't matter much. Then, write a script or scripts that perform(s) these steps:
1) Go to a layout based on the scratch table.
2) Delete all records.
3) Begin a loop.
4) Go to a layout based on the source table.
5) Set your temporary field to the desired revenue month.
6) Go to a layout based on the scratch table.
7) Import the records from the source table to the scratch table (using Matching Field Names).
8) Loop back to step 4 (repeat as many times as needed).
9) Go to a layout based on the scratch table.
10) Show all records.
11) Export to Excel.
I have one last question regarding step 7 above.
I was testing table import process this morning. The source table has 120 records, I had 53 in my found set on source table, , I went to the Scratch Table (empty) in the same db, I imported the source table and instead of only importing the 53 in the current found set, It imported ALL the records. I seem to recall that if you had an active found set on a layout, when you imported from that table, only the found set would import. That didn't happen this morning.
I did come across another thread regarding this approach, you answered on this thread too. https://fmdev.filemaker.com/message/108214#108214
thank you for assisting with this process.
It should only import the found set. Make sure the found set is on the same table occurrence from which you're importing. That would be at the top of the Import dialog.
I have two tables, Source and Scratch in the same file. Two layouts. source/scratch
source has 102 records - scratch is empty
source window has 17 records in the found set.
from the scratch table, using file Import, I select the file, select source table, fields are mapped and import..
I get 102 Records imported, not the found set of 17.
Please take a screen shot of your Import dialog.
Use "matching records" instead of "last order" (in case you change something later), Otherwise, you should be good.
Don't forget that you can click the arrow >> by Field Names and see data from the records you are about to import; and also see the count of records that the import will process.
I just set up the scratch table in the live DB, Mapped fields from the source table (using the correct TO that the source layout with the found set had) and it started importing a few 1,000 records instead of the 55 records on the source TO had on the other layout.
I'm going to create an export fmp12 file to the app temp folder, go to Scratch table and import it.
Thanks guys for this lesson in APPENDABLE EXPORTS !