Export Summaries

[LaRetta]

I have multiple dynamic columns which only contain summaries. Owner wants these 'cross-tab' views exported. Each attempt takes forever to export 100 records with 6 columns; it's almost like FM recalculates the summaries. And once exported, I end up with multiple rows of the summary totals and only the first row contains the 'row.' I understand why this happens - I need to be in the many side to have the 'row' list on each line ... but that's not what I want! Each column is a different relationship. And the following lines aren't needed because they only contain the summaries listed on the first line! I want a duplicate of my row/columns to export in cross-tab fashion.

What DOES work well is Copy All and then paste directly into Excel. Owner really likes that and wants me to automate it. Problems: 1) If too much is put on clipboard, it crashes XP, 2) I don't know how to automate opening Excel and pasting into a new worksheet and 3) it feels like a kludge solution. I suppose I could write the results somehow to a field or record but the best approach is eluding me.

The rows contain products and the columns are date periods with dollar summaries, count summaries and percentages. So I also need to export the headings (or field names). How would others approach a problem such as this?

LaRetta

[LaRetta]

I thought it would help to show you an example (attached). This is not how I'm creating my column relationships but it was quick to use for a demo. I want to capture this data as records if possible.

I can capture it to clipboard (see global Report field below). I assume this is providing me with a tab export (in a way) but exporting the Report field have proved unsuccessful to then import as records. It displays a square 'tab' doesn't seem to be recognizable to Excel to import as rows.

It works great to manually paste into Excel Worksheet - even providing the headers properly. But I'm concerned about crashing with too much on the clipboard and I'm unsure whether I want to 'put it all together just to take it apart again." I'd like records if possible.

Owner wants to send Excel files of this data and I'd like to automate it. :wink2:

ColumnSums.zip

[Fenton]

Just a quick note, as it's late. The reason that FileMaker leaves fields off of successive rows is that it's trying to help you produce what you'd normally see in a summarized report. It leaves out the successive rows of fields that were used to sort on in order to summarize. To get the values you can fool FileMaker by creating an unstored calculation of the same field, and exporting that instead; still sorting by the real field (obviously :-). At least I think so.

This doesn't necessarily solve your problem. Personally I think I'd create Sum() calculations in the reports table to produce the totals, then export those. Don't know about the Wholesale & Retail rows. It may be possible to use 2 dummy records at the end, with names of "Wholesale" and "Retail", and use a multi-line key as the target to match to.

Name & ¶ &

"Wholesale" & ¶ &

"Retail"

Name would match on Jason, Mara, etc.. Wholesale would match on its record, and Retail on its record. It might produce such an export. Which is a funky structure. One has to wonder why people have this urge to export data when FileMaker can show it to them practically any way they want internally, or print it. Sigh.

[LaRetta]

The reason that FileMaker leaves fields off of successive rows is that it's trying to help you produce what you'd normally see in a summarized report. It leaves out the successive rows of fields that were used to sort on in order to summarize.

Hi Fenton!!

Ummm, it's not sorted nor summarized; it's Browse mode. I realize you took a quick look and didn't really have time. There is only a body. My experience with exporting shows that, if you are in the 'one' side and export any related fields, you will get multiple blank rows - one to represent every related record. If from a Company table, you select related field with 3 Contacts for export, you'll get three rows - first with Company field and successive rows blank, containing only the related field information on subsequent rows. I believe that is why I'm getting the multiple rows ... I'm including related fields. But I could be wrong.

The power of using summary fields is 1) They don't need to be sorted to work perfectly, 2) Summaries appear to be faster than Sum() maybe because it's not required to look through a relationship to gather the data. I've only conducted one speed test comparison of the two across a network (so other factors may have come into play skewing my results) but my Summary displayes were almost one third faster than using Sum() and 3) the SAME summary field is used within every relationship represented to the same table occurrence thus saving creating multiple Sum() fields in Main for each of 12 relationships. At least that's my initial perception.

Alas, maybe I can write the headings and columns to a repeating field but I'm unsure how that exports - never done it. I'll give that a shot in the morning. What I'd rather NOT do is paste into one field and then parse it back into flat-line records. I should be able to set repetitions without the need to create an additional 12 fields to hold my results or parse and create records in another table.

The solution I'm using (provided by Comment) is working a treat (although the relationships are established more dynamically than the simple demo I provided). I don't plan to change it just so I can give Owner export capabilities. He uses the FM displays now but wanted to send this to other people who don't have FileMaker. I'm just not crazy about crashing him if he grabs too much on clipboard. Actually, I'll know the record count (because it's in the table producing the records). Maybe I can restrict how many records he copies to clipboard at a time.

I'm not giving up by a long shot. You've helped me so much in the past, Fenton, and I sure appreciate it!!

