Export summaries to flat file Excel

[PiedPiper]

I kinda got in trouble today. Owner wanted summary of all products sold by Customer by product. I created report and put customer and product both in sub-part above. The report displayed right. But he wanted it as straight data, so I exported it.

When importing into Excel, each sub part only listed once on the colume - rest blank until the next customer/product displayed. I worked on it for hours trying to export. I even added customer and product into the second subpart and only exported that subpart - nothing worked. and finally copy/pasted all through the spreadsheet to 'fill in' this information manually. It wasn't ready for his meeting and Im in deep [censored].

How can I take summaries and export them so they display as flatfile line items? I thought script to create records in new db - not sure that would work. I also needed to include the salesperson name - which would be the same on each so I just put it in the sub-part also. That worked. Why wouldn't the info 'category' sub'd and sorted by - not duplicate itself on each line but other stuff would.

Hope this made sense. If not, I'd be happy to try to explain it better. It's been one of those days and I think I hate exporting summaries. I clicked summary and clicked all fields displayed - then tried unclicking those fields - nothing made a difference. A bit of direction here please, all you smart people?

Pete

[Fenton]

I ran into this. FileMaker is trying to help by outputting the data pretty much as it would for a Subsummary report. The trouble is the "flat-file" systems have to have the data in every row.

You'll notice that the missing data is in the fields you sorted by. An easy work-around is to duplicate that field(s), then sort on the duplicate instead. Or include the duplicate in the export data instead. You can make the duplicate Unstored, so it doesn't add much to the file size.

There may be a better way, but that's what I remember right now.

[PiedPiper]

Hi Fenton,

Okay, I've been working on this. Could you explain more by:

"An easy work-around is to duplicate that field(s), then sort on the duplicate instead. Or include the duplicate in the export data instead. You can make the duplicate Unstored, so it doesn't add much to the file size."

When I copy and paste the duplicate field on the form, it doesn't come up as a duplicate to sort on.I pasted it in both sub parts. Unstored? That's in definition fields isn't it? How can I make it both. I changed Customer field to indesx on and unstored but it didn't like it and when I paste the field on the layout it seems the same. Well, somethings wrong that I'm missing. Can you just say some more about this. I don't quite understand. Thank you.

Pete

[Fenton]

I wasn't talking about the layout, I was talking about "duplicating" the field in Define Fields. What I meant was create a calculation field2, make it: = field1. Then you can sort on field1, but export field2. Since FileMaker is only doing the Summary thing on the field you actually sort on, you should not get "blanks" when you export the other field. At least that's what I remember; let me know if it doesn't work.

[PiedPiper]

oh all makes sense now. Amazing how one little word 'duplicate' can send someones thinking in the wrong direction. Thanks Fenton, I'll try it. I'm sure it'll work because the only fields that would only show one line were the ones being sorted as you said. If I run into problems I'll let you know but I don't think I will.

i'm trying to finish another project with backgrounds and then I'll turn back tothis issue. Owner will want more Excel stuff on Monday and I need to be ready for him. Thanks again.

Pete