LaRetta

[LaRetta]

I've attached a revised test file. Ran into a few problems.

Clipboard method: When I clear the clipboard, it doesn't. I thought for sure I've used Commit in the past with a Copy to put 'nothing' back on clipboard. But after pasting into Excel, then Clear Clipboard, it still holds onto data!!??

Export method: Well, using Repetitions MIGHT work (see new button on right of Report global). But it inserts additional garbage I don't want and Excel pulls all those silly boxes in also. Bummer!! To capture the Header information, I ended up creating a dummy record and set that record's repetitions with the column header info.

Windows/clipboard: Two things happen if I overload a clipboard: 1) The windows title bar turns white; a known indication of problem and (sure enough), I get the 'FileMaker not responding' error and have to close down or 2) the box itself powers off!!! Maybe that's a bios thing? Different systems are responding differently when clipboards are overloaded. I believe I can restrict their 'output' number of records to groups and eliminate this problem. But I don't want to leave this data on their clipboard when done.

Any other ideas appreciated.

LaRetta

ColumnSumsRev1.zip

[Fenton]

Here is how I'd do it. It's kind of different. Basically I'm not trying to fudge it inside FileMaker. I created the tables, fields and table occurrences just as I would normally. Records in the Sum table are really just SalesReps (which table you may have). There are multiple TOs at the end for the global date ranges (I included the From date in the range; it's easier for me).

"Wholesale" and "Retail" are not done with "dummy" records. They're unstored fixed-text calculation fields.

I created calculation fields to get the results, in Browse mode, into the Sum table. This cuts down on the number of records exported.

(BTW, I didn't realize that Summary fields worked across relationships like that, and that they're faster than Sum(). I don't think they did in FileMaker 6. Thanks for the tip.)

So I exported the fields, but as XML, so I could use an XSL stylesheet to rearrange them, and their labels, however I wanted to. The result is a tab-separated text file, with unicode returns (ASCII 10). That should work with Windows also. You could alternatively write it explicitly as a Windows file; just change the encoding='utf-8' attribute in the xsl file.

It would require a bit of hassle to get this to work properly with networked files. If you have a mounted server maching that would be where to put the xsl stylesheet.

[P.S. This file took a beating. I had to Force-Quit FileMaker, after it hung up while setting up the file reference for the XML export. This is a fairly common problem; it's a weakness, I would even say a bug, though it's happy again after you restart. I would not use a working file to set up an XML export, nor have any such running.]

LaRetta_Sums.zip

[LaRetta]

Thank you!!! I will play with this!!!

Just so you know ... the 'records' are all dummy records. This Reports module has no records. They are created after User selects from several Value Lists. The relationship includes the row shown. User can then Omit any final records they don't want to view.

I look forward to pulling your ideas apart. But I wonder if Repetitions can't also be made to work (as in my second demo)? Because if it means restructuring, I don't think I want to do it. This Reports module is the hub ... I don't want to recreate these column relationships in every table so I bring the table to it instead.

Much appreciated, Fenton.

BTW, I get the feeling that, if I can slick-up this technique, it will be quicker than even writing summaries to static fields. In other words, I see other potentials for the trickery we're trying. And that's why I'm PARTICULARLY interested in taking this as far as I can go. It never hurts to have more things in my bag of tricks. :bigshades:

Edited August 27, 2005 by Guest

[comment]

I cannot download this - I get a page saying:

"There was a problem loading this page:

Your permission set does not allow you to be here."

(If my permission set does not allow me to be there - how did I get there?)

[LaRetta]

Ummm, you might try again if it was my file you were trying to download. I, ummmm, had placed the repeating field on the table which was copied and messed up my demo. It's corrected now.

If you're talking about Fenton's demo, it downloaded fine for me.

[LaRetta]

Ummm, just realized I forgot to include the Row in the export but I hope you get the drift of where I was going anyway.

The first Repetition in the loop was supposed to hold the Row.

I can 'ahead get' (spoonerism) of myself sometimes. :wink2:

[comment]

No, it was your file - I got it now.

A couple of thoughts (I cannot test these without generating a huge table, which I won't):

1. If you would add a calc field per column, you could still use the summaries from the related file. Of course, it's less elegant that way, but worth a try. Make the first calc =

Data Column 01::sTotalAmount

and so on. This *should* make a plain export reasonably fast, and hopefully will not slow down the browsing.

(Oh - I see Fenton's done this AND added the headers. So we should await your verdict regarding speed.)

2. I am still puzzled by the copy problem. Does this happen as soon as you copy all records, or only when you copy the global field and go to Excel?

If the former, you could compile the report row by row, copying only one record at a time (you'd probably want to save the found set first).

In any case, you can use Export Field Contents to get the report out, instead of copy.

[LaRetta]

OMG!

Taking my demo - no changes whatsoever - creating the sample 'global report' then a simple Export Field Contents[] - which I've NEVER used, and creating a txt file, opens in Excel perfectly!!

I exported the global report field directly!! Yeah, it would bomb when only copying ... this is not an isolated problem for Windows, I'm afraid. The same thing would happen in prior version of FM if too much was put on clipboard. I would bet Macs don't have this problem at all. My work system, which powers OFF if I use FM with clipboard (upon executation of Copy All Records) is an isolated problem. I've NEVER had another system shut off on me because of ANY software action. It's very strange indeed.

But this whole concept blows me away! I must now try it in my real solution (on a backup copy of it anyway)! So much to play with! Well, I don't want to create all the Sum() calcs - or ANY calcs. I think it more logical (for me anyway) to copy one record at a time (or chunks at a time) into one field. But I reserve decision until I've played with every option thoroughly.

I can't thank you both enough.

LaRetta

Edited August 27, 2005 by Guest

[LaRetta]

Owner emailed me this morning after viewing the demo options ... he likes the copy/paste method.

However, I'm trashing it. It is simply too unpredictable, ie, 1600 records with 6 portals doesn't crash but 1000 records with 8 does. Determining exactly how much clipboard is used isn't worth the hastle. And there may be up 12 columns and possible 3,000 records. Too many variables for my taste. And, even though I can clear the clipboard which is used within FM, I can't clear the clipboard if User then decides to paste into Excel or Word elsewhere. I don't like that.

I think I may script it unless someone sees some problems with that concept. I am still dragging my feet on creating all those calculations just to export it.

And I admit a weakness (actually NO knowledge of XML) and, although I want to learn Fenton's method, I believe this approach will be easier for me to work with, unless my speed tests show a major difference.

Demo attached. In this way, I can script maximum columns (12) even if they aren't used. The script would be extended out from 3 columns to allow for all 12 (same with column headings). I will probably even provide a global for a Report Heading for them. The tabs should provide 'spaces' if there are any empty column fields; at least they do in my simple tests.

Am I taking a wrong turn in this approach?

Hmmm, since I'm using Set Field[], maybe I could pretty-up the columns so they prefill with .00 if there are no cents. I'll work on that also. I'm unsure which would look best for an export if the column is blank, maybe .00?

LaRetta

ColumnSumsRev2.zip

Edited August 28, 2005 by Guest

[comment]

I believe that is the optimal solution in this case. You took the best of both worlds. Adding fields just for export does not seem elegant - after all, it is only an auxiliary task of the solution. Replacing 'copy' with the actual calculation is smart (I'm kicking myself for not suggesting it earlier).

BTW, the generated file is mislabeled as csv - you are using tabs to separate the values.

Two tabs in a row will leave an empty cell in Excel, so you don't need to worry about that. You should try to automate the rest of the process: to have the file opened in Excel, and format the appropriate fields to currency format (instead of adding zeros in FM).

[LaRetta]

Hi Michael,

I changed it from txt to csv because my version of Excel (ancient) doesn't even list tab - won't open tab. But it takes csv and selects TAB as delimiter whether txt or csv extension. I thought it would be clearer to User that it's TAB if I named it csv because most people know that's delimited.

Yes, now I will attack the Excel side.

Another thing I like about working through this process with you both, is that I see a handy utility to provide quick views of small summarized tables for Users. For some standardized reports, such as simple list of Sales Reps (6-10 rows) and only 4 columns (only sales per week for current month), one click can display the results in a global text field anywhere (even unrelated). It doesn't have to be displayed at all as list or table layout. I can set the column/rows via script on report form layout under a Freeze Window[]. I think it will be much quicker than waiting for redraws! I sure hope I'm right about that because that will make this process even faster, I think.

Thank you tons!

LaRetta

Edited August 28, 2005 by Guest

[LaRetta]

Excel macro works perfectly! It's done!

If I use this for quick views (small tables), I'll want to format the dollars. But I have a custom function already in place just for these types of things. It should work fine!!

Within 10 minutes, this will be one less thing on my plate and one more project done! YAY!!

Oh. Wait. I should automate opening Excel and firing the macro. I think my FM script needs Open URL or Send Message or something; I'll research it. Never needed to do that but I've read about it. And there must be a way within Excel to fire a macro upon startup?

Well, it's a gorgeous day and I'm taking Ginger for a hike! This project can wait a few hours to finish it. Good Lord ... I had to look behind me to see who could have possibly said such a thing! But I'm the only one here!!

UPDATE: BTW, Fenton, your technnique is quite powerful. And it's on my plate to learn. I see many uses for such an elegant technique exporting as XML and using XLS. Thank you so much!

Edited August 28, 2005 by Guest